SQL Query - find FederatedPIC conversations

  • Article

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'