Extending your Lync monitoring data using PowerPivot and Power View


During the last Techdays here in Belgium, I have had the privilege of being able to present a session on Lync. I had chosen as a topic for that session “Deploying Lync: Notes from the Field, and more”, where I talked about the top ten questions I run into when talking to customers about deploying Lync in their environment. I tried to answer the following questions:

  • Lync Online – Lync On Premises: where do we go?
  • Lync is a Puzzle, really?
  • Lync and NAT: Yes or No?
  • Whats’s up with the Picture in Lync?
  • Are there any tools available to help me design hardware and bandwidth requirements for Lync?

And then the final topic I touched upon was monitoring, and that’s when I was happy to introduce one of my Technology Advisor colleagues, Wesley Backelant, who knows almost everything about PowerPivot and Power View and SQL and BI to the IT Pro Lync audience Smile And we promised to blog about it, so here it is, the details on the web Smile

Monitoring Server in Lync, why would you use it?

In short, the Monitoring Server role in Lync is optional, and can be added after finishing the deployment of all the other server roles within Lync. The Monitoring Server enables you to capture both call detail records (CDR) for Enterprise Voice and Audio/Video conferences and data collections about your Audio/Video Quality of Experience, also incorporating data on  file transfers, application sharing, and remote assistance. It does NOT archive the conversations itself (if you want to keep the IM conversations in your organization, you should consider deploying the Archiving server role).

Some typical questions on the Monitoring Server role in Lync:

  • Can the Monitoring server role be combined with any other Lync Server role?

    Yes, as taken from TechNet:

    “A Monitoring Server can be collocated with an Archiving Server, with a SQL Server store of an Enterprise Edition Front End pool, or with a file store of a Front End pool. The Monitoring Server requires a database, but the database can be collocated on the Monitoring Server, with the database server for the Archiving Server, or on the Back End Server of an Enterprise Edition Front End pool. A Monitoring Server cannot be collocated with a Standard Edition server in a production environment.”

  • Can you collocate the monitoring database with a Lync back-end database?

The answer is yes, as taken from TechNet

You can collocate each of the following databases on the same database server:

  • Back-end database
  • Monitoring database
  • Archiving database

You can collocate any or any or all of these databases in a single SQL instance or use a separate SQL instances for each, with the following limitations:

Each SQL instance can contain only a single back-end database, single Monitoring database, and single Archiving database.

The database server cannot support more than one Front End pool, one Archiving Server, and one Monitoring Server, but it can support one of each, regardless of whether the databases use the same SQL instance or separate SQL instances.

  • Do you require SQL Reporting Server?

    No,  but if you do you will be able to take advantage of the built-in Monitor Server Reports, which you can customize as wanted.

  • What’s the advantage of using the Monitoring Server role with Microsoft System Center Operations Manager?

    By installing the Microsoft System Center Operations Manager, which uses the Monitoring CDR and QoE data, you can enable the generation of near real-time alerts showing the health of call reliability and media quality, and define actions upon these.

  • Do I need a monitoring server per Lync Pool, and even per Site?

    No, as taken from the same Technet Article linked to before:

    “Multiple central sites can also share any of the following that you deploy in one central site:

    • Archiving Server
    • Monitoring Server
    • Stand-alone Mediation Server or pool
    • Edge Server or Edge pool”
  • Any documentation available on how to deploy the Monitoring Server?

    Yes: Microsoft Lync Server 2010 Monitoring Deployment Guide, available for download here: http://www.microsoft.com/download/en/details.aspx?id=8207, which described the different steps envolved:

    • Installing Message Queuing for Monitoring
    • Installing SQL Server (with Reporting Services)
    • Adding a Monitoring Server to the Topology using Topology Builder
    • Installing the Monitoring Server
    • Deploying Monitoring Server Reports (if wanted)
    • Configuring Monitoring Server Settings


  • Is there a document that described how to use the Monitoring Server Reports in a Lync Server 2010 deployment?

    Sure there is, there is Microsoft Lync Server 2010: Using Monitoring Server Reports whitepaper available for download here: http://www.microsoft.com/download/en/details.aspx?id=890

Built-In Reports examples:






What does the Dashboard give me?



And yes you can export the reports, and you can customize them…but if you know have the feeling as in “this is not exactly what I want to show my manager”, “I would like to have some more fancy reports” “I’m looking for an easier way to use the gathered data”…then please read along, because now it’s time to dive into PowerPivot and Power View!

Let me start by saying that the solution provided here is obviously for demo purposes.  It is not a perfect solution and we are not responsible for the production use of this file.

Technologies Used

First a couple of words on the technology used in this solution.

PowerPivot is a powerful data mashup and data exploration tool based on xVelocity in-memory technologies providing unmatched analytical performance to process billions of rows at the speed of thought.  It comes with a client part, a free add-in for Excel 2010, which allows you to import and combine data in an easy way.  We used Excel in this particular case to get information from the Lync monitoring databases and create some interesting calculations.  In order to get your hands on PowerPivot please visit the official website

The second part of the solution uses SharePoint and Power View to share and visualize the information in a very interactive way.  Sharing PowerPivot files in a scalable and controlled way can be achieved by using the Excel Services functionality of SharePoint.  With Excel Services you have the ability to share Excel files to a broad audience without the need to install Excel 2010 (and in this case PowerPivot) on every machine.  If you need more information about Excel Services please read this.  When integrating SQL Server Reporting Services 2012 in SharePoint 2010, you will get a new powerful data visualization tool called Power View.  Power View is really all about the interactive and stunning experience.  Understanding Power View is actually the easiest by just watching this demo.

Now that you know the technology part let’s dive into some of the details on how we created this.  Of course we started by interviewing customers to understand what they would like to get out of the information available.  Some of the topics that came up were duration distribution, average response time, number of sessions by type, uptake of Lync and a lot more interesting stuff!

Most of the information you need is available in the LcsCDR which is fully documented here.  The leading table is SessionDetails so you better figure out how this table is structured.  One additional set of data you may need is a date table, I have included a hidden sheet with dates but you could also use the DateStream feed from our Windows Azure Marketplace.  A couple of other tables we used are SIP, Users, ClientVersions and UriTypes


The other database we used was rtccab1 to get display names.  This is a bit less straightforward to retrieve but here is how you can extract it:

SELECT avname.Value as 'DisplayName', avuri.Value as 'UserUri'
  FROM AbUserEntry u
  INNER JOIN AbAttributeValue avname ON avname.UserId = u.UserId AND avname.AttrId = 3
  INNER JOIN AbAttributeValue avuri ON avuri.UserId = u.UserId AND avuri.AttrId = 8


The entire model looks like this


Now that we have all the information in place it is time to visualize some of it.  A couple examples of the things we built (remember it was built on a test database too so don’t freak out by the unanswered call rate clip_image008).




Taking this PowerPivot file to the server to share it with a broad audience is a matter of saving it on an Excel Services enabled SharePoint Server.  This would give you the following view


But we promised a more compelling and interactive way of visualizing this information and that is exactly what you will do with Power View.  Power View can be used on PowerPivot files published on SharePoint or on solutions deployed to SQL Server Analysis Services.  As a little side note, you can import the PowerPivot model or build this model from scratch in SQL Server Data Tools to make it a real server-side solution running on SQL Server Analysis Services.


The most popular view however is the adoption rate of Lync in the organization, this can be achieved by using the Scatter Chart functionality in Power View.


Please find the PowerPivot file here.  In order to get data from your enviroment go to the PowerPivot window, select “Design” and “Existing Connection”.  Edit both connections to point to your SQL Server and when done use “Refresh All”.  In the Excel Windows go to Data" and select “Refresh All” and you are done.  If you want to use Power View just upload the file to a SharePoint where this feature is enabled.

There is a LOT more you can get out of the Lync databases, so we hope this sparks a whole wave of creativity!  Ilse’s favorite for example is the QoEMetric database to see the call quality for different users and devices.  If I can find some time I’ll see if we can publish this PowerPivot file too. 

UPDATE dd April23rd, 2012

Just a small heads up that I changed the Lync PowerPivot file to make it scale better.  In order to do so I have removed several unique columns from the import and moved some calculations to the query side instead of the client side.

I have placed the new file here and the original file is still available here.

If you are interested in having this as a Tabular Model (for SQL Server Analysis Services) please let me know.  You can easily do this yourself if you like of course by importing the model in the SQL Server Data Tools.



Ilse & Wesley

Comments (13)
  1. Anonymous says:

    Hi Jed,

    Thank you for your question.  

    The PowerPivot version we used is the 2012 version, it is available on http://www.powerpivot.com



  2. Thank you Elan for your comment, changes made in the blog post to reflect your feedback.


  3. Elan Shudnow says:

    You mentioned that the Monitoring and Lync FE can coexist on the same SQL Server but must reside in different instances.  This used to be true but the guidance was changed about a year ago.


    You can collocate each of the following databases on the same database server:

       Back-end database

       Monitoring database

       Archiving database

    You can collocate any or any or all of these databases in a single SQL instance or use a separate SQL instances for each, with the following limitations:

       Each SQL instance can contain only a single back-end database, single Monitoring database, and single Archiving database.

       The database server cannot support more than one Front End pool, one Archiving Server, and one Monitoring Server, but it can support one of each, regardless of whether the databases use the same SQL instance or separate SQL instances.

  4. Jed Ellerby says:

    Looks like a really usefuly article, if I can get PP up and running.

    What version of PowerPivot are you using as the latest version I could find (10.51.2500) states the data associated with the XLSX download is from a newer version so it won't analyse the data. This seems to stop me getting to the important design info of the xlsx. Any clues?

  5. Teo De Las Heras says:

    I love this!!

  6. Ivo Frenken says:

    I can't get connected. What DB do I need to use. My standard edition DB, or my monitoring DB's.

  7. Corey says:

    Please publish the QoE PowerPivot file too! This is great!

  8. Darryl Munro says:

    I have a small issue I am hoping you can help me with. On the Call Analysis worksheet, all I get it Blank for the Row Labels, so if I change the year to something other than blank I get no data in the view. I must have something wrong with my datetime configuration. Is there something I can change that you can suggest? Thanks in advance.

  9. Anonymous says:

    Pingback from Extending your Lync monitoring data using PowerPivot and Power View – Ilse Van Criekinge’s Weblog – Site Home – TechNet Blogs | JC’s Blog-O-Gibberish

  10. Chad says:

    How do you connect to the data source?

  11. alex rico says:

    do you have for lync 2013 ???


  12. Gabriel says:

    Could you update to new powerpivot version, i use Office 2013 and it cannot open the model. Regards

  13. show box says:

    Thanks for the great info. I really loved this. I would like to apprentice at the same time as you amend your web site, how could i subscribe for a blog site?
    For more info on showbox please refer below sites:
    Latest version of Showbox App download for all android smart phones and tablets.
    http://movieboxappdownloads.com/ – It’s just 2 MB file you can easily get it on your android device without much trouble. Showbox app was well designed application for android to watch movies and TV shows, Cartoons and many more such things on your smartphone.
    For showbox on iOS (iPhone/iPad), please read below articles:
    Showbox for PC articles:
    There are countless for PC clients as it is essentially easy to understand, simple to introduce, gives continuous administration, effectively reasonable. it is accessible at completely free of expense i.e., there will be no establishment charges and after establishment
    it doesn’t charge cash for watching films and recordings.
    http://www.showboxforipad.org/showbox-apk/ Not simply watching, it likewise offers alternative to download recordings and motion pictures. The accompanying are the strides that are to be taken after to introduce Showbox application on Android. The above
    all else thing to be done is, go to the Security Settings on your Android telephone, Scroll down and tap on ‘Obscure sources’.
    Movie Box, an esteemed movies application in which you can find stacks of programs and films. The guide is given here to download Movie Box app to Android and to Apple iOS 9.0.2, iOS 8.4/8.3 and also for the lower versions without Jailbreak.
    Please do login to Showbox application with the help of Ymail. You can login in Ymail from here –
    Sign Up & Do registration for latest movies on Showbox application

Comments are closed.

Skip to main content