Powerbi analyzing data and KPI presentation from a Lync or Skype for business database

Tag: Cloud Platform for Cloud computing

However, when the enterprise uses Lync Server or Skype for business, it involves a variety of landing client versions, as well as voice calls, user status and other information, if extracted for analysis, I believe it is very helpful to the operations manager, You can quickly and intuitively understand the current time of the entire enterprise environment in which users use Lync or SFB (Skype for business) status, although Lync Server or SFB server provides default reporting functionality, But for our administrators more like to see the customized data and real-time or timely display, then I will use Powerbi to show you how to extract this data and show

Before that, it was necessary to connect the database structure of the SFB and the data of what function type each database was responsible for.

• RTC Instance: Responsible for storing all back-end databases, including primary CMS, Response group configuration, location data, etc. (RTC is also the default instance name for SFB)

• Rgsconfig: Contains the configuration of the response group, such as agents, workflows, queues, etc.

• Cpsdyn: Contains dynamic information for Call Park

• Rtcshared: Hosting the Conference directory

• Rgsdyn: Contains dynamic "real-time" information used by response groups

• XDS: This is the central management store database (CMS) for SFB, which contains the Lync topology, configuration, and policies

• RTCAB: Storing SFB's address book information

• Rtcxds: Storing backup of user data

• LIS: Store location configuration information for Lync Server, such as subnets, ports, switches, etc.

• Rtclocal instance: Responsible for storing the local copy of the primary CMS database

• RTC: Store user information, such as contact list, scheduled meetings, etc.

• RTCDYN: Store the user's dynamic real-time data, such as the current status, from what device login and other information

• Lynclocal instances:

• Lyss: is a storage framework for different SFB storage service users to access the SFB storage platform, such as archiving information integrated with Exchange

• Archiving and Monitoring Example (Mssqlsever): Responsible for storing archived information and monitoring data

• LCSlog: Storage of instant message content, peer-to call and meeting data information

• LCSCDR: Storage of detailed call call logs

• Qoemetrics: Data that stores the quality of call usage experience

Understanding the storage usage of each instance and database makes it easy to get data for analysis.

I'm going to do a UCMA. Client connection statistic analysis, what is UCMA?

UCMA provides a flexible managed-code platform for Unified Communications and collaboration

First, get the data from the SFB SQL database.

Enter the SQL database for SFB

Select Top (CAST (RE) ClientApp as varchar) 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

Enter Access credentials

Load, then select a pie chart, put the clientversion into the legend, put the clientversion into the value and select Count

PS: Because I don't have UCMA access to the connection data so the icon doesn't show any data.

Next I will add an icon to count a user has a variety of client login how we show the way

The same SQL database that gets the SFB first

Input instances and databases and queries

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

FROMrtcdyn.dbo.RegistrarEndpoint

INNER JOINrtc.dbo.Resource

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

Whereisserversource = 0

You can see the preview of the 2 data, showing the user is currently logged in use, respectively, a user logged in the SFB and a SIP phone login SFB, click to load

Add 2 slicers Separately, select SIP address and client version in 2 slicers to see each user has a logged-in client or each client version corresponding to which users are using the analysis display

Add a pie chart to see the client ratio, then if a new user login, then the data can be clicked Refresh to get to the

Next I want to know the stats of each user, whether it's idle or busy or leaving, and get the SQL database of SFB first.

Selectlower (Userathost) as Userathost, status=

Case

When availability between 0 and 2999 then availability

When availability between, 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 ' does not disturb '

When availability between 12000 and 14999 then ' being right back '

When availability between 15000 and 17999 then ' away '

When availability >= 18000 and 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) as availability,

Instance.lastpubtime

From (

SELECT PublisherID, CAST (substring (Data, 0, max) as varchar) Asdata, lastpubtime from Rtcdyn.dbo.PublishedInstance W Here Containernum = 2 Andcategoryid = 4

UNION All

SELECT PublisherID, CAST (substring (Data, 0,) as varchar) 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

Click Load, you can preview the status of the user, most of which are offine

Create a doughnut chart and put the status state as the legend, userathost percent as the value, so that you can see how much is not the line, how much online

Then continue to look at the quality of the audio call, mainly want to see the average drop rate per day and the average MOS opinion score

First, let's look at the average packet loss rate per day.

Similarly, get the SQL data for SFB first

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)

Load

Drag a stacked area chart, date to set the axis, packet Loss rate as the value, and then open the Data tab to see the trend

Let's take a look at the MoS opinion average score

Selecttop 10000 CONVERT (date,sessiontime) as [date], avg (Overallavgnetworkmos) As[overall MOS] From Audiostreamdetailview GROUP by CONVERT (date,sessiontime) Order by convert (Date,sessiontime)

Load

Or select a stacked area chart, set date to axis, set overall MOS value and select Average

On the basis of the above diagram, I then click on the partition map to change the view from the stacked area chart to the partition map.

The trend line can be added under the partition map to predict the data

You can set the parameters of the trend line

In the same vein, I'm going to change the stacked area chart of the average daily packet loss rate to a partition chart and add an average line.

Next, add a title to each visual view to make it easy to identify what title The view is displayed in.

The next analysis is that we often use Lync or SFB screen sharing, then we need to monitor this RDP delay, generally less than 400 milliseconds is the best user experience, so we can also be based on 400 milliseconds to monitor the baseline

Also get the SQL data for SFB first

Selecttop 10000 CONVERT (date,starttime) as [date], AVG (rdptileprocessinglatencyaverage) as [Latency] from Ascallaggview Group Byconvert (date,starttime) Order by convert (Date,starttime)

Pull a partition graph, the date as the axis, latency as the value, because my test environment is no one to share, so I can't see the data, but need to add a constant line setting value of 400, This will see each user to see the time required for each frame with the constant line 400 MS is high or low as the user experience is good or bad judging basis

Create a partition diagram in the same way to measure jitter in application sharing in a conference call

Selecttop 10000 CONVERT (date,conferencedatetime) as [date], AVG (jitterinterarrival) as [jitter] from Qoereportsascalllistview Group Byconvert (date,conferencedatetime) Order by convert (Date,conferencedatetime)

Use date as axis, jitter count or average as value

I will define three constant values to define well, general, poor level, the 3 horizontal lines are also different colors, green is good, XXX general, red difference

Next, create a call report to view the call history by week

Selecttop 10000 DATEPART (wk,sessionidtime) as [Week], COUNT (*) as Calls Fromvoipdetailsview where year (getdate ()) = year (S Essionidtime) and (Touri = ' [email protected] ') Group by DATEPART (wk,sessionidtime) Order Bydatepart (Wk,sessionidtime)

With week as the axis, the count of calls is the value, open the data table label and the trend line

PS: The test environment has no data, so there is no space (awkward AH)

If we want to create a set of data to show 3 months of calls and minutes for a given user, then we need to add 2 more visualizations, one view for the data table, one for the number of minutes for the user

Selecttop (date, sessionidtime) as [date], sessionidtime,tophone as [Tophone], fromphone as [from Phone], RESP Onsetime 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 protecte D] ') Order Bysessionidtime desc

Drag a table to display all of these call log data, of course, you can add a partition map to see the talk time analysis, because the test environment has no data, all I do not show here, the basic and the above partition chart settings are similar

Next Add noise monitoring

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)

Load

Select a line chart with a date axis and a noise value

Finally, I'll analyze a video call. Send receive loss rate and low rate percentage analysis

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 [Percent] Fromqoereportsvideouseragentview GROUP by convert (date, Conferencedatetime) Order Byconvert (Date,conferencedatetime)

Drag a clustered Column chart, with date as the axis, the others are values, unfortunately I have no data here, so I can only introduce the function to everyone

Finally, after my typography and landscaping, I'm done.

Powerbi analyzing data and KPI presentation from a Lync or Skype for business database