none
Find active Lync calls from SQL database

    Question

  • Does anyone know a SQL query to pick out the currently open calls in Lync? I've tried selecting records from the SessionDetails table where SessionEndTime is NULL but a lot of records seem to match this, not just the active calls.


     
    Thursday, April 12, 2012 3:35 PM

Answers

  • Hi

    After some more testing I've now settled on this to find current audio calls:

    select
    s.[SessionIdTime]
           ,[User1Id]
          ,u1.UserUri as user1uri
          ,u2.UserUri as user2uri
          ,[User2Id]
          ,[User1EndpointId]
          ,[User2EndpointId]
          ,[TargetUserId]
          ,[SessionStartedById]
          ,[OnBehalfOfId]
          ,[ReferredById]
          ,[IsUser1Internal]
          ,[IsUser2Internal]
          ,[ResponseTime]
          ,[DiagnosticId]
      FROM [LcsCDR].[dbo].[SessionDetails] s
      left outer join [LcsCDR].[dbo].[Users] u1
      on s.User1Id = u1.UserId
      left outer join [LcsCDR].[dbo].[Users] u2
      on s.User2Id = u2.UserId
     where ResponseCode=200
     and s.SessionEndTime is null 
     and (User1Id is null or User2Id is null or User1Id != User2Id)
     and MediaTypes = 16     /* Audio */
     and s.[SessionIdTime] >= dateadd(dd,0, datediff(dd,0, getDate()))

    I encountered records where User1Id and User2Id were identical, with SessionEndTIme null. I never found where these came from but in the end I just filtered them out. Maybe the Lync client sometimes does a loop-back call as a test? I then check user1uri and user2uri to distinguish between internal and external calls.

    Dave

    Wednesday, April 18, 2012 8:08 AM

All replies

  • Hi,

    Each record in SessionDetails Table represents one peer-to-peer session, which could be a VoIP-VoIP phone call, two-party IM session, or other type of session. So it is not only for calls.

    You may have to use Media Table and MediaList Table.

    The blow article may help you:

     http://blogs.technet.com/b/perez/archive/2009/07/18/application-sharing-monitoring-in-ocs-r2.aspx

    Regards,

    Lisa

    Friday, April 13, 2012 3:30 AM
    Moderator
  • Thanks, I can use those tables to identify the voice calls, but I still can't see how to identify calls which are currently active. Maybe that information simply isn't in the database.

    Dave

    Friday, April 13, 2012 12:05 PM
  • Hi,

    Would you post script here? I think you can do that, for there is a property EndTime in Media Table. Have you tried that?

    Regard,

    Lisa

    Monday, April 16, 2012 2:40 AM
    Moderator
  • Hello there, I would be interested in how to query currently active calls (Lync/Lync, Lync/PSTN, Audio Conference)....thanks
    Monday, April 16, 2012 4:28 AM
  • Hi

    After some more testing I've now settled on this to find current audio calls:

    select
    s.[SessionIdTime]
           ,[User1Id]
          ,u1.UserUri as user1uri
          ,u2.UserUri as user2uri
          ,[User2Id]
          ,[User1EndpointId]
          ,[User2EndpointId]
          ,[TargetUserId]
          ,[SessionStartedById]
          ,[OnBehalfOfId]
          ,[ReferredById]
          ,[IsUser1Internal]
          ,[IsUser2Internal]
          ,[ResponseTime]
          ,[DiagnosticId]
      FROM [LcsCDR].[dbo].[SessionDetails] s
      left outer join [LcsCDR].[dbo].[Users] u1
      on s.User1Id = u1.UserId
      left outer join [LcsCDR].[dbo].[Users] u2
      on s.User2Id = u2.UserId
     where ResponseCode=200
     and s.SessionEndTime is null 
     and (User1Id is null or User2Id is null or User1Id != User2Id)
     and MediaTypes = 16     /* Audio */
     and s.[SessionIdTime] >= dateadd(dd,0, datediff(dd,0, getDate()))

    I encountered records where User1Id and User2Id were identical, with SessionEndTIme null. I never found where these came from but in the end I just filtered them out. Maybe the Lync client sometimes does a loop-back call as a test? I then check user1uri and user2uri to distinguish between internal and external calls.

    Dave

    Wednesday, April 18, 2012 8:08 AM
  • Great! thanks for sharing that..
    Thursday, April 19, 2012 5:23 AM
  • one note.

    I'm a bit concern about this line:

     and s.[SessionIdTime] >= dateadd(dd,0, datediff(dd,0, getDate()))

    I found Instant Messages, Audio calls where they don't have a SessionEndTime....the above line with the date is only getting the recent stuff. 

    In my database, I don't have a lot of records that are missing SessionEndTime (for example, I have 489 Instant Message rows that are missing SessionEndTime for the last 2 months which is probably less than 2% of the total number of instant messages). 

    Friday, July 06, 2012 5:36 PM
  • Hi,

    One question, shouldn't you change the:

    and MediaTypes = 16     /* Audio */

    with

    and (MediaTypes & 16 ) = 16

    because this way you would not only get the Audio calls but also the sessions that had Audio, IMs, Video, etc.

    Artur

    Sunday, December 23, 2012 2:42 AM