Displaying Network and Building Information in the Call Quality Dashboard


In the Use Call Quality Dashboard TechNet section we describe the schema for additional tables in QoEArchive that CQD uses to store Network and Building information. The information can be used in the CQD Portal using the measurements/filters:

  • [First Network Name].[Network Name]
  • [First Network Name].[Network Type]
  • [First Building].[Building Key]
  • [First Building].[Building Name]
  • [First Building].[Building Office Type]
  • [First Building].[Building Type]
  • [First Building].[City Name]
  • [First Building].[Country]
  • [First Building].[Inside Corp]
  • [First Building].[Ownership Type]
  • [First Building].[Region]
  • [First Building].[State]
  • [First Building].[Zip Code]
  • [Second Network Name].[Network Name]
  • [Second Network Name].[Network Type]
  • [Second Building].[Building Key]
  • [Second Building].[Building Name]
  • [Second Building].[Building Office Type]
  • [Second Building].[Building Type]
  • [Second Building].[City Name]
  • [Second Building].[Country]
  • [Second Building].[Inside Corp]
  • [Second Building].[Ownership Type]
  • [Second Building].[Region]
  • [Second Building].[State]
  • [Second Building].[Zip Code]

In my lab I've used the SQL statements below to create network and building information. I used the SQL Management Studio to execute them against the QoEArchive database. The information will show up for calls made after the network and building information has been inserted into the QoEArchive.

NOTE: CQD use the Network and the Network Range to correlate the Lync/Skype for Business clients IP address to a network. When the clients sends it quality report (VQReport) to the server it contains the actual IP address in use. CQD takes this IP address and correlates it to networks by using Network and the NetworkRange. For example if the clients IP address is 10.1.1.15, CQD will be able to correlate that to the TR22-Network-1 network given the example below.

 

USE [QoEArchive]
GO

-- Building Type
DECLARE @nextBuildingId AS INT;
SELECT @nextBuildingId = MAX(BuildingTypeId) FROM [dbo].[CqdBuildingType];

INSERT INTO [dbo].[CqdBuildingType]
 ([BuildingTypeId],[BuildingTypeDesc])
VALUES
 (@nextBuildingId+1,'TR22 Hang-Out');

SELECT [BuildingTypeId]
      ,[BuildingTypeDesc]
FROM [QoEArchive].[dbo].[CqdBuildingType]
WHERE BuildingTypeDesc = 'TR22 Hang-Out';

-- Building Ownership Type
DECLARE @nextOwnerShipId AS INT;
SELECT @nextOwnerShipId = MAX(OwnershipTypeId) FROM [dbo].[CqdBuildingOwnershipType];

INSERT INTO [dbo].[CqdBuildingOwnershipType]
 ([OwnershipTypeId],[OwnershipTypeDesc],[LeaseInd],[Owner])
VALUES
 (@nextOwnerShipId+1,'State Owned',2,'Washington State');

SELECT * FROM [QoEArchive].[dbo].[CqdBuildingOwnershipType]
WHERE [Owner] = 'Washington State';

-- Network Name and Type
INSERT INTO [dbo].[CqdNetworkName]
 ([NetworkName],[NetworkType])
VALUES
 ('TR22-Network-1','EventNet');

SELECT * FROM [QoEArchive].[dbo].[CqdNetworkName]
WHERE [NetworkName] = 'TR22-Network-1';

-- Building
DECLARE @ownerShipTypeId AS INT;
DECLARE @buildingTypeId AS INT;

SELECT @nextBuildingId = MAX(BuildingKey) FROM [dbo].[CqdBuilding];
SELECT @ownerShipTypeId = OwnershipTypeId FROM [dbo].[CqdBuildingOwnershipType]
WHERE [Owner] = 'Washington State';
SELECT @buildingTypeId = BuildingTypeId FROM [QoEArchive].[dbo].[CqdBuildingType]
WHERE BuildingTypeDesc = 'TR22 Hang-Out';

INSERT INTO [dbo].[CqdBuilding]
 ([BuildingKey],[BuildingName],[BuildingShortName],[OwnershipTypeId],[BuildingTypeId]
  ,[CityName],[ZipCode],[CountryShortCode],[StateProvinceCode],[InsideCorp]
  ,[BuildingOfficeType],[Region])
VALUES
 (@nextBuildingId+1,'Washington State Convention Center','WSCC',@ownerShipTypeId,@buildingTypeId
  ,'Seattle','98101','US','WA',0
  ,'Events','PUGET SOUND');

SELECT * FROM [dbo].[CqdBuilding] WHERE BuildingShortName = 'WSCC';

-- Subnet
DECLARE @buildingKey AS INT;
DECLARE @networkNameId AS INT;

SELECT @buildingKey = BuildingKey FROM [dbo].[CqdBuilding] WHERE BuildingShortName = 'WSCC';
SELECT @networkNameId = NetworkNameId FROM [dbo].[CqdNetworkName] WHERE NetworkName = 'TR22-Network-1';

INSERT INTO [dbo].[CqdNetwork]
 ([Network],[NetworkRange],[NetworkNameID],[BuildingKey],[UpdatedDate])
VALUES
 ('10.1.1.0','24',@networkNameId,@buildingKey,'2016-02-1');

SELECT * FROM [dbo].[CqdNetwork] WHERE Network = '10.1.1.0';

 

Comments (1)

  1. Robert Peters says:

    Thanks Jens for your description.
    Your articles about the CQD helped me much!

    I think in the CqdNetworkName, the NetworkNameId to link it with the CqdNetwork table is missing.

    Jens>Thanks Robert! The CqdNetworkName table is defined such that it won't allow you to insert the NetworkNameID. It's therefore a 3-step process, where you first need to create the network names, find the id's and then later reference the ids in the other inserts.

Skip to main content