Identifying Federation Enabled Users, Including PIC

Microsoft had an agreement with Yahoo, AOL, and itself (Skype and Live Messenger) that enabled organizations to utilize Public IM Connectivity (PIC) to send instant messages between Lync environments and the PIC environments. Microsoft announced that they were going to discontinue their agreements with Yahoo and AOL and the last day is to be June 30, 2014.

June 30, 2014 is almost here.

I am working with AOL on a direct federation agreement. The entire process is extremely painful, and pretty expensive. AOL has set a minimum contract price of $14,400 per year to continue federation. Between legal and security issues, the process of coming to an agreement is a major challenge.

Which takes me to my issue. I had to find out which users are currently connecting to AOL and let them know that there might be an outage as contract negotiations with AOL are going pretty slowly and are not likely to be resolved soon. I expect an outage.

So, how to proceed… There are a couple of ways to look at this.

1. Just identify all users that have been authorized to use PIC. That is pretty easy, using a simple Lync PowerShell script, as shown here:

Get-CsUser | Where ($_.ExternalAccessPolicy –ilike “*Public*”} | Select SipAddress

2. The second way is a good bit more complex. Identify everyone that is actually using PIC to connect to AOL users, and identify the AOL account destinations for the IM conversations. I am sure there is a better way to do this, and I have asked Pat Richard to take a look at this and then write a much more efficient script. The nice thing about this script is that it can be easily modified to identify users that are communicating to specific Federated domains.

The second method provides more accurate data. Of course, it now requires that we query data out of the LcsCDR database. There will be some duplication when it comes to identifying the users that are using Lync to connect to AOL and the accounts that they connect to at AOL, but that is easy to clean up in Excel. You can download it here.

$SQLQuery = “Select





       U.UserUri ‘User1URI’,

       UU.UserUri ‘User2URI’,


From SessionDetails S

Inner join Users U on S.User1Id = U.UserId

Inner join Users UU on S.User2Id = UU.UserId

WHERE S.IsUser1Internal = 0 OR S.IsUser2Internal = 0

Group by








$connection = new-object

$Connection.connectionString=”Data Source=SqlServerFqdn\InstanceName;Initial Catalog=LcsCDR;Integrated Security=SSPI”


$Command = $Connection.CreateCommand()

$Command.Commandtext = $SqlQuery

$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $Command

$Dataset = New-Object System.Data.Dataset


$Dataset.Tables[0] | Export-CSV FederationActivity.csv -notype


$connection = $null

$Results = Import-Csv FederationActivity.csv

$Null | Out-File FederationActivityResults.csv

$Header = “User1” + “,” + “User2” + “,” + “Time”

$Header | Out-File FederationActivityResults.csv

ForEach ($r in $Results){

               $User1 = $r.User1URI

               $User2 = $r.User2URI

               $Time = $r.SessionIdTime

               If($User1 -imatch “” -or $User2 -imatch “”){

                              #Write-Host “User1’s URI is $User1 and User2’s URI is $User2”

                              $Entry = “$User1″+”,”+”$User2″+”,”+”$Time”

                              Write-Host $Entry

                              $Entry | Out-File FederationActivityResults.csv -Append



This entry was posted in Lync. Bookmark the permalink.

1 Response to Identifying Federation Enabled Users, Including PIC

  1. Pingback: Weekly IT Newsletter – June 23-27, 2014 | Just a Lync Guy

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s