Description

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.

Syntax

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

Installation

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.

Donations

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.

Assumptions

None

Download

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

Changelog

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