Power BI and Skype for Business: Conference counting

Author by Brett Janzen

So the other day, got to thinking it would be cool to see how many users are using the conferencing service in Skype?

Here is how I went about gathering the dataConfPowerBI

 

There are 3 tables that I pull from to get the data. Ill break them out and what each contain.

in the LcsCDR Database there are 3 tables that used.

  1. ConferenceSessionDetails
    This contains all the sessions that are classified as a conference. This includes pulling a 3rd person in to your IM conversations to scheduled meetings.
  2. Users
    This contains Users Uri, this is handy as the ConferenceSessionDetails only contain the UserID
  3. Servers
    This is handy to tie back to conferences running on each server.

 

Fire up good old Power BI and connect it to your Skype SQL server. Load in the 3 tables or more, there are lots of goodies in these tables!

Lets make sure that UserId and ServerId have a relationship between all the tables, this helps when filtering.

ConfRelationshipDB

For this example I am just going to use Clustered Column Chart
Adding the ConfStartTime to the Axis and ConfInstance to the value will get us started. You might be shocked at how many conferences are going on at your organization. Thats because its counting every type of interaction a conference. There is a column in the tabled that is called UriType. If you fire up good old SQL Management Studio and take a look at the tabled called UriTypes you will see a list of what is what.ConfUriType

This is where it threw me for a bit of a loop, actual scheduled Conferences are not considered any of the above. These guys help with the clarity, but the answer is Null or Blank are scheduled conferences

https://blogs.technet.microsoft.com/rischwen/2014/01/17/per-user-conference-joins-grouped-by-uritype-sql-query/

Adding a Page filter to only show the blank ConfGraph.JPG

Just cause we can, I like to add a chart to see percentage of conf held between servers.

 

ConServer

Lastly what I did to see who was joining meeting was added UserUri column
ConfDisplay

ConfUserURIreport

 

The more time I get in Skype CDR and QoE the more beneficial the reports get. Feel free to ask questions.

Brett