PowerBI從Lync或Skype for Business資料庫中分析資料和KPI展現

標籤：雲端運算   雲平台   

但企業使用Lync Server或者Skype for Business時，涉及到多種登陸用戶端版本以及語音通話，使用者狀態等資訊，這些資訊如果提取出來進行分析，相信對於營運管理員來說是非常有協助，可以快速直觀瞭解到目前時間整個企業環境中使用者使用Lync或SFB（Skype for Business）的狀態，雖然Lync Server或SFB Server提供了預設的報表功能，但對於我們管理員來說更像看到定製化的資料並即時或及時展現，那麼下面我就採用PowerBI為大家示範如何進行這些資料的提取以及展現

在此之前呢，需要先串連下SFB的資料庫結構以及每一個資料庫負責什麼功能類型的資料

• RTC執行個體：負責儲存所有後端資料庫，包括主CMS，回應群組配置，位置資料等（RTC也是SFB的預設執行個體名稱）

• rgsconfig：包含回應群組的配置，如代理，工作流程，隊列等

• cpsdyn：包含通話駐留的動態資訊

• rtcshared：承載會議目錄

• rgsdyn：包含回應群組使用的動態“即時”資訊

• XDS：這是SFB的中央管理存放區資料庫（CMS）,包含Lync拓撲，配置和策略

• RTCAB：儲存SFB的地址薄資訊

• RTCXDS：儲存使用者資料的備份

• LIS：儲存Lync伺服器的位置配置資訊，如子網，連接埠，交換器等

• RTCLOCAL執行個體：負責儲存主CMS資料庫的本機複本

• RTC：儲存使用者資訊，如連絡人清單，預定的會議等

• RTCDYN：儲存使用者的動態即時資料，如目前狀態，從什麼裝置登陸等資訊

• LYNCLOCAL執行個體：

• lyss:是一個儲存架構，供不同SFB儲存服務使用者訪問SFB的儲存平台，例如與Exchange整合的存檔資訊

• 歸檔和監控執行個體（MSSQLSEVER）：負責儲存歸檔資訊和監控資料

• LCSlog:儲存立即訊息內容，P2P呼叫和會議資料資訊

• LCSCdr:儲存詳細的呼叫通話記錄

• QoEMetrics:儲存呼叫使用體驗品質的資料

瞭解了各個執行個體和資料庫的儲存用途就方便進行擷取資料來進行分析了

我先來做一個UCMA的用戶端串連統計分析，什麼是UCMA呢？

UCMA是為了整合通訊和協作提供了靈活的Managed 程式碼平台

首先還是先從SFB的SQL資料庫中擷取資料

輸入SFB的SQL資料庫

Select top 1000(cast (RE.ClientApp as varchar (100))) as ClientVersion, R.UserAtHost asUserName, FE.Fqdn

  From rtcdyn.dbo.RegistrarEndpoint RE

  Inner Join rtcdyn.dbo.Endpoint EP onRE.EndpointId = EP.EndpointId

  Inner Join rtc.dbo.Resource R on R.ResourceId= RE.OwnerId

  Inner Join rtcdyn.dbo.FrontEnd FE onEP.RegistrarId = FE.FrontEndId

  Order By ClientVersion, UserName

輸入訪問憑證

載入，然後選擇一個餅圖，把ClientVersion放入圖例中，把ClientVersion放入值裡並選擇計數

PS：因為我這沒有UCMA訪問串連資料故此表徵圖沒有任何資料展示出來

接下來我將再添加一個表徵圖統計一個使用者擁有多種用戶端登陸我們如何展現出來的方法

同樣的先擷取SFB的SQL資料庫

輸入執行個體和資料庫以及查詢

SELECTrtc.dbo.Resource.UserAtHost as 'SIP Address',CAST(rtcdyn.dbo.RegistrarEndpoint.ClientApp as varchar(100)) as 'ClientVersion'

FROMrtcdyn.dbo.RegistrarEndpoint

INNER JOINrtc.dbo.Resource

ONrtcdyn.dbo.RegistrarEndpoint.OwnerId = rtc.dbo.Resource.ResourceId

WHEREIsServerSource = 0

可以看到預覽出來的2條資料，顯示的是當前正在登陸使用的使用者，分別是一個使用者登陸的SFB以及一個SIP話機登陸的SFB，點擊載入

分別添加2個切片器，分別在2個切片器選擇SIP Address和Client Version查看每一個使用者擁有登陸的用戶端版本或者每一個用戶端版本對應哪些使用者在使用的分析展現

再添加一個餅圖查看用戶端佔比情況，這時如果有新的使用者登陸，那麼資料也是可以點擊重新整理擷取到的

接下來我想瞭解統計每一個使用者的狀態，是空閑還是忙碌還是離開等，同樣先擷取SFB的SQL資料庫

SELECTLOWER(UserAtHost) AS UserAtHost, Status=

CASE

WHEN Availability BETWEEN 0 AND 2999 THEN Availability

WHEN Availability BETWEEN 3000 AND 4499 THEN 'Available'

WHEN Availability BETWEEN 4500 and 5999 THEN 'Available - Idle'

WHEN Availability BETWEEN 6000 and 7499 THEN 'Busy'

WHEN Availability BETWEEN 7500 and 8999 THEN 'Busy - Idle'

WHEN Availability BETWEEN 9000 and 11999 THEN 'Do not Disturb'

WHEN Availability BETWEEN 12000 and 14999 THEN 'Be right back'

WHEN Availability BETWEEN 15000 and 17999 THEN 'Away'

WHEN Availability >= 18000 THEN 'Offline'

END,

LastPubTime

FROM rtc.dbo.Resource Resource

RIGHTJOIN (

SELECT

Instance.PublisherId,

SUBSTRING(Instance.Data,CHARINDEX('<availability>', Instance.Data) + 14,CHARINDEX('</availability>', Instance.Data) -CHARINDEX('<availability>', Instance.Data) - 14) AS Availability,

Instance.LastPubTime

FROM (

SELECT PublisherId, cast(substring(Data, 0, 256) AS varchar(256)) ASData, LastPubTime FROM rtcdyn.dbo.PublishedInstance WHERE ContainerNum = 2 ANDCategoryId = 4

UNION ALL

SELECT PublisherId, cast(substring(Data, 0, 256) AS varchar(256)) ASData, LastPubTime FROM rtc.dbo.PublishedStaticInstance WHERE ContainerNum = 2AND CategoryId = 4

)AS Instance

WHERE

CHARINDEX('aggregateState',Data) > 0

) AS UserAndAvailability ON Resource.ResourceId = PublisherId

/*WHERE UserAtHost = '[email protected]' COLLATESQL_Latin1_General_CP1_CI_AS*/

ORDER BY UserAtHost, LastPubTime DESC

點擊載入，可以預覽看到使用者的狀態，這裡大部分都是Offine

建立一個環形圖，並把Status狀態作為圖例，UserAtHost百分比作為值，這樣可以統計看到不線上的佔多少，線上的佔多少

接下來再繼續看看音訊撥號的品質狀況，主要想看看每天平均的丟包率以及MOS意見平均分數

首先先來看看每天的平均丟包率

同樣的，先擷取SFB的SQL資料

select top 10000 convert(date,SessionTime) as [Date], avg(PacketLossRate) as [Packet Loss Rate] from AudioStreamDetailView group by convert(date,SessionTime) order by convert(date,SessionTime)

載入

拖一個堆積面積圖，Date設定軸，把Packet Loss Rate作為值，再開啟資料標籤即可看到走勢

接下來再看看MOS意見平均分數

 selecttop 10000 convert(date,SessionTime) as [Date], avg(OverallAvgNetworkMOS) as[Overall MOS] from AudioStreamDetailView group by convert(date,SessionTime)order by convert(date,SessionTime)

載入

還是選擇堆積面積圖，把Date設定為軸，把Overall MOS設定值並選擇平均值

在上面這張圖做好的基礎上，我再點擊分區圖，把該視圖從堆積面積圖改成分區圖

在分區圖下可以添加走向線，進行資料預測走向

可以設定走向線的參數

同理我再把之前做的每日平均丟包率的堆積面積圖也改成分區圖並添加一個平均線

接下來把每一個可視化視圖都添加下標題便於識別該視圖是顯示的什麼標題

接下來再做一個分析就是我們經常會用到Lync或SFB的螢幕畫面分享，那麼我們需要監測下這個RDP的延遲，一般低於400毫秒是使用者體驗最好的，所以我們也可以基於400毫秒為基準來進行監測

同樣先擷取SFB的SQL資料

selecttop 10000 convert(date,StartTime) as [Date], avg(RDPTileProcessingLatencyAverage) as [Latency] from ASCallAggView group byconvert(date,StartTime) order by convert(date,StartTime)

拉一個分區圖，把Date作為軸，Latency作為值，因為我的是測試環境沒人共用，所以看不到資料，但需要添加一個恒線設定值為400，這樣就看到每一個使用者查看每一幀所需的時間與恒線400毫秒比對是高還是低作為使用者體驗是好是壞的評判依據了

按照同樣的方法再建立一個分區圖，來測量電話會議中應用程式共用時的抖動

selecttop 10000 convert(date,ConferenceDateTime) as [Date], avg (JitterInterArrival)as [Jitter] from QoEReportsASCallListView group byconvert(date,ConferenceDateTime) order by convert(date,ConferenceDateTime)

以Date為軸，Jitter的計數或者平均值來作為值

我會定義三個常量值來分別定義好，一般，差的水平，這3個水平線也用不同的顏色區分，綠色代表好，×××一般，紅色差

接下來再建立一個呼叫報告，按周來統計查看通訊記錄

selecttop 10000 DATEPART(wk,SessionIdTime) as [Week], count(*) as Calls fromVoipDetailsView where year(getdate()) = year(SessionIdTime) and (ToUri ='[email protected]') group by DATEPART(wk,SessionIdTime) order byDATEPART(wk,SessionIdTime)

以week為軸，Calls的計數為值，開啟資料表標籤和走向線

PS：測試環境沒有資料，所以這裡空白（好尷尬啊）

如果我們要為指定的一個使用者建立產生一組資料展示3個月的呼叫和分鐘數，那麼我們需要再添加2個可視化視圖，一個視圖用於資料表，一個使用者電話分鐘數

selecttop 1000 Convert(date, SessionIdTime) as [Date], SessionIdTime,ToPhone as [ToPhone], FromPhone as [From Phone], ResponseTime as [Start Time], EndTime as[End Time], DATEDIFF(mi,ResponseTime, EndTime) as [Minutes] fromVoipDetailsView where InviteTime > getdate()-93 and InviteTime <getdate()+1 and (ToUri = '[email protected]' or FromUri = '[email protected]') order bySessionIdTime desc

拖一個表來顯示這些所有的通話記錄資料，當然還可以再添加一個分區圖來看通話時間的分析，因為測試環境沒有資料，所有我這裡不再示範，基本和上面的分區圖設定是差不多的

接下來加入噪音監測

selecttop 10000 convert(date,SessionTime) as [Date], avg(CallerRxAGCNoiseLevel) as[Caller Noise], avg(CalleeRxAGCNoiseLevel) as [Callee Noise] fromAudioStreamDetailView group by convert(date,SessionTime) order byconvert(date,SessionTime)

載入

選擇折線圖，以日期為軸，噪音為值

最後我再分析一個視訊通話的發送接收丟失率和低速率百分比分析

selecttop 10000 convert(date,ConferenceDateTime) as [Date], avg(SendFrameRateAverage) as [Send Frame Rate], avg(RecvFrameRateAverage) as[Receive Frame Rate], avg(VideoPacketLossRate) as [Video Loss Rate],avg(LowFrameRateCallPercent) as [Low Rate Percent] fromQoEReportsVideoUserAgentView group by convert(date,ConferenceDateTime) order byconvert(date,ConferenceDateTime)

拖一個群組直條圖，以Date為軸，其他均為值，很可惜我這裡也是沒有資料，所以只能把功能介紹給大家了

最後經過我的排版和美化就大功告成了

PowerBI從Lync或Skype for Business資料庫中分析資料和KPI展現