I had a need to see realtime 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!
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
and change it to include the SQL server FQDN, such as
[string]$server = "sqlserver.domain.local",
and look for
and change it to include the instance name, such as
[string]$instance = "Lync",
And then you can just call the script by name using
There is also full comment based help, using
- 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 aparrently 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.
See the changelog for this script to see what has changed between versions.