使用Grafana监控Lync/Skype服务器(四)

标签：Plant dbo Lync Grafana --- Skype where DBName

在上一篇文章中，我们使用Grafana从SQL Server的表中取数，然后通过Dashboard实现了对Lync/Skype服务器的点对点通话质量的监控。那么在本篇文章中，我们将继续使用Grafana来实现对Lync/Skype的会议质量进行监控。

如何使用Grafana监控Lync/Skype的会议质量

Skype服务器的实时会议数来源

Skype的通话质量数据存储在后端数据库，我们可以使用内置的视图QoEReportsCallListView来获取到这些数据。

使用Grafana监控Lync/Skype服务器(四)_Skype

会议质量数据获取

第一次筛选 - 获取会议数据

使用如下SQL语句：

declare @date1 datetime
declare @date2 datetime
set @date1 = (SELECT DATEADD(minute,-5,GETUTCDATE()))
set @date2 = (SELECT GETUTCDATE());

SELECT 

	StartTime,
	EndTime,
	ClassifiedPoorCall,
  CallerUAType,
  CalleeUAType,
	CallerPAI,
	CalleePAI,
	CallerEndpointSubnet,
	CalleeEndpointSubnet


  FROM [QoEMetrics].[dbo].[QoEReportsCallListView]
  where StartTime > @date1 and StartTime < @date2 
  and IsConfCall = 1

运行以上语句，我们就可以得到一个类似如下的查询结果：

使用Grafana监控Lync/Skype服务器(四)_Grafana_02

之后我们即可和之前一样，将这些数据存储在一个新的SQL表中，由于创建表格的方法在之前均有讲述，本文将不再赘述，仅展示使用Powershell脚本将数据导入SQL的方法，脚本如下

#Connect to SQL database
Function SQLConnectionWindows
{
    param
    (
        [string]$server,
        [string]$database
 
    )
    $SQLCon = New-Object System.Data.SqlClient.SqlConnection
    $SQLCon.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=SSPI;"
 
    try
    {
        $SQLCon.Open()
        return $SQLCon
    }
 
catch [exception]
    {
        Write-Warning ('Connect to database failed with error message:{0}' -f ,$_)
        $SQLCon.Dispose()
        return $null
    }
}


Function SQLConnectionSQL
{
    param
    (
        [string]$server,
        [string]$database,
        [string]$uid,
        [string]$pwd
 
    )
    $SQLCon = New-Object System.Data.SqlClient.SqlConnection
    $SQLCon.ConnectionString = "Data Source=$server;Initial Catalog=$database;User ID=$uid;pwd=$pwd;"
 
    try
    {
        $SQLCon.Open()
        return $SQLCon
    }
 
catch [exception]
    {
        Write-Warning ('Connect to database failed with error message:{0}' -f ,$_)
        $SQLCon.Dispose()
        return $null
    }
}

Function SQLCommand
{
    param
    (
        [System.Data.SqlClient.SqlConnection]$SQLConnection,
        [string]$command
    )
    $dataset = New-Object System.Data.DataSet
    $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($command,$SQLConnection)
    $dataAdapter.Fill($dataset) | Out-Null
    return $dataset.Tables[0]
} 

$SQLServer = 'SqlServer'
$SQLDBName = 'QoEMetrics'

$Cred = 'Password'
$conn = SQLConnectionWindows -server $SQLServer -database $SQLDBName -uid 'UserName' -pwd $Cred

$SqlQuery = "
declare @date1 datetime
declare @date2 datetime
set @date1 = (SELECT DATEADD(minute,-5,GETUTCDATE()))
set @date2 = (SELECT GETUTCDATE());

SELECT 

	StartTime,
	EndTime,
	ClassifiedPoorCall,
  CallerUAType,
  CalleeUAType,
	CallerPAI,
	CalleePAI,
	CallerEndpointSubnet,
	CalleeEndpointSubnet


  FROM [QoEMetrics].[dbo].[QoEReportsCallListView]
  where StartTime > @date1 and StartTime < @date2 
  and IsConfCall = 1
"


$DataTables = SQLCommand -SQLConnection $conn -command $SqlQuery 

$Server = 'SqlServer'
$DBName = 'DBName'
$userName = "UserName"
$Cred = 'Password'

$Newconn = SQLConnectionSQL -server $Server -database $DBName -uid $userName -pwd $Cred

#更新至数据库
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $Newconn
$bulkCopy.DestinationTableName = "Skype_Conf_Original_Data"
$bulkCopy.WriteToServer($DataTables)

第二次筛选 - 定义通话位置

当我们进行第一次筛选后，可以看到我们获取到了会议质量的一些关键数据：

主叫子网
被叫子网
通话质量

那么接下来，我们就可以对这些数据进行二次筛选，以定义参会用户所属位置。

导入子网数据

和上篇文章一样，我们可以预先将这些网段及其对应的位置信息导入在SQL表中，以供之后取数使用，示例如下：

使用Grafana监控Lync/Skype服务器(四)_Skype_03

判断通话位置

当子网数据导入后，我们就可以使用如下SQL语句来判断通话的位置：

declare @date1 datetime
declare @date2 datetime
set @date1 = (SELECT DATEADD(minute,-5,GETUTCDATE()))
set @date2 = (SELECT GETUTCDATE());

SELECT 

	StartTime,
	EndTime,
	ClassifiedPoorCall,
	CallerUAType,
  CalleeUAType,
	CallerPAI,
	CalleePAI,
	CallerEndpointSubnet,
	CalleeEndpointSubnet,
	
--判断城市--
---根据主叫子网定义城市1--- 
	 When
	 CallerEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where City = '城市1')
	and CalleeUAType = 2
	Then '城市1'

---根据主叫子网定义城市2--- 
	 When
	 CallerEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where City = '城市2')
	and CalleeUAType = 2
	Then '城市2'

---根据被叫子网以定义城市1---  
	  When
	 CalleeEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where City = '城市1')
	and CallerUAType = 2
	Then '城市1'

---根据被叫子网以定义城市2---  
	When
	 CalleeEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where City = '城市2')
	and CallerUAType = 2
	Then '城市2'

---根据主叫子网定义城市1中的Plant---
	Case When
	 CallerEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant1')
	 and CalleeUAType = 2
	 Then 'Plant1'
	  When
	 CallerEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant2')
	 and CalleeUAType = 2 
	 Then 'Plant2' 
	 
---根据主叫子网定义城市2中的Plant---
	 When
	 CallerEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant1')
	 and CalleeUAType = 2 
	 Then 'Plant'
	 When
	 CallerEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant2')
	 and CalleeUAType = 2 
	 Then 'Plant2'
	 
---根据被叫子网定义城市1中的Plant---
	 When
	 CalleeEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant1')
	 and CallerUAType = 2 
	 Then 'Plant1'
	  When
	 CalleeEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant2')
	 and CallerUAType = 2 
	 Then 'Plant2' 

---根据被叫子网定义城市2中的Plant---
	 When
	 CalleeEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant1')
	 and CallerUAType = 2 
	 Then 'Plant1'
	 When
	 CalleeEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant2')
	 and CallerUAType = 2 
	 Then 'Plant2'
	 
	 ELSE 
	'Internet'

	End AS 
	'Plant'

  FROM [DBName].[dbo].[Skype_01_Conf_Original_Data]
  where StartTime > @date1 and StartTime < @date2

运行以上语句，我们就可以得到一个类似如下的查询结果：

使用Grafana监控Lync/Skype服务器(四)_Grafana_04

之后我们即可将这些数据存储在新的SQL表中，使用Powershell脚本将数据导入到SQL的方法如下：

#Connect to SQL database
Function SQLConnectionSQL
{
    param
    (
        [string]$server,
        [string]$database,
        [string]$uid,
        [string]$pwd
 
    )
    $SQLCon = New-Object System.Data.SqlClient.SqlConnection
    $SQLCon.ConnectionString = "Data Source=$server;Initial Catalog=$database;User ID=$uid;pwd=$pwd;"
 
    try
    {
        $SQLCon.Open()
        return $SQLCon
    }
 
catch [exception]
    {
        Write-Warning ('Connect to database failed with error message:{0}' -f ,$_)
        $SQLCon.Dispose()
        return $null
    }
}

Function SQLCommand
{
    param
    (
        [System.Data.SqlClient.SqlConnection]$SQLConnection,
        [string]$command
    )
    $dataset = New-Object System.Data.DataSet
    $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($command,$SQLConnection)
    $dataAdapter.Fill($dataset) | Out-Null
    return $dataset.Tables[0]
} 


$Server = 'SqlServer'
$DBName = 'DBName'
$userName = "UserName"
$Cred = 'Password'
$conn = SQLConnectionSQL -server $Server -database $DBName -uid $userName -pwd $Cred

$SqlQuery = "
declare @date1 datetime
declare @date2 datetime
set @date1 = (SELECT DATEADD(minute,-5,GETUTCDATE()))
set @date2 = (SELECT GETUTCDATE());

SELECT 

	StartTime,
	EndTime,
	ClassifiedPoorCall,
	CallerUAType,
  CalleeUAType,
	CallerPAI,
	CalleePAI,
	CallerEndpointSubnet,
	CalleeEndpointSubnet,
	
--判断城市--
---根据主叫子网定义城市1--- 
	 When
	 CallerEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where City = '城市1')
	and CalleeUAType = 2
	Then '城市1'

---根据主叫子网定义城市2--- 
	 When
	 CallerEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where City = '城市2')
	and CalleeUAType = 2
	Then '城市2'

---根据被叫子网以定义城市1---  
	  When
	 CalleeEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where City = '城市1')
	and CallerUAType = 2
	Then '城市1'

---根据被叫子网以定义城市2---  
	When
	 CalleeEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where City = '城市2')
	and CallerUAType = 2
	Then '城市2'

---根据主叫子网定义城市1中的Plant---
	Case When
	 CallerEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant1')
	 and CalleeUAType = 2
	 Then 'Plant1'
	  When
	 CallerEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant2')
	 and CalleeUAType = 2 
	 Then 'Plant2' 
	 
---根据主叫子网定义城市2中的Plant---
	 When
	 CallerEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant1')
	 and CalleeUAType = 2 
	 Then 'Plant'
	 When
	 CallerEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant2')
	 and CalleeUAType = 2 
	 Then 'Plant2'
	 
---根据被叫子网定义城市1中的Plant---
	 When
	 CalleeEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant1')
	 and CallerUAType = 2 
	 Then 'Plant1'
	  When
	 CalleeEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant2')
	 and CallerUAType = 2 
	 Then 'Plant2' 

---根据被叫子网定义城市2中的Plant---
	 When
	 CalleeEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant1')
	 and CallerUAType = 2 
	 Then 'Plant1'
	 When
	 CalleeEndpointSubnet in (select Address from [DBName].[dbo].[Skype_EnterpriseVoice_Subnet_Map] where Plant = 'Plant2')
	 and CallerUAType = 2 
	 Then 'Plant2'
	 
	 ELSE 
	'Internet'

	End AS 
	'Plant'

  FROM [DBName].[dbo].[Skype_01_Conf_Original_Data]
  where StartTime > @date1 and StartTime < @date2
"

$DataTables = SQLCommand -SQLConnection $conn -command $SqlQuery

#更新至数据库
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $conn
$bulkCopy.DestinationTableName = "Skype_Conf_Result_Data"
$bulkCopy.WriteToServer($DataTables)

创建自动化任务计划（Task Schedule）

在上面的步骤中，我们使用了2个脚本：

脚本1：从Skype后端数据库获取到会议质量数据，存储在SQL表1中。
脚本2：对SQL表1中的数据进行处理，判断位置信息，存储再SQL表2中。

所以我们需要分别创建2个任务计划，以调用前文中的脚本，将数据每隔5分钟将数据导入到SQL表中。由于任务计划在此系列前两篇文章中已经有所展示，将不在此文中赘述。

在Grafana中创建Dashboard

我们导航到Grafana Dashboard界面，之后点击左侧的➕：

使用Grafana监控Lync/Skype服务器(四)_Skype_05

在弹出的New Panel界面，选择Add Query:

使用Grafana监控Lync/Skype服务器(四)_监控_06

我们在Query的配置界面，选择之前添加的对应的SQL数据源，之后分别输入两条SQL查询语句，这两条语句分别对应点对点通话质量好坏，之后选择时序(Time series)格式：

Query 1

select count(*) as 'Good', $__timeEpoch(StartTime) 
from
(
select * from [DBName].[dbo].[Skype_Conf_Result_Data] 
Where Plant = 'Plant1'
and ClassifiedPoorCall = '0'
) as tmp 
where $__timeFilter(StartTime)
group by StartTime

Query 2

select count(*) as 'Poor', $__timeEpoch(StartTime) 
from
(
select * from [DBName].[dbo].[Skype_Conf_Result_Data] 
Where Plant = 'Plant1'
and ClassifiedPoorCall = '1'
) as tmp 
where $__timeFilter(StartTime)
group by StartTime

使用Grafana监控Lync/Skype服务器(四)_SQL_07

在Visualization界面，我们选择Pie Chart，并进行如下配置：

使用Grafana监控Lync/Skype服务器(四)_SQL_08

在General界面，为Dashboard命名，之后保存：

使用Grafana监控Lync/Skype服务器(四)_Dashboard_09

之后我们将得到下面的一个Dashboard：

使用Grafana监控Lync/Skype服务器(四)_Grafana_10

Dashboard最终效果展示

同时我们可以根据保存在SQL表中的会议质量和位置数据，来生成多个类似的图形，之后将其整合。在展示某个城市总数据的同时，也能看到各个plant的数据，类似效果如下：

使用Grafana监控Lync/Skype服务器(四)_Grafana_11

本章总结

在此篇文章中，我们了解了：

如何获取Skype会议质量数据
如何对数据进行二次处理并导入到SQL Server表
如何通过Grafana展示会议质量

在此系列文章的下一部分，我们将继续使用Grafana来展示Skype服务的功能状态。

标签：Plant,dbo,Lync,Grafana,---,Skype,where,DBName
From： https://blog.51cto.com/fantacyzmr/6149661