Share via

SQL Query - find FederatedPIC conversations

One of my customers recently needed to find out how many internal users were talking to aol.com users via Lync. I wrote the script below which can be edited for any Federated\PIC domain.

 

This will display both users in the conversation URIs and the Session time. If you just wanted a total count you can change the first line to say Select Count (*).

 

Use LcsCDR

Select u1.UserUri User1URI, u2.UserUri User2URI, SessionIdTime

From SessionDetails S

inner join Users u1 on u1.UserId = S.User1Id

inner join Users u2 on u2.UserId = S.User2Id

Where (u1.UserUri like '%aol.com' or u2.UserUri like '%aol.com') and SessionIdTime >= '2014-01-01'

Comments

  • Anonymous
    January 01, 2003
    @Pat - thanks I updated the code above.
  • Anonymous
    January 01, 2003

    @Shenoyhareesh try this: this will only return the number of unique users that are not AOL users, I figured you didn't want to count the AOL users. If you want the names, replace count(*) with just .

    Use LcsCDR

    Select count(    ) from (

    Select u1.UserUri UserURI
    From SessionDetails S
    inner join Users u1 on u1.UserId = S.User1Id
    inner join Users u2 on u2.UserId = S.User2Id
    Where (u2.UserUri like '%aol.com') and SessionIdTime >= '2014-06-01'

    UNION

    Select u2.UserUri UserURI
    From SessionDetails S
    inner join Users u1 on u1.UserId = S.User1Id
    inner join Users u2 on u2.UserId = S.User2Id
    Where (u1.UserUri like '%aol.com') and SessionIdTime >= '2014-06-01'

    ) as CombinedURIs
  • Anonymous
    May 09, 2014
    if you put
    USE LcsCDR
    at the beginning, it'll use the LcsCDR database instead of having to manually select it.
  • Anonymous
    May 11, 2014
    Pingback from NeWay Technologies – Weekly Newsletter #94 – May 8, 2014 | NeWay
  • Anonymous
    May 11, 2014
    Pingback from NeWay Technologies – Weekly Newsletter #94 – May 9, 2014 | NeWay
  • Anonymous
    May 11, 2014
    Pingback from Weekly Newsletter #94 – May 9, 2014 | Just a Lync Guy
  • Anonymous
    May 11, 2014
    Pingback from Weekly Newsletter #94 – May 9, 2014 | Just a Lync Guy
  • Anonymous
    May 11, 2014
    Pingback from Weekly Newsletter #94 – May 9, 2014 | Just a Lync Guy
  • Anonymous
    June 07, 2014
    I tossed this into a PowerShell script in case you can't get to Management Studio.http://www.ehloworld.com/2661
  • Anonymous
    June 27, 2014
    How do I find unique number of users ?
    Thanks,
    -Hareesh.