Home » SFBS » CQD » Improving SFBS experience – Call Quality Dashboard (CQD)

Skype for business reporting are great, but as discussed earlier works on real time monitoring database and are predefined. CQM other hand gives the flexibility of offline analysis but again predefined and each time need info from a different time windows, have to run against the monitoring database again. Skype for business server introduced Call Quality Dashboard (CQD) to overcome some of these challenges. It copies the monitoring database into a kind of local data warehouse and using SQL Server Analysis Services it provides aggregate usage and call quality information as well as allowing filtering and pivoting on the fly. However, the tool requires populating the network definition manually into its local database.

First the deployment of required SQL roles. Here in the script, S drive is the tiered drive from storage spaces and T drive is the SSD only space for SQL tempDB.


All the procedure run through SQL agent and hence this needs to be set to start mode Automatic. I have deployed all the roles of CQD into a single server as otherwise multiple SQL installation required for each server.

Frist things is to check that SQL Agent job user configured during installation is actually able to read from monitoring database. It’s worth connecting to monitoring database from the CQD server with this ID and verify read operation on monitoring database. Once the installation completes, it wold take some time for the Cube to populate locate database from monitoring server database. Meanwhile, Build and network database on CQD can be populated.

For Building, a table like this saved in form of CSV would help to automate inserting into SQL database of CQD.

BuildingKey BuildingName BuildingShortName OwnershipTypeId BuildingTypeId Latitude Longitude CityName ZipCode CountryShortCode StateProvinceCode InsideCorp BuildingOfficeType Region
1 PKPnotesCampus HQ 1 1 12.123 12.123 mycity 12345 IN NA 1 CoporateCampus India

Defining Building requires Ownership and BuidlingType definition, which I am doing through a static script.

Invoke-Sqlcmd -Database QoEArchive "INSERT INTO [dbo].[CqdBuildingType] ([BuildingTypeId],[BuildingTypeDesc]) VALUES ('1','SEZ Campus')"
Invoke-Sqlcmd -Database QoEArchive "INSERT INTO [dbo].[CqdBuildingType] ([BuildingTypeId],[BuildingTypeDesc]) VALUES ('2','STPI Campus')"
Invoke-Sqlcmd -Database QoEArchive "INSERT INTO [dbo].[CqdBuildingType] ([BuildingTypeId],[BuildingTypeDesc]) VALUES ('3','Rental Office')"
Invoke-Sqlcmd -Database QoEArchive "INSERT INTO [dbo].[CqdBuildingOwnershipType] ([OwnershipTypeId],[OwnershipTypeDesc]) VALUES ('1','PKPnotes Owned')"
Invoke-Sqlcmd -Database QoEArchive "INSERT INTO [dbo].[CqdBuildingOwnershipType] ([OwnershipTypeId],[OwnershipTypeDesc]) VALUES ('2','Rental')"

And the script to read this Building definition CSV file and update into CQD database.

import-csv 'S:\Building n Network Data\Buidlinglist.csv' |%{

$CQD_buildingkey = Invoke-Sqlcmd -Database QoEArchive "SELECT [BuildingKey] FROM [dbo].[CqdBuilding] WHERE [BuildingShortName] = $BuildingShortName"

if ( !($CQD_buildingkey) ) {
Invoke-Sqlcmd -Database QoEArchive "INSERT INTO [dbo].[CqdBuilding] ([BuildingKey],[BuildingName],[BuildingShortName],[OwnershipTypeId],[BuildingTypeId],[Latitude],[Longitude],[CityName],[ZipCode],[CountryShortCode],[StateProvinceCode],[InsideCorp],[BuildingOfficeType],[Region]) VALUES ($BuildingKey,$BuildingName,$BuildingShortName,$OwnershipTypeId,$BuildingTypeId,$Latitude,$Longitude,$CityName,$ZipCode,$CountryShortCode,$StateProvinceCode,$InsideCorp,$BuildingOfficeType,$Region)"
Else {
Invoke-Sqlcmd -Database QoEArchive "UPDATE [dbo].[CqdBuilding] SET [BuildingName] = $BuildingName ,[BuildingShortName] = $BuildingShortName ,[OwnershipTypeId] = $OwnershipTypeId ,[BuildingTypeId] = $BuildingTypeId,[Latitude] = $Latitude ,[Longitude] = $Longitude ,[CityName] = $CityName ,[ZipCode] = $ZipCode ,[CountryShortCode] = $CountryShortCode ,[StateProvinceCode] = $StateProvinceCode ,[InsideCorp] = $InsideCorp ,[BuildingOfficeType] = $BuildingOfficeType ,[Region] = $Region WHERE [BuildingKey] = $BuildingKey"

For network definition, I will reuse the table created for defining skype for business sites and subnet based on information from IPAM.

Location NetSubnet Name Type Site Region Country CQDBuildingKey
EC EC-projectsfb-BB LAN BLR_EC India India 2

Script for the same

Import-Csv 'S:\Building n Network Data\SFBSIPdata.csv' |%{

$Buildkey = "'"+$_.CQDBuildingKey+"'"
$IPRrange = "'"+ $_.NetSubnet.split("/")[0] +"'"
$IPMask = "'"+ $_.NetSubnet.split("/")[1] +"'"
$sqltime = "'"+(get-date)+"'"
$NetworkName = "'"+ $_.Name +"'"

if ( $_.Type -eq "WiFi" ) {$netype = "'WiFi'"}
Else { $netype = "'Ethernet'"}

$CQD_NetworkName = Invoke-Sqlcmd -Database QoEArchive "SELECT [NetworkNameID],[NetworkName],[NetworkType] FROM [dbo].[CqdNetworkName] WHERE [NetworkName] = $NetworkName"

if ( $CQD_NetworkName ) {
if (!( $netype -eq "'$CQD_NetworkName.NetworkType'" )) {
Invoke-Sqlcmd -Database QoEArchive -Query "UPDATE [dbo].[CqdNetworkName] SET [NetworkType] = $netype WHERE [NetworkName] = $NetworkName"
} Else
Invoke-Sqlcmd -Database QoEArchive -Query "INSERT INTO [dbo].[CqdNetworkName] ([NetworkName],[NetworkType]) VALUES ($NetworkName,$netype);"
$CQD_NetworkName = Invoke-Sqlcmd -Database QoEArchive -Query "SELECT [NetworkNameID],[NetworkName],[NetworkType] FROM [dbo].[CqdNetworkName] WHERE [NetworkName] = $NetworkName"
$CQD_NetworkNameID = "'" + $CQD_NetworkName.NetworkNameID +"'"
$CQD_Network = Invoke-Sqlcmd -Database QoEArchive "SELECT [NetworkNameID],[BuildingKey] FROM [dbo].[CqdNetwork] WHERE [Network] = $IPRrange"

if ( $CQD_Network ) {
Invoke-Sqlcmd -Database QoEArchive -Verbose -Query "UPDATE [dbo].[CqdNetwork] Set [NetworkNameID] = $CQD_NetworkNameID,[BuildingKey] = $Buildkey WHERE [Network] = $IPRrange"
Invoke-Sqlcmd -Database QoEArchive -Verbose -Query "INSERT INTO [dbo].[CqdNetwork] ([Network],[NetworkRange],[NetworkNameID],[BuildingKey],[UpdatedDate]) VALUES ($IPRrange,$IPMask,$CQD_NetworkNameID,$Buildkey,$sqltime)"

Now I can view the default reports by browsing into https://CQDserver/CQD . Using the flexibility of cube architecture of CQD, I am going to define a report that lists the IP address where Building name is not defined for the IP subnet. So that I can use these reports to update CQD, skype for business sites and subnet as well as IPAM inventory. Like this CQD can be used to get real-time information on every aspects of skype for business that help to address the problem areas and thus the improve the service quality and user experience.

2 Replies to “Improving SFBS experience – Call Quality Dashboard (CQD)”

  1. anjali says:

    Hi.. Useful blog.
    can you please tell how to use the cqd for troubleshooting. In lync reporting we get the option to get the detailed report for any issue. is it possible in CQD.

  2. Matt says:

    I am trying to run your script to import building data but I keep receiving an error.

    import-csv : the Member “New York City” is already present.

    When I open SQL Management Studio and open the QoEArchive database, and then the dbo.CqdBuilding table (Select top 1000 rows) I dont get a return of results, other than what appears to be the default entry.

    Building Key 0, BuildingName Unknown, etc etc etc.

    Once I import all of the building data shouldn’t it be present in this table?

    Thanks for the blog post I haven’t found much info about this topic. I like your scripts I am just struggling to get them to work.

Leave a Reply

Your email address will not be published. Required fields are marked *