Very cool script Pat, I tried running it against our regional monitoring databases, and got this error when running against the region where the LCSCDR is in the default instance:

Exception calling “ExecuteReader” with “0” argument(s): “The server principal “DOMAIN\MyLyncAdminAccount” is not able to access the database “LcsCDR” under the current security context.”
At C:\Powershell\Get-CsVoiceCalls.ps1:86 char:32
+ $data = $sqlCmd.ExecuteReader <<<< ()
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException

When I tried running against the other regions where there is a SQL cluster/instance, I got this:

Get-WmiObject : Invalid namespace
At C:\Powershell\Get-CsVoiceCalls.ps1:67 char:20
+ if ((Get-WMIObject <<<< -Class "Win32_PerfFormattedData_MsSqlServer_SqlServerDatabases" -Computer $server | ? {$
_.name -match "lcscdr"}) -ne $null){
+ CategoryInfo : InvalidOperation: (:) [Get-WmiObject], ManagementException
+ FullyQualifiedErrorId : GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

Lastly, I modified the script into a v1.3 version that changes the prompt to allow you to input the site name instead of the database instance, since it's usually easier for me to remember that then wherever the SQL DBs are. I added the section to validate the central site name and determine the SQL FQDN of the monitoring database from that:

[CmdletBinding(SupportsShouldProcess = $True)]
Param (
[Parameter(ValueFromPipeline = $False, ValueFromPipelineByPropertyName = $True, Mandatory = $False)]
[ValidateNotNullOrEmpty()]
[string]$site,
[Parameter(ValueFromPipeline = $False, ValueFromPipelineByPropertyName = $True, Mandatory = $False)]
[ValidateNotNullOrEmpty()]
[string]$database = “master”,
[Parameter(ValueFromPipeline = $False, ValueFromPipelineByPropertyName = $True, Mandatory = $False)]
[ValidateNotNullOrEmpty()]
[string]$query2 = “select s.[SessionIdTime],u1.UserUri as User1Uri,[User1Id],u2.UserUri as User2Uri,[User2Id],[User1EndpointId],[User2EndpointId],[TargetUserId],[SessionStartedById],[OnBehalfOfId],[ReferredById],[IsUser1Internal],[IsUser2Internal],[ResponseTime],[DiagnosticId] FROM [LcsCDR].[dbo].[SessionDetails] s left outer join [LcsCDR].[dbo].[Users] u1 on s.User1Id = u1.UserId left outer join [LcsCDR].[dbo].[Users] u2 on s.User2Id = u2.UserId where ResponseCode=200 and s.SessionEndTime is null and (User1Id is null or User2Id is null or User1Id != User2Id) and MediaTypes = 16 and s.[SessionIdTime] >= dateadd(dd,0, datediff(dd,0, getDate()))”,
[Parameter(ValueFromPipeline = $False, ValueFromPipelineByPropertyName = $True, Mandatory = $False)]
[ValidateNotNullOrEmpty()]
[string]$query = “select s.[SessionIdTime],u1.UserUri as User1Uri,[User1Id],u2.UserUri as User2Uri,[User2Id],[TargetUserId],[SessionStartedById],[OnBehalfOfId],[ReferredById],[IsUser1Internal],[IsUser2Internal],[ResponseTime],[DiagnosticId] FROM [LcsCDR].[dbo].[SessionDetails] s left outer join [LcsCDR].[dbo].[Users] u1 on s.User1Id = u1.UserId left outer join [LcsCDR].[dbo].[Users] u2 on s.User2Id = u2.UserId where ResponseCode=200 and s.SessionEndTime is null and (User1Id is null or User2Id is null or User1Id != User2Id) and MediaTypes = 16 and s.[SessionIdTime] >= dateadd(dd,0, datediff(dd,0, getDate()))”,
[Parameter(ValueFromPipeline = $False, ValueFromPipelineByPropertyName = $True, Mandatory = $False)]
[ValidateNotNullOrEmpty()]
[string]$ConnectionName = “Lync SQL Query”,
[Parameter(ValueFromPipeline = $False, ValueFromPipelineByPropertyName = $True, Mandatory = $False)]
[ValidateNotNullOrEmpty()]
[int]$CommandTimeout = 15,
[Parameter(ValueFromPipeline = $False, ValueFromPipelineByPropertyName = $False, Mandatory = $False)]
[ValidateNotNullOrEmpty()]
[ValidateRange(-12,14)]
[int]$GMTOffsetHours = ([System.TimeZoneInfo]::Local).BaseUtcOffset.Hours,
[Parameter(ValueFromPipeline = $False, ValueFromPipelineByPropertyName = $False, Mandatory = $False)]
[ValidateNotNullOrEmpty()]
[ValidateRange(0,60)]
[int]$GMTOffsetMinutes = ([System.TimeZoneInfo]::Local).BaseUtcOffset.Minutes
)

if ((Get-Date).IsDaylightSavingTime()){
$GMTOffsetHours = $GMTOffsetHours+1
}

Write-Verbose “Validating that Central Site exists”
if (Get-CsService -MonitoringDatabase | Where-Object {$_.SiteId -like “*$site”}) {
Write-Verbose “Central Site $site exists”
$MonServerDB = Get-CsService -MonitoringDatabase | Where-Object {$_.SiteId -like “*$site”}
$serverFQDN = $MonServerDB.Identity.Replace(“MonitoringDatabase:”,””)
if ($MonServerDB.SqlInstanceName -ne $null) {
$server = $serverFQDN + “\” + $MonServerDB.SqlInstanceName
} else {
$server = $serverFQDN
}
Write-Verbose “SQL Instance is calculated as $server”
} else {
Write-Host “Central Site $site was not found”
Exit
}

You’re welcome to edit further, I’m just an intermediate Powershell hack… 🙂

Here is the error I receive when using a SQL Named Instance. Looks like the script is indeed only looking for a Defaul Instance. But maybe there is another method to allow for the definition of the named instance. Perhaps an additional parameter that if provided, uses a different connection logic.

PS Microsoft.PowerShell.Core\FileSystem::\\domain.local\lync\LyncShare\Install\Scripts> .\Get-C

sVoiceCalls.ps1 -server RTLCDB1\LyncFE

Security Warning
Run only scripts that you trust. While scripts from the Internet can be useful, this script can potentially harm your computer. Do you want to run
\\domain.local\lync\LyncShare\Install\Scripts\Get-CsVoiceCalls.ps1?
[D] Do not run [R] Run once [S] Suspend [?] Help (default is “D”): r
Get-WmiObject : Invalid namespace
At \\realtime.com\lync\LyncShare\Install\Scripts\Get-CsVoiceCalls.ps1:67 char:20
+ if ((Get-WMIObject <<<< -Class "Win32_PerfFormattedData_MsSqlServer_SqlServerDatabases" -Computer $server | ? {$_.name -match "lcscdr"}) -ne $
null){
+ CategoryInfo : InvalidOperation: (:) [Get-WmiObject], ManagementException
+ FullyQualifiedErrorId : GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

Database does not exist

Hi Pat,

Neat script. I’ve tried running it on my environment, but the LCSCDR is not the default SQL instance. The script just says “database does not exist”. Any way around this? I’ve tried defining the instance when running the script but it doesn’t like that. Thanks,

Pat, That last comment helps a bit with it, I think the trick now is that the WMI class you’re looking for could change depending on the instance name. We have lcscdr databases in different instance names depending on region, and in one region the class to get is this (Win32_PerfFormattedData_MSSQLPROD4_MSSQLPROD4Databases), where in another is this (Win32_PerfFormattedData_MSSQLPROD2_MSSQLPROD2Databases). I’ll see if I can combine some of your work and some the work I did to query the instance name to see if we can create the appropriate WMI class to query.

Great script 😀

We have noticed that the scrip don’t display Group Conversations and conferences
Maybe we get this in version 1.5 ? 😉

Thanks in avance

Yep noticed that when the caller is admitted to a conference the calls disappear. We are actually interested in conference attendee numbers, not necessarily calls to PSTN. Any pointers?

This script displays current/active calls.

Is there a script that will generate a log of all calls specified with a time period?

The reporting tools in Lync only shows a maximum of 1000 records when the time period specified has more than 1000 records. I want to use the CDR Reports for billing purposes on a monthly basis.

Thank you Pat for the interesting script.
Unfortunately, on my system, this script reports an active call only after ten minutes it was established. Therefore calls that last less than 10 minutes are never reported.

Do you have an idea why this occurs?
Is there any setting that I can change to fix this issue?

Hello Pat,
I would like to ask you if it is possible for you to send me script for Lync2010 which should be available at http://www.confusedamused.com/notebook/monitoring-ocs-and-lync-peak-call-capacity/#comment-2604. mpaulovic AT yahoo dot com

Thank you!

Michal

The script will not work properly with Lync 2013 as the logging has changed and the database entries are now only written at the end of the call.

Hi great script but i found a small bug in the following line.
Write-Verbose “GMT Offest: $GMTOffsetHours:$GMTOffsetMinutes”
You need an ` in front of the : to escape the character.

Hello Pat, was there a newer version fro Lync2013/Sfb2015 server ever released?

@Pat Richard

Thanks Pat for the feedback! Due to MSFT messing with the CDR schema in every major release, probably it is a huge effort to develop the solid query for Lync2013/Sfb2015…

Is there an updated version of this (or in the making?)? Can’t get this to show ‘realtime’ data on newest SfB onprem.

Hi Pat

Had an error:
At C:\appl\tools\Get-CsVoiceCalls.ps1:78 char:29
+ Write-Verbose “GMT Offest: $GMTOffsetHours:$GMTOffsetMinutes”
+ ~~~~~~~~~~~~~~~~
Variable reference is not valid. ‘:’ was not followed by a valid variable name character. Consider using ${} to delimit the name.
+ CategoryInfo : ParserError: (:) [], ParseException
+ FullyQualifiedErrorId : InvalidVariableReferenceWithDrive

Fixed it with masking the : with `
Write-Verbose “GMT Offest: $GMTOffsetHours`:$GMTOffsetMinutes”

Regards,
Manfred

@Manfred

hi, i am seeing the same error returned.

ITNOA

Hi Pat

Please upload it to GitHub to we can contribute it and support it

thx

ITNOA

Hi Pat

Please upload this script to GitHub and added to powershell script gallery to we can contribute it and support it.

thx