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 (10)

  1. Richard Schwendiman says:

    @Pat – thanks I updated the code above.

  2. Anthony Caragol says:

    @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

  3. Pat Richard says:

    if you put
    USE LcsCDR
    at the beginning, it’ll use the LcsCDR database instead of having to manually select it.

  4. Anonymous says:

    Pingback from NeWay Technologies – Weekly Newsletter #94 – May 8, 2014 | NeWay

  5. Anonymous says:

    Pingback from NeWay Technologies – Weekly Newsletter #94 – May 9, 2014 | NeWay

  6. Anonymous says:

    Pingback from Weekly Newsletter #94 – May 9, 2014 | Just a Lync Guy

  7. Anonymous says:

    Pingback from Weekly Newsletter #94 – May 9, 2014 | Just a Lync Guy

  8. Anonymous says:

    Pingback from Weekly Newsletter #94 – May 9, 2014 | Just a Lync Guy

  9. Pat Richard says:

    I tossed this into a PowerShell script in case you can’t get to Management Studio.
    http://www.ehloworld.com/2661

  10. How do I find unique number of users ?
    Thanks,
    -Hareesh.