Lync Call Report using SQL Query

In this blog we will see how to pull out the Lync (Enterprise Voice) outgoing call Report using SQL Query

To get the Outgoing call report

Use LcsCDR
SELECT  VoipDetails.SessionIdTime [Date],
        CONVERT(varchar(10),SessionDetails.SessionEndTime - VoipDetails.SessionIdTime,108) [Time],
        Phones.PhoneUri [From Number],
        Users1.UserUri [From Sip],
        Phones1.PhoneUri [Number Dialed]


FROM         VoipDetails LEFT OUTER JOIN SessionDetails
             ON VoipDetails.SessionIdTime = SessionDetails.SessionIdTime AND VoipDetails.SessionIdSeq = SessionDetails.SessionIdSeq
             LEFT OUTER JOIN  Phones
             ON VoipDetails.FromNumberId = Phones.PhoneId
             LEFT OUTER JOIN  Phones Phones1
             ON VoipDetails.ConnectedNumberId = Phones1.PhoneId
             LEFT OUTER JOIN  Users Users1
             ON SessionDetails.User1Id = Users1.UserId

where SessionDetails.ResponseCode = 200
      and VoipDetails.SessionIdTime between '2013-10-01 00:00:00' and '2013-10-01 23:59:59'     
order by VoipDetails.SessionIdTime

Here is the Output of above query

Date	Time	From Number	From SIP                                Number Dialed
10/1/2014 6:50	0:04:46	1234567890	user01@contoso.com             1234567893
10/1/2014 6:52	0:01:54	1234567891	user02@contoso.com             1234567895
10/1/2014 6:54	0:02:32	1234567892	user03@contoso.com             1234567896
10/1/2014 6:57	0:00:37	1234567893	user04@contoso.com             1234567897

To get the Outgoing call report for the Particular Users


Use LcsCDR
SELECT VoipDetails.SessionIdTime [Date],
       CONVERT(varchar(10),SessionDetails.SessionEndTime - VoipDetails.SessionIdTime,108) [Time],
       Phones.PhoneUri [From Number],
       Users1.UserUri [From Sip],
       Phones1.PhoneUri [Number Dialed]

FROM         VoipDetails LEFT OUTER JOIN SessionDetails
                        ON VoipDetails.SessionIdTime = SessionDetails.SessionIdTime AND VoipDetails.SessionIdSeq = SessionDetails.SessionIdSeq
                        LEFT OUTER JOIN  Phones
                        ON VoipDetails.FromNumberId = Phones.PhoneId
                        LEFT OUTER JOIN  Phones Phones1
                        ON VoipDetails.ConnectedNumberId = Phones1.PhoneId
                        LEFT OUTER JOIN  Users Users1
                        ON SessionDetails.User1Id = Users1.UserId

where SessionDetails.ResponseCode = 200
         and VoipDetails.SessionIdTime between '2014-10-01 00:00:00' and '2013-10-01 23:59:59'        
         and Phones1.PhoneUri is not null
     and Phones.PhoneUri  is not null
     AND Users1.UserUri IN ('User01@contoso.com','User02@contoso.com')
order by VoipDetails.SessionIdTime


Any Comments are Welcome :)