Home > Lync Server/Skype for Business Server > Script: Get-CsFederatedConversationDetails.ps1 – See Stats About Conversations With Specific Federated Domains

Script: Get-CsFederatedConversationDetails.ps1 – See Stats About Conversations With Specific Federated Domains

Lync 2013 logo 128x128Description

Richard Schwendiman, PFE at Microsoft, came up with a great SQL query that you could plug into SQL Server Management Studio to see time & date info for conversations with federated or PIC domains. In Richard’s case, he used the aol.com PIC domain. Since PIC federation with AOL and Yahoo is ending next month, I thought this was great timing on Richard’s part. But sometimes, Lync admins can’t login to SQL servers to run queries due to security policy. Plus, the query is something you’d have to keep handy and edit accordingly each time you wanted to get data. So I figured – hey, why not whip up a quick script to allow an admin to query SQL for this data, allowing for any domain and time frame to be specified? Poof – out comes my script.

This script will query a specific SQL server for information about a specific federated SIP domain. The domain does NOT need to be in the allowed domains list if you’re open federating. Any SIP domain name works. You can specify a start date/time in the yyyy-MM-dd format, such as 2014-05-13 using the -TimeSpan parameter. Or, you can use some handy ranges I’ve included, including LastWeek (the last 7 days), Last30Days, Last year (last 365 days), FirstOfYear or ThisYear (since Jan. 1), FirstOfMonth or ThisMonth (since the 1st of the month), FirstOfWeek or ThisWeek (since Sunday). Optionally, you can specify an end date/time in the yyyy-MM-dd format. This script will default to FirstOfYear with no end date, and aol.com for the domain. As we see below, only the SQL server holding the LcsCDR database is queried.


Now, from this output, we see that there is not a lot of communications with people on AOL since the first of the year. The upcoming change should have very little impact.

If you’re using a named instance in SQL, you can specify it as well.

The script outputs a full object, just like other cmdlets, so you can pipe it to other commands to alter the display, including sorting, or my favorite, Out-GridView, as well as outputting to files such as .csv.

Hopefully, this tool will make life a little easier in digging out data.


Get-CsFederatedConversationDetails.ps1 [[-SqlServer] ] [[-SqlInstance] ] [[-SipDomain] ] [[-TimeSpan] <object>] [[-EndDate] <object>] [-WhatIf ] [-Confirm ] [<commonparameters>]</commonparameters></object>


Execution Policy: Third-party PowerShell scripts may require that the PowerShell Execution Policy be set to either AllSigned, RemoteSigned, or Unrestricted. The default is Restricted, which prevents scripts – even code signed scripts – from running. For more information about setting your Execution Policy, see Using the Set-ExecutionPolicy Cmdlet.


I’ve never been one to really solicit donations for my work. My offerings are created because *I* need to solve a problem, and once I do, it makes sense to offer the results of my work to the public. I mean, let’s face it: I can’t be the only one with that particular issue, right? Quite often, to my surprise, I’m asked why I don’t have a “donate” button so people can donate a few bucks. I’ve never really put much thought into it. But those inquiries are coming more often now, so I’m yielding to them. If you’d like to donate, you can send a few bucks via PayPal at https://www.paypal.me/PatRichard. Money collected from that will go to the costs of my website (hosting and domain names), as well as to my home lab.




v1.0 – 05-13-2014 – Get-CsFederatedConversationDetails.v1.0.zip


See the changelog for information on what’s changed/included in each version.


  1. May 13th, 2014 at 15:56 | #1

    Awesome script. Thanks!

  2. May 13th, 2014 at 21:44 | #2

    Very Cool! That’s a great script and solves 2 issues I have been working on.
    1- Getting usages to/from federated domains
    2- SQL query from powershell.

  3. Henry Creagh
    June 11th, 2014 at 18:03 | #3

    Hello Richard, I found a small bug in the script when using it with a SQL instance. The issue is on line 178. Here is the fix
    Please replace existing line:
    [string] $SQLServer += “\SQLInstance”

    With this updated line:
    [string] $SQLServer += “\”+$SQLInstance

  4. August 19th, 2014 at 05:22 | #4

    Thanks Pat. It took me a while to realise that I should be specifying my Monitoring role SQL server which makes complete sense now but it wasn’t immediately apparent to me whilst I was trying to figure out why it wasn’t working – d’oh.

  5. October 8th, 2015 at 17:33 | #5

    If you want to dump ALL federated convo’s, just feed your list of Allowed domains in:

    Get-CsAllowedDomain | ForEach-Object {.\Get-CsFederatedConversationDetails.ps1 -SqlServer sql2008r2.contoso.local -SqlInstance Lync2013 -SIPDomain $_.Domain >> c:\log.txt}

    With only a modest amount of massaging in Excel you can pull out your top users and/or see who your most/least frequent federated partners are.

  1. May 17th, 2014 at 16:08 | #1
  2. May 17th, 2014 at 16:14 | #2
  3. May 17th, 2014 at 16:20 | #3
  4. June 20th, 2014 at 13:50 | #4
  5. June 23rd, 2014 at 06:26 | #5