Monday, March 9, 2015

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 :)

6 comments:

  1. thanks this is great. Do you know how I can see inbound calls as well?

    ReplyDelete
  2. think I found it. VoipDetails.FromGatewayId and
    VoipDetails.ToGatewayId should help indicate the direction of the call I believe.

    ReplyDelete
  3. Thanks, is there a way to get only international call detail only.

    ReplyDelete
  4. hi,

    anyone can post plz finAL QUERY To list all the incoming call to specific user?

    thanks a lot.

    ReplyDelete
  5. How would I narrow this down to international calls only?

    ReplyDelete
    Replies
    1. Doug, if you want to narrow down to international calls only. add the line below as part of "Where" statement. This will get you all the calls your users made to international numbers ( Numbers outside US ( +1 XXX-XXX-XXXX)). I hope this answers your question.

      Line to add:

      and Phones1.PhoneUri not like '+1%'

      Delete