Lync 2013 – Creating Custom Lync Reports within Monitoring

Hello All

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.

Step 1.

Ensure the current reports are working as expected

1

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 

http://lync2013sql/reports_LYNCARCHMON

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)

2

ok now we have navigated to our browser page lets select the report builder option.

3

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.

4

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.

5

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

6

 

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.

7

 

On the arrange fields page drag and drop the required fields into the rows and columns 

8

 

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.

9

 

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

10

now if you go back to your browser and select the original reporting path, you will see your newly created report within to select

11

 

Thats it. 

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.

 

thanks again

 

 

Advertisements

2 thoughts on “Lync 2013 – Creating Custom Lync Reports within Monitoring

  1. Pingback: Lync 2013 – Creating Custom Lync Reports within Monitoring | NorthernLync « JC's Blog-O-Gibberish

  2. We do this, too, but store the reports (and views) on a separate, IT-wide Reporting Server.

    One caveat about rolling your own: those tables in the LcsCDR database changed between Lync Server 2010 and 2013, breaking most of my painstakingly-crafted reports! Apparently, MSFT does not feel compelled to maintain backwards compatibility for those backend databases. The views appear to have been a bit more stable, but I was taught not to make views of views…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s