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.
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
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