Lync and Skype for Business Call Quality Powershell Script – Getting at CDR/QoE data with Powershell


Hi Everyone

I have been working a bit lately on Enterprise Voice and Call Quality and so wanted to do a bit of digging into the data collected by Lync and Skype for Business in the LCSCDR and QoEMetrics databases. I love the Lync Monitoring reports that you get with the product, but as you might know about me I love to work in Powershell and so I wanted to put together a scriptable method of querying the QoE/CDR data. This blog post is about the script I have put together and how to use it.

General Usage Tips

The script can be configured to use a specific database server and SQL instance. You can update the script's defaults or you can pass arguments to the script to set the SQL Server you wish to connect to. The –SQLServer parameter sets the database server name and the –SQLInstance parameter sets the database instance.

The script also has a couple of other base arguments you can override. The script creates a logfile when running, and also depending on what action you use creates a HTML report for the data returned. To set the folder where the HTML file is created you use the –RptDir parameter. To set the path to the logfile use the –LOGFILE parameter (i.e. "c:\test\gwy.log"). If you want to save files to a path that include spaces in it, make sure you enclose in quotes.

The PoorCall argument is used to restrict the results to just calls that were flagged in the Lync/Skype monitoring databases as poor. A call could be deemed poor for a number of reasons like packet loss, round trip, jitter, a faulty audio device, or an overworked PC. NOTE: While the script can filter for just poor calls, sometimes the reporting data that is returned doesn't provide the exact cause of the poor call. You can restrict to PoorCalls only by setting a value of –PoorCall $True.

The script can be used to log everything it is doing to a Log file. You can set a specific file name for the log or you can use the script defaults ServerName-LyncCalls.log. To set a log file parameter, use the –LogFile parameter in the script. Don't forget to use quotes around paths that include spaces.

I am often interested in highlighting older client versions so I added a –ClientVersion parameter to the script. If you use something like –ClientVersion "2010" it will highlight any Caller or Callee agents that match 2010. You can also use the | to search for multiple client versions, for e.g. "2010|2007".

An example of overriding the defaults is shown below:

.\LyncCalls.ps1 –Action qoe –SQLServer skypesqlag-ip –SQLInstance Skype –RptDir "c:\Lync\Scripts\Enterprise Voice" –Debug $True

Action

The script can be used to perform a few different functions. It includes three different reports and a number of Powershell only functions. The Actions in the script are as follows:

  • QoE – This function is used to query the QoEMetrics database and to look at data focusing on Call Quality data in particular RoundTrip and %Packet Loss. You can also override the threshold values I am using the in this report (1% packet loss and 200ms round trip) for colourising those calls outside of those parameters;
  • VIP – This function is used to generate call quality data for a list of users contained in a particular Active Group (-GroupName). It is focused on providing information for Lync/Skype administrators wishing to keep an eye on poor quality calls made by VIPs in the environment.
  • Subnet – This function is used to generate a call quality report for all subnets (or perhaps a filtered list of subnets) from the Lync/Skype Network Configuration Subnets. This function is useful for reviewing subnets that may be experiencing regular poor calls, so that Lync Administrators can then work with Network Administrators to resolve why poor calls have been experienced in that site;
  • GWY – This function is used to review call quality data by Trunk/Gateway in the environment. The script basically loops through any known trunks in the environment and then reviews calls made to or from that Voice Gateway;
  • P2P – I don't use a lot day to day, but it's intent was to review Peer to Peer calls in the environment.
  • Detailed – I use this function the most when just looking at getting raw data about calls (PSTN, Conference, UC) I can manipulate with Powershell. It brings back some good data and focuses in particular on RoundTrip and Packet Loss.
  • Conf – This action is used to query Conferencing information. It is handy for looking at conferences that took place over the past days, weeks, months;


Limiting by Date Interval

There is going to be a lot of data in the QoE/CDR databases and so you won't want to try and bring it all back with a Powershell/SQL script. It will be important to try and up front filter out all of the data that is irrelevant. One of the best ways to do that is to limit to data that is about calls made recently. To limit data by a date interval you use the –DATELIMITED $True parameter and the –INTERVAL parameter. The –INTERVAL parameter allows the administrator to pass an interval like "7d" for 7 Days or "1h" for 1 hour. That's pretty much the extent of it. I sometimes call with "30d" or "42d", but try and limit searching too far back for the data you are looking for.

Active Calls

The script can also filter on Active Calls. Active Calls are a little tricky to find, but ultimately the way the script works I look for calls with a Start Time and no End Time. While this is largely true for most calls, a record is written to the database when a call starts, and the end time doesn't get written until the end of the call, it happens that if a call finishes due to some sort of client crash (PC blue screen or a client hang etc..) that an End Time will never be written. So this of course makes for some interesting data where data comes back that says an active call has been found that has been going for days (which of course wouldn't be true). So you'll have to do a bit of sanity reviewing of what comes back. The other thing to be aware of is that when a call starts it can take quite a few seconds for a call to be added to the database. To filter calls, use the –Active $True parameter when running the script.

Filtering to Specific Users

The script can be used to filter calls for specific users if you are wanting to filter by a special person (or persons). This is done by using the –IncludeUser $True parameter and the –UserFilter parameter. The –UserFilter parameter is unfortunately not a regular expression as I try to filter who you are after at the SQL Database end using a Like statement. This means that you can use a string like "steve.moore" to search for a sip addresses with steve.moore in it.

Creating Reports

The VIP, GWY and Subnet functions create HTML files that can be used to keep a record of the data returned by the script. The main parameters to use with these functions are the –RptDir parameter (folder where you want HTML files to be written) and the –DateLimited parameter. Typically, with the HTML reports you want to try and filter to recent data to something like a week "7d" or two "14d" as you might end up creating a massive HTML report.

The other thing to be aware of when Creating Reports is that the HTML can be delivered via Email as well. To enable sending of Email you use the –SendEmail $True, -FromAddress, -Recipients, -CC, -BCC, -SMTPHost, -EmailSubject. This is a useful feature if you want to automate the generation of reports and have them sent on a scheduled basis. The attributes are largely self-explanatory but I'll show some examples later in the blog post.

VIP Report

The VIP Report was goaled at collecting data in a HTML report, call quality for staff contained in an Active Directory group. You can see in the example below, the GP-LyncUsers group was being targeted. The script will only review call quality data for users who are Lync enabled in the GP-LyncUsers group. You can change the group name with the –GroupName argument, make sure you use quotes "" when using a group name that includes spaces in its name.

To run the VIPs Report use the following command line as an example (I am dropping a vips.html file in the SfB Internal IIS site so I can then use a browser to go to https://sfb1.contoso.org/vips.html):

.\LyncCalls.ps1 -Action vip -Debug $True -DATELIMITED 1 -INTERVAL 7d -RptDir 'C:\Program Files\Skype for Business Server 2015\Web Components\Internal Website' -Environment Contoso -POORCALL $True -CLIENTVERSION 2010 -RPTFILE vips.html -logfile 'c:\srm\scripts\enterprisevoice\vips.log' -GroupName gp-lyncusers


Subnet Report

The Subnet Report was goaled at collecting data in a HTML report for calls by subnets in the Networking Config of Lync/Skype. Get-CSNetworkSubnet cmdlet is used to retrieve all of the subnets in the environment, so if you haven't defined all of your subnets in the Lync config the script will miss subnets that could be being used in your environment (and experiencing call quality issues).
To run the Subnet Report use the following command line as an example (I am dropping a subnets.html file in the SfB Internal IIS site so I can then use a browser to go to https://sfb1.contoso.org/subnets.html):

.\LyncCalls.ps1 -Action subnet -Debug 1 -DATELIMITED 1 -INTERVAL 7d -RptDir 'C:\Program Files\Skype for Business Server 2015\Web Components\Internal Website' -Environment Contoso -POORCALL 1 -CLIENTVERSION '2010' –RPTFILE subnets.html -logfile 'c:\srm\scripts\enterprisevoice\subnets.log'


Gateway Report

The Gateway Report is targeted at reviewing data in a HTML report for calls by Trunk/Gateway in the Lync/Skype Topology. It is using the Get-CSTrunk cmdlet to find Trunks in the environment. You can use the –PoorCall argument if you wish to only filter calls that were bad associated with that gateway, otherwise use –PoorCall $False and you will get all calls for each gateway for the time period selected.

To run the Gateway Report use the following command line as an example (I am dropping a gwy.html file in the SfB Internal IIS site so I can then use a browser to go to https://sfb1.contoso.org/gwy.html):

.\LyncCalls.ps1 -Action gwy -Debug $True -DATELIMITED 1 -INTERVAL 14d -RptDir 'C:\Program Files\Skype for Business Server 2015\Web Components\Internal Website' -Environment Contoso -POORCALL $False -CLIENTVERSION 2010 -RPTFILE gwy.html -logfile 'c:\srm\scripts\enterprisevoice\gwy.log'


OR To send a HTML Email of the report as well.

.\LyncCalls.ps1 -Action gwy -Debug $True -DATELIMITED 1 -INTERVAL 14d -RptDir 'C:\Program Files\Skype for Business Server 2015\Web Components\Internal Website' -Environment Contoso -POORCALL $False -CLIENTVERSION 2010 -RPTFILE gwy.html -logfile 'c:\srm\scripts\enterprisevoice\gwy.log' –SMTPHost "ex13.contoso.org" –SendEmail $True –FromAddress "gwy-report@contoso.com" –Recipients "steve@contoso.com" –CC "anna@contoso.com" –BCC "steve@contoso.com"


Common Examples of the Script

The other thing to be aware of when Creating Reports is that the HTML can be delivered via Email as well. To enable sending of Email you use the –SendEmail $True, -FromAddress, -Recipients, -CC, -BCC, -SMTPHost, -EmailSubject. This is a useful feature if you want to automate the generation of reports and have them sent on a scheduled basis. The attributes are largely self-explanatory but I'll show some examples later in the blog post.

#The detailed action provides a few more attributes like IPAddress and other stuff. Use a little more sparingly, as is a more intensive query. Here I am querying for detailed information on calls in the last day.
.\LyncCalls.ps1 -action detailed [-PoolName skype.contoso.org] [-SQLSERVER skypesqlag-ip] [-SQLINSTANCE skype] –DateLimited $True –INTERVAL "1d"


An example of running this command

#The detailed action also allows for filtering on client version. You might be interested in seeing who is using call park for instance or UCCAPI/15.0.4517.1004 client version
.\LyncCalls.ps1 -action detailed -Debug $True -DATELIMITED $True -INTERVAL "42d" -CLIENTVERSION "call_park"
.\LyncCalls.ps1 -action detailed -Debug $True -DATELIMITED $True -INTERVAL "42d" -CLIENTVERSION "15.0.4517.1004"


An example of running this command filtering for Call Park calls.

#Have a look at all the client versions being used in the environment.
.\LyncCalls.ps1 -action detailed -DATELIMITED $True -INTERVAL "21d" -SHOWALL $True | sort -unique -Property FromClientVersion | select FromClientVersion
OR .\LyncCalls.ps1 -action detailed -DATELIMITED $True -INTERVAL "21d" -SHOWALL $True | sort -unique -Property ToClientVersion | select ToClientVersion


#Query calls to or from a particular client version (7577 – 2010 client)

.\LyncCalls.ps1 -action detailed -DATELIMITED $True -INTERVAL "21d" -CLIENTVERSION "7577"


#If you throw a measure on the end you can count the rows returned as well.

.\LyncCalls.ps1 -action detailed -DATELIMITED $True -INTERVAL "21d" -CLIENTVERSION "7577" | measure

#You might want to export that data to CSV or HTML and you can do that this way…

.\LyncCalls.ps1 -action detailed -DATELIMITED $True -INTERVAL "21d" -CLIENTVERSION "7577" -SHOWALL $True | ConvertTo-Csv | Out-File results.csv

#You can also have a look at QoEMetrics data using the QoE action.
#In this example I am looking at calls for the past 7days, and sending output to (via $SHOWALL) FL (Format-list) to look at the range of properties returned.
.\LyncCalls.ps1 -action qoe -DATELIMITED $True -INTERVAL "7d" -SHOWALL $True | fl starttime,endtime,sessionstartedtime,poorcall,calleepai,callerpai,CallerUAType,CalleeUAType,callersubnet,calleesubnet,roundtrip,packetlossrate

An example of pulling back the data as raw powershell objects, which you can then play around with. I am just format listing (fl), but the sky is the limit once you have in powershell objects.

#In this example I am looking at calls for the past 7days, and getting all of the data returned as Powershell objects so I can see all the possible attributes and then move on to manipulating with Powershell.

.\LyncCalls.ps1 -action qoe -DATELIMITED $True -INTERVAL "7d" -SHOWALL $True | fl


An example record out of the QoEMetrics view I am querying. You can see there is a lot of attributes (and there are more off the page) for each record you can play around with.

#Have a look at calls and sort by RoundTrip and PacketLossRate so I can see where the worst performers are…(and select just the first 20 records)

.\LyncCalls.ps1 -Action qoe -DATELIMITED $True -INTERVAL "14d" -POORCALL $True -showall $True | sort -property RoundTrip,PacketLossRate -descending | select –first 20 | ft StartTime,EndTime,PacketLossRate,RoundTrip,CallerEndpointSubnet,CalleeEndpointSUbnet,CallerPAI,CalleePAI –auto

#Get all QoE calls with greater than 500ms roundtrip

.\LyncCalls.ps1 -Action qoe -DATELIMITED $True -INTERVAL "14d" -POORCALL $True -showall $True | where {$_.RoundTrip -ge 500} | sort -property RoundTrip,PacketLossRate -descending | ft StartTime,EndTime,PacketLossRate,RoundTrip,CallerEndPointSubnet,CalleeEndpointSubnet,CallerPAI,CalleePAI –auto
\

#Get all QoE calls with greater than 2% packet loss

.\LyncCalls.ps1 -Action qoe -DATELIMITED $True -INTERVAL "14d" -POORCALL $True -showall $True | where {$_.PacketLossRate -ge .02} | sort -property RoundTrip,PacketLossRate -descending | ft StartTime,EndTime,PacketLossRate,RoundTrip,CallerEndPointSubnet,CalleeEndpointSubnet,CallerPAI,CalleePAI –auto


#In this example I am looking at calls for the past 7days, and getting the data that matches calls to a client (callee or caller) in the 192.168.15.0 subnet

.\LyncCalls.ps1 -action qoe -DATELIMITED $True -INTERVAL "7d" -SUBNET "192\.168\.15"

#Having a look at top subnets by CalleeSubnet for the past 7 days (with poor calls)
.\LyncCalls.ps1 -action qoe -DATELIMITED $True -INTERVAL "7d" -PoorCall $True -SHOWALL $True | where {$_.CalleeEndpointSubnet -ne $Null} | Group-Object -Property CalleeEndPointSubnet | Sort -desc Count | Select Count,@{Name="Name"; Expression={$strName=$_.Name;"'$strName'"}} | where {$_.name -ne """} | ft count,name –auto

OR
.\LyncCalls.ps1 -action qoe -DATELIMITED $True -INTERVAL "7d" -PoorCall $True -SHOWALL $True | where {$_.CallerEndpointSubnet -ne $Null} | Group-Object -Property CallerEndPointSubnet | Sort -desc Count | Select Count,@{Name="Name"; Expression={$strName=$_.Name;"'$strName'"}} | where {$_.name -ne """} | ft count,name –auto

In this example I actually go back 42 days, but you won't normally need to go back that far, I just didn't have enough calls to show you decent results. Normally limit to a couple of days.

#Having a look at calls for a particular gateway for the past 6 weeks (poor calls only)
.\LyncCalls.ps1 -action qoe -Debug $True -DATELIMITED $True -INTERVAL "42d" -SHOWALL $True –PoorCall $True | where {$_.CallerPAI -match "pstnsydgwy1" -or $_.CalleePAI -match "pstnsydgwy1"} | ft starttime,endtime,calleepai,callerpai,packetlossrate,roundtrip,ispstncall,isuccall,poorcall,CallerSubnet,calleesubnet

An example of pulling back data for a particular gateway pstnsydgwy1

##number of bad calls for a subnet
.\LyncCalls.ps1 -Action qoe -Debug $True -DATELIMITED $True -INTERVAL "7d" -POORCALL $True -SUBNET "192\.168\.14"

An example of pulling back the data as raw powershell objects, which you can then play around with. I am just format listing (fl), but the sky is the limit once you have in powershell objects.

#Look for Conf Calls involving external parties
.\LyncCalls.ps1 -action qoe -Debug $True -DATELIMITED $True -INTERVAL "42d" -SHOWALL $True | where {$_.CallerPAI -match "litware" -or $_.CalleePAI -match "litware"} | Group-Object -property IsConfCall

#Look for UC Calls involving external parties
.\LyncCalls.ps1 -action qoe -Debug $True -DATELIMITED $True -INTERVAL "42d" -SHOWALL $True | where {$_.CallerPAI -match "litware" -or $_.CalleePAI -match "litware"} | Group-Object -property IsUCCall

#Search for active calls, and get all fields for each call (-ShowAll), and exclude users matching "users-to-exclude" string. Data comes back as powershell objects so you can manipulate returned data.
.\LyncCalls.ps1 -action active -PoolName skype.contoso.org -SQLSERVER skypesqlag-ip -SQLINSTANCE skype -EXCLUDEUSERS $True -Filter "users-to-exclude" -DATELIMITED $True -Interval "7d" -ShowAll $True | ft SessionStartedTime,FromURI,ToURI –auto


An example of running the p2p argument for data over 14days (I don't have many calls in my DB, so use wisely).

#you can also filter by time interval, you can do this for all actions. In this example I am looking at calls in the last 14 days. Filtering for users on the skype.contoso.org pool.
.\LyncCalls.ps1 -action p2p -PoolName skype.contoso.org -SQLSERVER skypesqlag-ip -SQLINSTANCE skype –DateLimited $True –Interval "14d"

#Get Totals for Top Users (by Caller "FromURI")
.\LyncCalls.ps1 -action detailed -PoolName skype.contoso.org -SQLSERVER skypesqlag-ip -SQLINSTANCE skype -DATELIMITED $True -Interval "7d" -SHOWALL $True | Group-Object FromURI | Sort -Property Count | ft count, name –auto

An example of looking at top callers.

#Get Totals for Top Users (by Callee "ToURI")

.\LyncCalls.ps1 -action detailed -PoolName skype.contoso.org -SQLSERVER skypesqlag-ip -SQLINSTANCE skype -DATELIMITED $True -Interval "7d" -SHOWALL $True | Group-Object ToURI | Sort -Property Count -Descending | ft count, name –auto

An example of looking at top callees.

#Look for UC Calls involving external parties
.\LyncCalls.ps1 -action qoe -Debug $True -DATELIMITED $True -INTERVAL "42d" -SHOWALL $True | where {$_.CallerPAI -match "litware" -or $_.CalleePAI -match "litware"} | Where {$_.IsPSTNCall -ne 1 -and $_.IsConfCall -ne 1 -and $_.IsUCCall -ne 1}

#Generate a report of call quality by subnets in the environment (in Lync/Skype network configuration Get-CSNetworkSubnet)
.\LyncCalls.ps1 -action subnet -Debug $True -DATELIMITED $True -INTERVAL "14d" -RptDir C:\srm\Scripts\EnterpriseVoice -Environment Contoso -POORCALL $True

#Generate a report of call quality by subnets in the environment, limiting to just the 192.168.15.0 subnet
.\LyncCalls.ps1 -action subnet -Debug $True -DATELIMITED $True -INTERVAL "14d" -RptDir "C:\srm\Scripts\EnterpriseVoice" -Environment Contoso -POORCALL $False -Subnet "192\.168\.15"

#Generate a report of call quality for a particular set of subnets in the environment, only those calls deemed poor call quality for the last hour for the 192.168.15.0 subnet
.\LyncCalls.ps1 -action subnet -Debug $True -DATELIMITED $True -INTERVAL "1h" -RptDir C:\srm\Scripts\EnterpriseVoice -Environment Contoso -POORCALL $True -Subnet "192\.168\.15"

#Generate a report on call quality for VIP staff
.\LyncCalls.ps1 -action vip -RptDir C:\srm\Scripts\EnterpriseVoice -Debug $True -DATELIMITED $True -INTERVAL "14d" -GROUPNAME gp-lyncusers

#Generate a report on call quality for VIP staff and turn on refresh in case we want to publish HTML to a website.
.\LyncCalls.ps1 -action vip -RptDir C:\srm\Scripts\EnterpriseVoice -Debug $True -DATELIMITED $True -INTERVAL "14d" -GROUPNAME gp-lyncusers

#If you want to run a dashboard you could drop HTML report in the SfB internal website IIS folder and then the resulting HTML can be used as a dashboard.
#If you use a scheduled task to update the HTML, the will automatically update. I have a refresh tag in the HTML which will have IE refresh the page every 60 seconds.
.\LyncCalls.ps1 -action vip -Debug $True -DATELIMITED $True -INTERVAL "14d" -RptDir 'C:\Program Files\Skype for Business Server 2015\Web Components\Internal Website' -POORCALL $True -CLIENTVERSION "rtcc|2010|skype" -RPTFILE "VIPs.html" -GROUPNAME gp-lyncusers

Wrap Up

Well I hope you enjoy the script and get some use out of it. And like always, test this out in a dev/test environment before using in production.

Happy Skype/Lync'ing.

Steve

PS you can download the script here.

Latest version is 1.09 which you can download here – https://gallery.technet.microsoft.com/Powershell-script-5fe0494a

1.05 version is here – http://1drv.ms/1Mi0TZd


Comments (4)

  1. Umesh Radia says:

    Nice Job Steve

  2. maxcoder says:

    Nice job guy. I want see those data on the devices used for calls – phone, headset, laptop mic. Because I am using mixed lync phone devices such as Polycom CX ,VVX, Spectralink, Snom ,Yealink. So Is it possible to add detail device information? So I mean I want to identify Calls drop for many reasons – some of which you don’t control, like PSTN signal quality or Wi-Fi speeds. thanks

    1. hi maxcoder
      I can look at that. So you are after a query for dropped calls for whatever reason, so were terminated for unknown reason? I'll do some digging.
      Steve

      1. maxcoder says:

        Hi Steve,
        Very good news. Well when can see we updated version ? Thanks again,

Skip to main content