Following the SfB Video Broadcast from Microsoft i wanted to get my building information into CQD Online so i could take advantage of CQD Online Location Enhanced reporting and drilling down by Building and subnet.

The more info we upload the better the info in CQD Online.

So first i checked this out the link below and gave me a lot of info on CQD.

https://support.office.com/en-us/article/Turning-on-and-using-Call-Quality-Dashboard-in-Skype-for-Business-Online-553fa13c-92d2-4d5c-a3d5-41a073cb047c?ui=en-US&rs=en-US&ad=US

First you need enable CQD on tenant which i had already done so i skipped this part but below are the steps taken from link above.

1. Sign in to your Office 365 organization using an admin account, and then select the Admin tile to open the Admin center.
2. In the left pane, under Admin, Select Skype for Business to open the Skype for Business admin center.
3. On the Skype for Business admin center, select tools in the left pane and then select Skype for Business Online Call Quality Dashboard.
4. On the page that opens, select Login with your Global Administrator account and then provide the credentials for the account when prompted.

Now to upload building information.

Once logged into CQD online go to settings (cog) in top right corner

Select Tenant Data Upload

Here you can upload information.

The data file type currently is limited to Buildings but I’m sure more will come.

To upload Building information it must be formatted in .csv or .tsv file format which specific data types and formatted correctly. (This took me a couple of attempts to get it correct) was way to early or I’m dumb.

Listed formatting on Microsoft site is below. BUT its key to review the items above this table as well !!!

The formatting must also comply with the following !

• The file must be either a tsv file, which means, in each row, columns are separated by a TAB, or a csv file with each column separated by a comma.

• The content of the data file doesn’t include table headers. That means, the first line of the data file should be real data, not headers like “Network” etc.

• For each column, the data type can only be String, Number or Bool. If it is Number, the value must be a numeric value; if it is Bool, the value must be either 0 or 1.

• For each column, if the data type is string, the data can be empty (but still must be separated by an appropriate delimited, i.e. a tab or comma). This just assigns that field an empty string value.

• There must be 14 columns for each row, and each column must have the following data type, and the columns must be in the order listed

• The data file must be a tsv (Tab-separated values) file or a csv (Comma-separated value) file. If using a csv file, any field that contains a comma must be contain quotes or have the comma removed. For example, if your building name is NY,NY, in the csv file it should be entered as "NY,NY".

• The data file must be no larger than 50MB in size.

• For each data file, each column in the file must match a predefined data type

• IMPORTANT: The network range can be used to represent a supernet (combination of several subnets with a single routing prefix). All new building uploads will be checked for any overlapping ranges. If you have previously uploaded a building file, you should download the current file and re-upload it to identify any overlaps and fix the issue before uploading again. Any overlap in previously uploaded files may result in the wrong mappings of subnets to buildings in the reports.

So after a couple of goes here is the formatting from the table above in Excel

and in CSV

192.168.1.0,USA/Seattle/SEATTLE-SEA-1,24,SEATTLE-SEA-1,Contoso,IT Termination,Engineering,Seattle,98001,US,WA,MSUS,1,0

Download my .csv file as template download here

More buildings can be added if required in the CSV on the line below the first building.

For me .csv was easy with excel. To create tsv i used Excel save as type Text (Tab delimited) (*.txt) which you cant upload directly so i had to rename file from .txt to .tsv and that worked.

Key things / My Mistakes i have highlighted in RED above but

• I found REMOVE THE table headings!
• Must be 14 Columns! not 14 ROWs.

When CSV file is ready you have to upload, if there’s errors it will say invalid or actually point out where the error is like Column 3 row 4. The Microsoft page states the upload process utilises Azure Blob storage which is cool.

From CQD dashboard in data upload go to browse and select csv file

Select Start dates for data

You can add end date or leave blank and it will start end date as present day.

Click Upload

Hopefully you will get Upload successful

 

Then the file will be visible in My File Uploads.

From the file uploads you can remove file and download which is handy.

Here i saw the Process Status Saying “In Progress” for a long time and it still is. So ill keep checking back on this. Perhaps there’s a day on the back end that runs at scheduled times.

** update ** – checked on the process state the following morning and it was saying “Processed” some it takes some time for this to be processed it seems.

Only after the building upload was Processed would below information populate. If Buildings are not displayed or Wired / Wi-Fi Inside not populated please wait and check back on your Process status in settings Tenant data upload. Same place you uploaded your CSV or TSV.

So lets check out the difference now my file is processed, if i go to CQD and Server – Client the Wired Inside is populated

Next i checked out the Location-Enhanced Report and this was populated so i could select by buildings i had uploaded

Building – Wired

Buildings –Wi-Fi – Has much more data as my Surface is mostly on Wi-Fi these days.

Download Link for CSV

https://gallery.technet.microsoft.com/Skype-for-Business-Online-f28c57f7

 

Export from On Premise CQD

You can skip this if you haven’t deployed CQD on premises for Skype for Business Server

Already have CQD deployed on premise with SfB Server ? and have added networks and building into CQDArchive database ???

Then there’s a script available to export the information for you. Listed in the link here

It does note the following when using the script as ExpressRoute column has to be added manually! Its included in the script and has value of 1.

The following sample SQL query selects all the required columns. ExpressRoute isn’t in any of the existing QoE tables, it is a column that should be manually added by admin, so temporarily use 1 in the following SQL script). Make sure to use the correct database name for your environment.

Script below i had to update QoEArchive DB names

SELECT  isnull(Network, '') AS Network
      ,REPLACE(REPLACE(isnull(NetworkName.NetworkName, ''), CHAR(13), ''), CHAR(10), '') AS NetworkName
      ,isnull(NetworkRange, '') AS NetworkRange
      ,isnull(Building.BuildingName, '') AS BuildingName
	  ,isnull(OwnerShipType.OwnershipTypeDesc, '') AS OwnershipTypeDesc
	  ,isnull(Building.BuildingOfficeType, '') AS BuildingOfficeType
      ,isnull(BuildingType.BuildingTypeDesc, '') AS BuildingTypeDesc
	  ,isnull(Building.CityName, '') AS City
	  ,isnull(Building.ZipCode, '') AS ZipCode
	  ,isnull(Building.CountryShortCode, '') AS Country
	  ,isnull(Building.StateProvinceCode, '') AS State
	  ,isnull(Building.Region, '') AS Region
	  ,isnull(Building.InsideCorp, 0) AS InsideCorp
        ,1 AS ExpressRoute       
  FROM [QoEArchive3].[dbo].[CqdNetwork] Network
  left join [QoEArchive3].[dbo].[CqdBuilding] Building
  on Network.BuildingKey = Building.BuildingKey
  left join  [QoEArchive3].[dbo].[CqdBuildingType] BuildingType
  on BuildingType.BuildingTypeId = Building.BuildingTypeId
  left join [QoEArchive3].[dbo].[CqdBuildingOwnerShipType] OwnerShipType
  on OwnerShipType.OwnershipTypeId = Building.OwnershipTypeId
  left join [QoEArchive3].[dbo].[CqdNetworkName] NetworkName
  on NetworkName.NetworkNameID = Network.NetworkNameID

In my lab i had CQD deployed but i hadn’t uploaded any building information so took the script and on first run i got the below error. The RED lines gave it away. Wrong DB NAME!

This was because the QoEArchive DB was not by default called QoEArchive3 as it is in the script. I updated the database name to match my QoEArchive Database name and it ran ok.

As you can see my lab had no information so i went and added some from this great script here

Added Building using SQL script (This was for testing export only) If you do not have information populated then you don’t have to do this step. Its just showing the export process from an on premise CQD deployment only.

Save results as CSV

Open CSV – Needs a bit of editing but the information is there.

 

Hope this helps.

Martin