使用Grafana监控Microsoft Teams(二)-摩杜云开发者社区

背景

上一篇文章我们介绍了如何使用Grafana来监控Microsoft Teams的网络性能，但是在运行工具进行数据获取时，只运行了一次，这在一定程度上会导致测试数据的偶然性，那么在本篇文章中，我们将：

优化数据获取
优化Grafana dashboard

步骤

脚本优化

原始脚本只包含了一次工具运行，代码如下：

cd 'C:\Program Files (x86)\Microsoft Skype for Business Network Assessment Tool\'

$a = .\NetworkAssessmentTool.exe
$b = $a
$PacketLossRate = $b[13].Split(":")[1].trim() 
$NetworkLantency = $b[14].Split(":")[1].trim().Split(" ")[0]
$PacketSent = $b[15].Split(":")[1].trim()
$PacketReceived = $b[16].Split(":")[1].trim()
$AverageJitter = $b[17].Split(":")[1].trim().Split(" ")[0]
$PacketReorderRatio = $b[18].Split(":")[1].trim()
$now = Get-Date -Format "yyyy-MM-dd HH:mm:ss.fff"

$obj = New-Object PSObject
$obj | Add-Member -MemberType NoteProperty -Name Date -Value $now
$obj | Add-Member -MemberType NoteProperty -Name PacketLossRate -Value $PacketLossRate
$obj | Add-Member -MemberType NoteProperty -Name NetworkLantency -Value $NetworkLantency
$obj | Add-Member -MemberType NoteProperty -Name PacketSent -Value $PacketSent 
$obj | Add-Member -MemberType NoteProperty -Name PacketReceived -Value $PacketReceived
$obj | Add-Member -MemberType NoteProperty -Name AverageJitter -Value $AverageJitter
$obj | Add-Member -MemberType NoteProperty -Name PacketReorderRatio -Value $PacketReorderRatio
$obj | Add-Member -MemberType NoteProperty -Name Location -Value "VPN"

优化运行次数，变为连续运行5次，之后存储在数组中：

cd 'C:\Program Files (x86)\Microsoft Skype for Business Network Assessment Tool\'

$arr = @()

for($i = 1;$i -le 5;$i++){

$a = .\NetworkAssessmentTool.exe
$b = $a

$PacketLossRate = $b[13].Split(":")[1].trim() 
$NetworkLantency = $b[14].Split(":")[1].trim().Split(" ")[0]
$PacketSent = $b[15].Split(":")[1].trim()
$PacketReceived = $b[16].Split(":")[1].trim()
$AverageJitter = $b[17].Split(":")[1].trim().Split(" ")[0]
$PacketReorderRatio = $b[18].Split(":")[1].trim()


$obj = New-Object PSObject
$obj | Add-Member -MemberType NoteProperty -Name PacketLossRate -Value $PacketLossRate
$obj | Add-Member -MemberType NoteProperty -Name NetworkLantency -Value $NetworkLantency
$obj | Add-Member -MemberType NoteProperty -Name PacketSent -Value $PacketSent 
$obj | Add-Member -MemberType NoteProperty -Name PacketReceived -Value $PacketReceived
$obj | Add-Member -MemberType NoteProperty -Name AverageJitter -Value $AverageJitter
$obj | Add-Member -MemberType NoteProperty -Name PacketReorderRatio -Value $PacketReorderRatio
$arr += $obj

}

优化后效果如下：

使用Grafana监控Microsoft Teams(二)_监控

根据5次测试后获得的数据，取平均值，并调整数据格式，代码如下：

$AvgPacketLossRate = [Math]::Round(($arr | select -ExpandProperty PacketLossRate | Measure -Average | select -ExpandProperty Average) * 100,2)
$AvgNetworkLantency = [Math]::Round(($arr | select -ExpandProperty NetworkLantency | Measure -Average | select -ExpandProperty Average),0)
$AvgPacketSent = $arr | select -ExpandProperty PacketSent | Measure -Average | select -ExpandProperty Average
$AvgPacketReceived = $arr | select -ExpandProperty PacketReceived | Measure -Average | select -ExpandProperty Average
$AvgAverageJitter = [Math]::Round(($arr | select -ExpandProperty AverageJitter | Measure -Average | select -ExpandProperty Average),2)
$AvgPacketReorderRatio = [Math]::Round(($arr | select -ExpandProperty PacketReorderRatio | Measure -Average | select -ExpandProperty Average),2)
$now = Get-Date -Format "yyyy-MM-dd HH:mm:ss.fff"

Dashboard优化

此前多个参数值保持在同一个dashboard中，展示的效果不够清晰，调整为关键参数分开展示，主要截图示例如下：

使用Grafana监控Microsoft Teams(二)_Teams_02

调整dashboard展示形式，修改为Stat，并调整阈值：

使用Grafana监控Microsoft Teams(二)_Teams_03

调整后最终效果如下：

使用Grafana监控Microsoft Teams(二)_Powershell_04

脚本

完整脚本如下：

cd 'C:\Program Files (x86)\Microsoft Skype for Business Network Assessment Tool\'

$arr = @()

for($i = 1;$i -le 5;$i++){

$a = .\NetworkAssessmentTool.exe
$b = $a

$PacketLossRate = $b[13].Split(":")[1].trim() 
$NetworkLantency = $b[14].Split(":")[1].trim().Split(" ")[0]
$PacketSent = $b[15].Split(":")[1].trim()
$PacketReceived = $b[16].Split(":")[1].trim()
$AverageJitter = $b[17].Split(":")[1].trim().Split(" ")[0]
$PacketReorderRatio = $b[18].Split(":")[1].trim()


$obj = New-Object PSObject
$obj | Add-Member -MemberType NoteProperty -Name PacketLossRate -Value $PacketLossRate
$obj | Add-Member -MemberType NoteProperty -Name NetworkLantency -Value $NetworkLantency
$obj | Add-Member -MemberType NoteProperty -Name PacketSent -Value $PacketSent 
$obj | Add-Member -MemberType NoteProperty -Name PacketReceived -Value $PacketReceived
$obj | Add-Member -MemberType NoteProperty -Name AverageJitter -Value $AverageJitter
$obj | Add-Member -MemberType NoteProperty -Name PacketReorderRatio -Value $PacketReorderRatio
$arr += $obj
}

$AvgPacketLossRate = [Math]::Round(($arr | select -ExpandProperty PacketLossRate | Measure -Average | select -ExpandProperty Average) * 100,2)
$AvgNetworkLantency = [Math]::Round(($arr | select -ExpandProperty NetworkLantency | Measure -Average | select -ExpandProperty Average),0)
$AvgPacketSent = $arr | select -ExpandProperty PacketSent | Measure -Average | select -ExpandProperty Average
$AvgPacketReceived = $arr | select -ExpandProperty PacketReceived | Measure -Average | select -ExpandProperty Average
$AvgAverageJitter = [Math]::Round(($arr | select -ExpandProperty AverageJitter | Measure -Average | select -ExpandProperty Average),2)
$AvgPacketReorderRatio = [Math]::Round(($arr | select -ExpandProperty PacketReorderRatio | Measure -Average | select -ExpandProperty Average),2)
$now = Get-Date -Format "yyyy-MM-dd HH:mm:ss.fff"

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 = "SQLAdmin"
$Password = "Password" 

$Newconn = SQLConnectionSQL -server $Server -database $DBName -uid $userName -pwd $Password
$cmd  = "INSERT INTO O365_Monitor_Teams_Network_Test VALUES ('$now','$AvgPacketLossRate','$AvgNetworkLantency','$AvgPacketSent','$AvgPacketReceived','$AvgAverageJitter','$AvgPacketReorderRatio','VPN')"

SQLCommand -SQLConnection $Newconn -command $cmd