Inhalt
Der Bedarf
Dieser Artikel entstand aus den Wünschen vieler Lync/Skype for Business Kunden nach Reports und Statistiken. Es scheint anscheinend nicht ein sehr verbreitetes Wissen zu sein, wie aus den Daten in der lcscdr und qoemetric Datenbanken sinnvolle Reports gebaut werden können. Ich habe mir deshalb selber einige Möglichkeiten überlegt:
- Monitoring Server Reports (eig. SQL Server Reporting Sevices Reports): ein nützliches Grundset kommt ja mit. Wie man die anpasst, ist eher wenig unbekannt, geht aber soweit ganz gut. Reports können auch verschickt (per Mail abonniert) werden. Nachteil: ein Lync Monitoring Server muss vorhanden sein; manchmal streiken die Reports hartnäckig und der grafische Overhead ist nicht immer angebracht (bei der Weiterverarbeitung).
Ich schreibe noch einen Blog dazu. Hier ein Beispiel: How to build a custom report for Lync/Skype for Business - Erweiterung mit Datenfeeds und Power Pivot Techniken, siehe den Blog von Ilse van Criekinge: Extending your Lync monitoring data using PowerPivot and Power View. Dies eröffnet ganz coole Möglichkeiten, sofern man sich gut damit auskennt. Für mich das zu sehr Neuland. Einige Versuche scheiterten an irgendwelchen Berechtigungen und ich habe es nicht mehr verfolgt.
- Eigene Auswertungen mit gescripteten Exports aus der DB. Naheliegenderweise schlage ich dazu Powershell vor. Hier stellt sich die Frage, wie man die Resultate auswertet und präsentiert.
- Wie 3., aber mit Einlieferung der Daten in Splunk.
Aufgrund meiner Kriterien:
- Reporting ohne Monitoring Server (Monitoring muss in der Topologie definiert sein, aber kein Monitoring Server ist notwendig)
- maschinenauswertbare Reports ohne grafischen Ballast (low footprint)
- Langzeit-Archivierung
- flexible Visualisierungsmöglicheiten
habe ich mich entschieden, den Weg 4) mit dem eigenen Export und Splunk weiter zu verfolgen. Als Einführung lese und höre man die Präsentation Hardcore Lync Analytics (Lync Conference 2014) auf Channel9.
Datenquelle lcscdr DB
Die Schema der lcscdr Datenbank ist prinzipiell bei Microsoft dokumentiert, diese ist aber lückenhaft und es gibt keine Erklärungen zu den Zusammenhängen dazwischen. Weitere Erklärungen zu den Stored Procedures sind überhaupt nicht zu finden (nur tables und views).
dbo.Users: alle Users mit der Zuordnung UserId zu UserUri (=SIP oder Telefonnummer)
dbo.SessionDetails: aller Verbindungen aller Typen mit vollen Details
dbo.VoipDetails: Verbindungen mit mindestens einem PSTN Teilnehmer, der Anrufer ist FromNumberId, der Angerufene ConnectedNumberId.
dbo.Phones: Zuordnung von FromNumberId und ConnectedNumberId zu einer Telefonnummer. Beachte, dass beide Teilnehmer für Enterprise Voice enabled sein müssen und deshalb beide eine Telefonnummer besitzen.
Stored Procedures
Die Reports des Monitoring Servers sind unterschiedlich komplex. Einige bestehen aus umfangreichen Queries, die auf dem Reporting Services Server direkt ausgeführt werden. Andere bestehen nur aus einem einzigen Aufruf einer Stored Procedure plus grafischem Beigemüse. Für die P2P und Conferencing Summary Reports werden nur Stored Procedures aufgerufen. Sowas lässt sich natürlich einfach mit einem Script bewerkstelligen. Der Monitoring Server ist dabei gar nicht mehr involviert.
Zeit- und Datumsberechnungen
Die Zeit- und Datumsberechnungen sind sehr mühsam, da verschiedene Zeitzonen und Zeitformate berücksichtigt werden müssen. Die Reports sind z.T. mit UTC hardcoded programmiert – sie erwarten die Zeitparameter mit einer Angabe der Zeitzone (culture), liefern die Resultate aber immer in UTC zurück. Für Tagesstatistiken sind Fehler aber nicht sehr störend. Eine falsch berechnete Stunde wird noch zum Vortag gerechnet (oder zum nächsten Tag geschlagen).
$currentDate = ( [datetime](get-date -format "yyyy-MM-dd" )).AddMonths(-1) $firstDayMonth = get-date $currentDate -Day 1 -format "yyyy-MM-dd" $lastDayMonth = get-date ((get-date $currentDate -Day 1).AddMonths(1).AddSeconds(-1)) -format "yyyy-MM-dd" # $lastDayMonth = [datetime]$lastDayMonth -format "yyyy-MM-dd hh:mm:ss" $global:startTime = ([string]$firstDayMonth) + " 00:00:00" $global:endTime = ([string]$lastDayMonth) + " 23:59:59"
function getFirstDayLastMonth {
# determine first and last day of last month or current month
# $useLastMonth = 0: use current month
# returns datetime in form "2016-02-26 00:00:00". Reformatting applied later
#
param ( )
$currentDate = ( [datetime](get-date -format "yyyy-MM-dd" )).AddMonths(-1)
$firstDayMonth = get-date $currentDate -Day 1 -format "yyyy-MM-dd"
$lastDayMonth = get-date ((get-date $currentDate -Day 1).AddMonths(1).AddSeconds(-1)) -format "yyyy-MM-dd"
# $lastDayMonth = [datetime]$lastDayMonth -format "yyyy-MM-dd hh:mm:ss"
$global:startTime = ([string]$firstDayMonth) + " 00:00:00"
$global:endTime = ([string]$lastDayMonth) + " 23:59:59"
}
function getFirstHourLastDay {
# determine first and last hour of the previous day
# returns datetime in form "2016-04-07 00:00:00"
param ( )
$previousDay = get-date ([DateTime]::Today.AddDays(-1) ) -format "yyyy-MM-dd"
$global:startTime = ([string]$previousDay) + " 00:00:00"
$global:endTime = ([string]$previousDay) + " 23:59:59"
}
Benötigt man häufiger Umrechnungen in verschiedene Zeitzinen, kann das Script ConvertTimeZones.ps1 nützlich sein.
Wer es noch genauer haben will, liest diesen Artikel: Time conversions in PowerShell (and .NET in general).
Parameterübergabe vorbereiten
Mein Script kann mehrere Statistiken ausgeben. Dazu wird in einer CSV Datei pro Zeile ein sog. Workorder definiert:
sip,monthy,daily,storedprocname,reportfilename,reportsubject,mailrecipient dummy,no,yes,CdrP2PActSumReport,P2P-ActivitySummaryReport,"P2P Anrufe",andreas.bieri1@domain.com dummy,no,yes,CdrConfActSumReport,CdrConfActSumReport,"Konferenzstatistik",andreas.bieri1@domain.com rgs.058xxxxxxx@domain.com,true,false,CdrRGSUsageTrend,RGS-Report-058xxxxxxx,"RGS 058xxxxxxx",andreas.bieri1@domain.com
Das Script liest die Workorders aus der CSV aus und bereite die Parameter entsprechend vor. Jede Stored Procedure hat ihre eigenen obligatorischen oder optionalen Parameter.
import-csv -path $inputFile -delimiter ',' | % {
try {
# read CSV with word orders
$rgsSIP = [string]$_.sip
# switch: daily report with hourly granularity or monthly with daily granularity # in CSV two columns daily, monthly; fill exactly one with yes $rgsMonthly = $false $rgsDaily = $false
# read name of stored procedure $storedprocCSV = [string]$_.storedprocname # prepare output $rgsReportfilename = [string]$_.reportfilename $rgsReportSubject = [string]$_.reportsubject $rgsMailTo = [string]$_.mailrecipient
#check we have a valid input line
if ( $rgsSIP -eq $null -or $rgsMonthly -eq $null -or $rgsDaily -eq $null -or $rgsReportfilename -eq $null) {
throw ('Invalid input line - missing value')
}
# define monthly/daily reporting
# set to true (default false)
if ( ($_.monthly -eq "true") -or ($_.monthly -eq "True") ) {
$rgsMonthly = $true
}
if ( ($_.daily -eq "true") -or ($_.daily -eq "True") ) {
$rgsDaily = $true
}
if ( ($rgsMonthly -eq $false -and $rgsDaily -eq $false) -or ($rgsMonthly -eq $true -and $rgsDaily -eq $true)) {
throw ('Invalid input line - exactly one of monthly/daily option must be set')
}
# define time span $startTime and $endTime
# SQL format does not work: "N'26-02-2016T00:00:00'" use format 2016-03-01 00:00:00 and 2016-03-31 23:59:59
if ( $rgsMonthly -eq "true" ) {
getFirstDayLastMonth
}
if ( $rgsDaily -eq "true" ) {
getFirstHourLastDay
}
# Testing only: specify date directly #$global:startTime = "2016-02-29 22:00:00" #$global:endTime = "2016-03-30 22:00:00"
catch {
$msg = "*** Script catch error: $error[0]"
$msg
$msg >> $logFile
}
SQL Query
Das Kernstück des Scripts führt die Stored Procedures mit den passenden Parameter aus und holt die Resultate in ein Array.
Es wird ein SqlDataAdapter Objekt erzeugt
# create report: prepare SQL query, execute and store result $DataSet = New-Object System.Data.DataSet $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
die Verbindung zur DB wird gebildet
# connect string to db $SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"
Powershell wartet nur 30 Sekunden auf den Query, das reicht in der Regel nicht bei grossen Datenmengen.
# define timeout value: default it will wait only 30s $SqlCmd.CommandTimeout = 300
wir wollen eine stored procedure starten und teilen das auch so mit
# indicate that we are working with stored procedure $SqlCmd.CommandType=[System.Data.CommandType]’StoredProcedure’ # supply the name of the stored procedure #$SqlStoredProcName = "CdrP2PActSumReport" #$SqlStoredProcName = "DiagShowEndpointsByClientApp" #$SqlStoredProcName = "CdrRGSUsageTrend" $SqlStoredProcName = $storedprocCSV $SqlCmd.CommandText = $SqlStoredProcName
jede SP verlangt ihre Parameter; hier ist eine Fallunterscheidung zu machen
# supply standard parameters for SP
$SqlCmd.Parameters.AddWithValue("@_StartTime",[datetime]$global:startTime) | Out-Null
$SqlCmd.Parameters.AddWithValue("@_EndTime",[datetime]$global:endTime) | Out-Null
$SqlCmd.Parameters.AddWithValue("@_Interval",[int]1440) | Out-Null
$SqlCmd.Parameters.AddWithValue("@_WindowSize",[int]1440) | Out-Null
# supply non-standard parameters for SP
if ($SqlStoredProcName -eq "CdrRGSUsageTrend") {
# special treatment
$SqlCmd.Parameters.AddWithValue("@_WorkflowUri", $rgsSIP) | Out-Null
}
Schliesslich wird die Query geschickt:
#————- execute command and close connection $SqlCmd.Connection = $SqlConnection $SqlAdapter.SelectCommand = $SqlCmd $SqlAdapter.Fill($DataSet) | Out-Null $SqlConnection.Close()
Das Resultat wird im Objekt $Dataset gespeichert.
Postprocessing
Für den RGS Report ist es nötig, die Rohdaten noch geeignet zusammenzurechnen. Hier zeigt sich wieder die Stärke der kraftstrotzenden Muschel: ich erweitere das $Dataset mit neuen Spalten, iteriere durch alle Zeilen und berechne die Inhalte. Der Zeitstempel wird noch etwas vereinfacht.
# switch for special parameters, post treatment of results
#
if ($SqlStoredProcName -eq "CdrRGSUsageTrend") {
# add calculated columns
$DataSet.Tables[0].Columns.Add("Day") | Out-Null
$DataSet.Tables[0].Columns.Add("ReceivedCalls") | Out-Null
$DataSet.Tables[0].Columns.Add("SuccessfulCalls") | Out-Null
$DataSet.Tables[0].Columns.Add("OfferedCalls") | Out-Null
$DataSet.Tables[0].Columns.Add("AnsweredCalls") | Out-Null
$DataSet.Tables[0].Columns.Add("TransferredCalls") | Out-Null
($DataSet.Tables[0]) | foreach {
$_.ReceivedCalls = $_.SOCCnt + $_.FOCCnt
$_.SuccessfulCalls = $_.SOCCnt + 0
$_.OfferedCalls = $_.SACCnt + $_.FACCnt
$_.AnsweredCalls = $_.SLCnt + 0
if (-not $_.AnsweredCalls ) { $_.AnsweredCalls = "9999"}
$_.TransferredCalls = $_.STCCnt + $_.FTCCnt
# shorten date (samples column)
$nextDay = [datetime]( ([datetime]$_.Sample).tostring('yyy/MM/dd') )
$_.Day = ( $nextDay.AddDays(1) ).tostring('yyy/MM/dd')
# debug
#Write-Host $_.Day
}
}
Für die Ausgabe werden nur die neuen Spalten ausgewählt.
# special treatments for output formatting
if ($SqlStoredProcName -eq "CdrRGSUsageTrend") {
$DataSet.Tables[0] | select-object -Property Day,ReceivedCalls,SuccessfulCalls,OfferedCalls,AnsweredCalls,TransferredCalls | Export-CSV $datafilename -Delimiter " " -encoding UTF8 -NoTypeInformation
}
Für die anderen Reports ist keine solche Nachbearbeitung nötig.
if ($SqlStoredProcName -eq "CdrP2PActSumReport") {
$DataSet.Tables[0] | Export-CSV -Delimiter ";" $datafilename -encoding UTF8 -NoTypeInformation
}
Report per Mail
Selbstverständlich können die Resultate mit Powershell ganz einfach per Mail verschickt werden. Das brauchen wir aber nicht, da Splunk direkt die geschriebene Datei liest.
# send report
$MailMessage = New-Object system.net.mail.mailmessage
$MailMessage.from = "xxxxx@domain.com"
$MailMessage.To.add($rgsMailTo)
$MailMessage.bcc.add("yyyyy@domain.com")
$MailMessage.Subject = "Test Reporting $rgsReportSubject"
$MailMessage.IsBodyHtml = $false
$MailMessage.Body = "Guten Tag `n `n Angehängt ist die Statistik für die $rgsReportSubject für den vergangenen Monat. `n `n Freundliche Grüsse `n Ihr Admin"
#$MailMessage.Attachments.add($datafilename)
#$SmtpServer.Send($MailMessage)
$MailMessage.Dispose()
Indizierung in Splunk
Die Daten werden von Splunk eingelesen und müssen interpretiert werden. Dazu wurde eine Lync Splunk App entwickelt, die den Speicherordner überwacht und jede Änderung an der Ausgabedatei bemerkt. Die Reportdaten können somit täglich in die gleiche Datei geschrieben werden und ein Cleanup von alten Dateien erübrigt sich. Splunk versteht das CSV Datenformat, sodass die Felder problemlos geparst werden können. Für Berechnungen kann man dann mit schönen und sprechenden Variablennamen auf die Felder zugreifen..