Working with device names in QoE data
When you start working with device information in QoE you might find that there is an issue around the naming of devices.
The different Lync client reports the name of the device used for capture and rendering in the QoE report sent to the Monitoring Server. However the name is based on the device name in the operating system. That name might contain localized text and different ordinals based on which USB port the device was connected to on the device. This means that you might end up having a number of different devices seen in QoE, but they are actually all the same type of device.
To illustrate the issue let us assume that you would like to know the AvgSendListenMOS value for all devices over all audio streams in a period of time. You create the necessary SQL query against QoE data and it output something like the sample shown in Table 1.
|
CaptureDeviceName |
AvgSendListenMOS |
NumStreams |
|
|
Auscultadores com Microfone (4- GN 2000 USB OC) |
4,475555 |
36 |
|
|
GN 2000 USB OC |
4,03525 |
40 |
|
|
Headset Microphone (12- GN 2000 USB OC) |
3,661666 |
6 |
|
|
Headset Microphone (2- GN 2000 USB OC) |
3,896413 |
92 |
|
|
Headset Microphone (3- GN 2000 USB OC) |
3,815769 |
26 |
|
|
Headset Microphone (4- GN 2000 USB OC) |
4,063571 |
14 |
|
|
Headset Microphone (5- GN 2000 USB OC) |
3,9172 |
50 |
|
|
Headset Microphone (6- GN 2000 USB OC) |
3,9996 |
25 |
|
|
Headset Microphone (GN 2000 USB OC) |
3,974421 |
95 |
|
|
Headset Microphone(GN 2000 USB OC) |
3,83 |
1 |
|
|
Kopfhörermikrofon (2- GN 2000 USB OC) |
4,253333 |
9 |
|
|
Kopfhörermikrofon (GN 2000 USB OC) |
1,695862 |
58 |
|
|
Microphone sur casque (14- GN 2000 USB OC) |
4,02 |
1 |
|
|
Microphone sur casque (4- GN 2000 USB OC) |
4,45 |
1 |
|
|
Microphone sur casque (8- GN 2000 USB OC) |
4,26 |
2 |
|
|
Microphone sur casque (9- GN 2000 USB OC) |
4,445 |
14 |
|
Table 1. Example of different device names in QoE data
You can see that all these streams actually used the same device, GN 2000 USB OC, but viewed from QoE they are different because of the naming. So how can this be changed to report on just the specific devices?
In this post I'm suggesting a solution based on a PowerShell script to extract the specific device names from the QoE data and store them in a SQL table called TrimDeviceNames in a database called QoEMetricsUtil. You can then use the TrimDeviceNames SQL table in your queries.
Populating the TrimDeviceNames table by running TrimDeviceNamesV10.ps1
The solution use a database called QoEMetricsUtil. You have to create that database before running the PowerShell script.
The attached DeviceQuality.ZIP file contains the TrimDeviceNamesV10.ps1 PowerShell script and it does the following:
- Connects to your monitoring server database and reads all device names out of the Device table
- Extracts the specific device names (please see note below! )
- Drops and Creates the TrimDeviceNames table in the QoEMetricsUtil database
- Stores all the specific device names in the TrimDeviceNames table
As new devices are being used in your deployment you will have to re-run the script to pick-up the new specific device names.
The script has the following mandatory parameters:
- -SqlServerForQoE – the SQL server name hosting your monitoring database
- -SqlServerInstanceForQoE – the SQL server instance name hosting your monitoring database
- -SqlServerForQoEMetricsUtil – the SQL server name hosting the QoEMetricsUtil database
- -SqlServerInstanceForQoEMetricsUtil – the SQL server instance name hosting the QoEMetricsUtil database
An example of running the script is: TrimDeviceNamesV10.ps1 -SqlServerForQoE sql1 -SqlServerInstanceForQoE rtc -SqlServerForQoEMetricsUtil sql1 -SqlServerInstanceForQoEMetricsUtil rtc
You need to run it with an account having appropriate permissions to the Lync monitoring database and to the QoEMetricsUtil database.
Using the TrimDeviceNames table
In your device related queries against QoE data you can now use the TrimDeviceNames table. The way the specific device name is extracted assumes that the resulting device name is a sub-string of the original device name. Based on this assumption you can use sub-string matches to join TrimDeviceNames on CallerCaptureDeviceName and CalleeCaptureDeviceName.
Query to show device quality using TrimDeviceNames table
The output shown in Table 1 was generated by a query reporting AvgSendListenMOS value for all devices over all audio streams in a period of time. I have changed that query to use the TrimDeviceName table. An example of the output of running this changed query is shown Table 2
CaptureDeviceName |
AvgSendListenMOS |
NumStreams |
GN 2000 USB OC |
3,728042 |
470 |
Table 2. Example of output from DeviceQualityNormalizedV13.txt using TrimDeviceNames table
You can see that there is now just one device name row for GN 2000 USB OC and it covers all audio streams using that type of device.
The full query is attached in the DeviceQuality.zip file in the file DeviceQualityNormalizedV13.txt. You run the query by copying all the text from the file into a New Query window inside SQL Management Studio connected to your QoE database. You then change the @beginTime and @endTime to suit your requirements and execute the query.
Notes and Caveats
This is not solution, which will cover all device names out there. The string extraction logic in the PowerShell script is built around the assumption that the specific device name is contained within parentheses in the device name (as shown in Table 1 for GN 2000 USB OC). The logic will most likely need to be changed based on the actual devices used in your Lync deployment. I have only tested with a limited set of device names and there are other string combinations out there. My recommendation is to run the PowerShell script once, and then examine all the resulting rows in the TrimDeviceNames table. If any of them look odd then you have to change the logic in the script to handle that kind of string as well.
The query expects the QoEMetricsUtil database to be in the same SQL instance as the monitoring server database.
The use of sub-string matches might not cover all device names used in your deployment. If the sub-string match does not work for a device name any streams using that device will not appear in the query output.
The PowerShell script has no built-in error handling.
Please consider the impact, running the script and query, might have in your environment depending on your amount of QoE data and the date range you select.
The query is developed against the Lync 2013 QoE schema.
The script and query are sample, comes "as-is" and I'm not providing any support for them.
Acknowledgements
Thanks to Henk.
Comments
- Anonymous
April 29, 2014
I think we can achieve the same results directly within the SQL query by using a CASE statement and string functions within the initial SELECT statement. This worked for me:
WITH fulllyncdevicejoinview
AS (SELECT CASE
WHEN CaptureDevice.DeviceName like '%([0-9]- %' then substring(CaptureDevice.DeviceName, 1, patindex('%(- %', CaptureDevice.DeviceName)) + substring(CaptureDevice.DeviceName, patindex('%(- %', CaptureDevice.DeviceName) + 4, len(CaptureDevice.DeviceName))
WHEN CaptureDevice.DeviceName like '%([0-9][0-9]- %' then substring(CaptureDevice.DeviceName, 1, patindex('%(- %', CaptureDevice.DeviceName)) + substring(CaptureDevice.DeviceName, patindex('%(- %', CaptureDevice.DeviceName) + 5, len(CaptureDevice.DeviceName))
ELSE CaptureDevice.DeviceName
END as 'CaptureDeviceName',
a.SendListenMOS AS SendListenMOS,
m.ConferenceDateTime