Extracting the SIP Domain based Lync Usage Report from SQL Server 2008

Login to SQL Server
Run SQL Server Management Studio
Click Connect
Click “LCSCDR” database and New Query
Copy and paste the below SQL Query to SQL Management Studio – Query Window and change the date accordingly to your requirement and Use the correct SQL Query for respective locations. The same query can be modifying for different locations with multiple sip domain names. Click on “Execute” to run the query.

*******************************************************************
select distinct (select a.UserUri from Users a where a.UserId = b.User1Id) From_user,(select a.UserUri from Users a where a.UserId = b.User2Id) To_User ,case when b.MediaTypes = 1 then 'IM'

when b.MediaTypes = 2 then 'File Transfer'

when b.MediaTypes = 4 then 'Remote Assitance'

when b.MediaTypes = 8 then 'Application Sharing'

when b.MediaTypes = 16 then 'Audio'

when b.MediaTypes = 32 then 'Video'

when b.MediaTypes = 64 then 'App Invite'

else 'None' end 'Modality'  ,b.InviteTime,b.SessionEndTime from Users a

join SessionDetails b

on a.UserId = b.User1Id

where (select a.UserUri from Users a where a.UserId = b.User1Id) like '%SipDomain%'

and b.InviteTime < '2014-11-01 00:00:01.000'

and b.InviteTime > '2014-10-01 00:00:01.000'

***********************************************************************
Results will be pop-up after running the query, Click to select all the cells

Click “Save Result As”

Save the File in CSV Format 

Change the CSV file to Excel file by clicking on “Save As” and format the excel file into below format,