Description
I had a need to see real-time call data. Lync MVP/MCM Tom Pacyk has a really cool script for logging concurrent calls to .csv over time by querying perfmon counters at regular intervals. That works great when you’re trying to determine concurrent for scale planning. But I wanted to see who was on the phone – not just the number of current calls.
This script will look at the LCSCDR database, which is installed when you install a Monitoring Server in your topology. The script gathers information about current calls in progress, and displays them in list style. It’s fairly basic.
Call between Lync users
As you can see, the list shows the two users in the call, their ID number, who initiated the call, when it was made, etc. In this screen shot, User 1 was connected from outside the environment (IsUser1Internal is false), while User2 was in a company office with WAN connectivity to the Lync infrastructure (IsUser2Internal is True).
If a user has initiated or received a call to/from a PSTN number, then User2Uri will read “PSTN call”.
Call to PSTN number
As mentioned above, a Monitoring Server does need to be installed in your topology so that the LCSCDR database is created and updated. But the script can be run from anywhere.
You could also wrap this into a function and toss it in your PowerShell profile to make easily accessible.
This script was more of a proof of concept into querying SQL from PowerShell, and the required query. If you have suggestions, let me know!
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.
Download the script from below to a server with PowerShell. Run the script in PowerShell using
Get-CsVoiceCalls.ps1 -server [SQL server]
If the lcscdr database is in a named instance, specify the instance
Get-CsVoiceCalls.ps1 -server [SQL server] -instance [instance]
Where [SQL server] is the FQDN of the database server containing your LCSCDR database.
You can also hard code the SQL server FQDN and the instance in the script. Look for
[string]$server,
and change it to include the SQL server FQDN, such as
[string]$server = "sqlserver.domain.local",
and look for
[string]$instance,
and change it to include the instance name, such as
[string]$instance = "Lync",
And then you can just call the script by name using
Get-CsVoiceCalls.ps1
There is also full comment based help, using
Get-Help Get-CsVoiceCalls.ps1
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.
Known Issues
- The logic that looks for the database in named instances still needs more testing. If you run it in an environment where you have the lcscdr database in a named instance, please let me know your results.
- I tried to adapt to correcting the time display. The data is apparently stored in SQL in GMT. So I’ve coded the script to display it according to the local time zone (according to Windows). Let me know if your results aren’t as expected.
- If a user receives a call from a PSTN number, the SessionStartedById field is blank. Working around that has been problematic, but I’m still trying.
Download
Get-CsVoiceCalls.v1.4.zip
Get-CsVoiceCalls.v1.2.zip
Changelog
See the changelog for this script to see what has changed between versions.
Follow Me