Bridge Communications

Saturday, July 23, 2016

Using Power BI with Skype for Business - Part I

In this series I am going to show you how to use Microsoft's business intelligence reporting tool Power BI to quickly create reports from your Skype for Business or Lync data.  If you are not familiar with SQL permissions, queries, or your Skype for Business SQL configuration, I would recommend you stop reading, as these things are vital to system function and should not be tinkered with in a production environment by amateurs.

Power BI

Microsoft Power BI comes in 2 flavors, basic and pro.  Pro is included in the E5 O365 license, or can be purchased separately for $10 a month.  You can get a full list of the differences here.

We are going to use pro in our example because it works with live data.  That means it can use an personal or enterprise gateway to connect and query you live running Skype for Business data, rather than you have to import it and republish every time you want to view a report.  To begin download the Power BI desktop application.

Skype for Business SQL

The data we are going to query today is going to reside in the RTCLOCAL Sql instance on our front end server.  Make sure the user you are going to attach was has the proper rights, this instance is Windows Auth only by default.  Once you have confirmed data access with SSMS or some other tool return to Power BI Desktop.

Setting up our data




Under the get data section in Power BI Desktop we have to choose SQL Server Database.  Inter your sql instance name in the server field (yourserver\rtclocal) and let's put rtcdyn in the database optional field.  Make sure you have directquery selected and put this in your query.

Select top 1000 (cast (RE.ClientApp as varchar (100))) as ClientVersion, R.UserAtHost as UserName, FE.Fqdn
From rtcdyn.dbo.RegistrarEndpoint RE
Inner Join rtcdyn.dbo.Endpoint EP on RE.EndpointId = EP.EndpointId
Inner Join rtc.dbo.Resource R on R.ResourceId = RE.OwnerId
Inner Join rtcdyn.dbo.FrontEnd FE on EP.RegistrarId = FE.FrontEndId
Order By ClientVersion, UserName

Normally I would not add the top 1000 part, but BI gets picky if you don't have top something, so make it larger than the number of connected clients you have.


Now that we have our query built we can return to the main page in Power BI desktop.  Let's pick our data, and select a pie chart.





Once we have made the following settings, BI will automatically generate us a pie charge breakdown of what type of clients we have currently attached.  As you can see I have a rather large UCMA program named Jarvis that is creating most of my authenticated Skype for Business endpoints.



Next week we will continue with some more advanced reporting, I hope this gives you enough information to get started making reports on your own.


Doug Routledge, C# Lync, Skype for Business, SQL, Exchange, UC Developer
BridgeOC Twitter - @droutledge @ndbridge








9 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. Do you know if I can do something similar with Skype for Business online? I really need to create user activity dashboards for our sales team and we do not have an server on premises.

    ReplyDelete
    Replies
    1. There is not database access there, and the reporting is very limited. If you are in hybrid configuration with local PSTN you can do it the same way as this blog. If you are pure cloud you will have to wait for something from Microsoft. Sorry I don't have a better answer.

      Delete
  4. Thanks for the post! I am trying to do this for user activity, do you know what databases are needed? I basically need the user activity detail report but need more than 1000 rows returned. I am hooking it to hierarchy so we can look at the metrics across depts and teams etc - any help/advice greatly appreciated!

    Thanks

    Rich

    ReplyDelete
    Replies
    1. When you say user activity are you talking about phone calls? If so that is available in the lcsCDR database your on prem S4B server is set to save its call history to. If you are talking about presence that is another story. http://bridgeoc.blogspot.com/2016/03/building-skype-for-business-automatic.html You shouldn't run into any 1000 row limit if you are using direct queries either btw.

      Delete
    2. Thanks for the reply Doug! I basically need a 30 day extract that contains communications data, so number of IMS, audio, video, lengths of conversations - particularly audio and video, number of daily users. We are rolling it company wide and management want to monitor the uptake in order to push it. The user activity detail report which Skype offers does contain this but it only has a 1000 row limit which isn't enough for even a days activity. Again any help advice/greatly appreciated.

      Thanks

      Rich

      Delete
    3. Everything you are looking for will be in the S4B archive database location you specified in your topology. I am not sure if IM is on by default, but it can be easily enabled there. There are a set of stored procedures in the lcsCDR database that should get you started.

      Delete
    4. lovely, thanks - really appreciate it!!!

      Delete