I haven’t seen much around guides on how to create custom reports for Lync 2013 so i thought it would be good to share the mechanisms on how to do this with the native Monitoring reporting tool which can be co-located with the Lync 2013 frontend.
For this guide I’m running a Lync 2013 Frontend patched upto CU version January 2014, and SQL Server 2012r2.. All servers are running windows 2012r2 and patched unto date.
Ensure the current reports are working as expected
Next step is to go to the SQL Server you provisioned and open up internet explorer and navigate to the reports path/URL for this demo that would be
Also if you are unsure you can find the path required by going into the SQL Reporting Services configuration Manager and selecting reporting Manager (as below)
ok now we have navigated to our browser page lets select the report builder option.
NOTE: Depending on your original setup up, you might have to download and install the actual report builder application. (you will be prompted for this download if you haven’t already got it installed.)
Once installed or when the new dialog appears – Now select ‘new report’ as this is what we are wanting to do.
Once you’ve selected the table wizard, accept the default dataset ‘create a dataset’ and click next.
Now you will be promoted for a data source, for Lync we need to browse to the data source down the tree.
select it and click ok. then do a quick test connection for clarity. now select Next, and when prompted add the credentials of your ‘privileged’ user account
Once you’ve added this information, you will be presented with a dialog which is called design a query. In this you can create your own report based on the information held within the Monitoring database. (everything), NOTE: it would be worth getting hold of the Lync 2013 database schema to understand all the tables available to interrogate.
for this demo, I’m going to select a stored procedure calls GetSCOMAlertData. this store procedure in the report and information which is passed to the SCOM application for reporting.
in this scenario you don’t need SCOM, but you can get reports on what information would have been passed. I find this useful for monitoring your Lync environment.
Also you could if you already had a reporting tool ie: Crystal Reports data mine your own information onto a crystal report and interact with other internal system databases.
On the arrange fields page drag and drop the required fields into the rows and columns
on the next couple of pages select your layout and style and select next/finish
once this is complete you can select the ‘Run Report’ button to see your finished result. If your not happy you can go back and redesign the report as you wish.
Once you’ve tweaked your report to suit, no select save and navigate to the lyncserverreports folder and give your report a name and select save
now if you go back to your browser and select the original reporting path, you will see your newly created report within to select
As i say there is a whole raft of information available to report on, so before you start i would suggest you have the report requirements at had and also have a good understanding of where within the CDR database that information is stored.