#############################################################################
# Parameters
#
# [ ClusterFqdn ] - Computer (FQDN) that Lync Monitoring Server is hosted on
# [ MinutesToQuery ] - Analyze CDR data for the last "MinutesToQuery" number of minutes.
# [ AlertThresholdPercentage ] - Consider Diagnosis IDs that cross this provided percentage value
# - of total call completions as "Alerts"
# [ MinUsersImpacted ] - Consider Minimum numbers of users that get impacted before raising Alert.
# [ MinVolume ] - Consider Minimum numbers of calls per modality before raising Alert.
# [ SQLInstance ] - SQL Instance where monitoring store is located. This Instance has the LcsCDR database.
# [ MirrorSQLInstance ] - SQL Mirror Instance where monitoring store is located. This Instance has the LcsCDR database.
# [ ReportingServiceUrl ] - The base URL of the Reporting Server.
#
#############################################################################
#LOGS & EXCEPTIONS###########################################################
$CDR_DSMODULE_RUN_SUCCESS = "CDR DataSource Module run execution completed for '{0}'`n"
#############################################################################
#############################################################################
# Functions for Database Connections
#############################################################################
# Set the Threshold Settings
function CDR_GetAlertSettings($StartUTCTime, $EndUTCTime, $AlertThresholdPercentage, $MinUsersImpacted, $MinVolume)
{
# The XML string that will be passed to sproc that queries CDR database
$AlertSettingXML =
"
<AlertSetting>
<Settings
StartTime=`"" + $StartUTCTime+"`"
EndTime=`"" + $EndUTCTime+"`"
AlertThresholdPercentage=`""+ $AlertThresholdPercentage + "`"
MinUsersImpacted=`"" + $MinUsersImpacted + "`"
MinVolume=`"" + $MinVolume + "`"
/>
</AlertSetting>
"
$AlertSettingXML
}
# return a string with two digits.
# make it start with "0" if the input string contains only one digit
function CDR_TwoDigits( $sDigits )
{
$s = "0" + $sDigits
if ( $s.length -lt 2 )
{
"00"
}
else
{
$s.substring($s.length-2, 2)
}
}
# Bind the AlertSetting parameter as Xml type
$SqlCmd.Parameters.Add("@AlertSettingsXMLArg", [System.Data.SqlDbType]::NVarChar ).Value = $AlertSettingsXML
# Encapsulate connection and reader to query context.
$QueryContext = @( [ref]$SqlConnection, [ref]$SqlDataReader )
$QueryContext
}
# Get sql data reader from the query context.
function CDR_DB_GetDataReaderReference($QueryContext ) {
$RefSqlDataReader = $QueryContext[1]
$RefSqlDataReader
}
# Close connection and reader in the query context.
function CDR_DB_CloseQueryContext($QueryContext ) {
$QueryContext[1].Value.Close() # SqlDataReader
$QueryContext[0].Value.Close() # SqlConnection
}
#############################################################################
# Column ID for each column of the result set of QoeGetScomInstance sproc
# See dev\server\qoe\aggregator\sql\perfcountergeneration.sql for the resultset of QoeGetScomInstance sproc
$CDR_SPROC_COLUMN_ID_MAP =
@{
"Media"=0;
"DiagnosticId"=1;
"DistinctUsers"=2;
"NumberOfCalls"=3;
"VolumePerModality"=4;
"ReasonString"=5;
"Description"=6;
"PercentageOfVolume"=7;
}
# Now compute the StartTime and EndTime. Set the current time (in UTC).
$CurrentUTCTime = [System.DateTime]::UtcNow
# Subtract 15 sec from the endtime to avoid collisions with calls that are being inserted currently
$EndUTCTime = $CurrentUTCTime.AddSeconds(-15)
TRACE ("EndUTCTime is " + $EndUTCTime)
# The query window is go past 'MinutesToQuery' number of minutes.
$StartUTCTime = $EndUTCTime.AddMinutes( -1 * $MinutesToQuery )
TRACE ("StartUTCTime is " + $StartUTCTime)
TRACE ("Querying the CDR DB : " + $SQLInstance)
$QueryContext = CDR_DB_OpenQueryContext $SQLInstance $MirrorSQLInstance $AlertSettingsXML
TRACE ("CDR_DB_OpenQueryContext after")
# Caution : Should use [ref] type for SqlDataReader.
# Otherwise SqlDataReader reads all records from resultset once
# (1)it is assigned to a varible or
# (2)passed as an argument to a function
$RefDataReader = CDR_DB_GetDataReaderReference $QueryContext
TRACE ("CDR_DB_GetDataReaderReference after")
# Create an Operations Manager EventLog reference.
$EventLog = New-Object System.Diagnostics.EventLog("Operations Manager")
# Set the Source to Health Service Script. This is used in the expression in the rule.
$EventLog.Source = "Health Service Script"
# Create an EventInstance reference. We pass a Task Category of None ( as indicated by 0 )
$EventInstance = New-Object System.Diagnostics.EventInstance($CDRRuleEventId, 0 , $EVENT_INFORMATION)
foreach($record in $records)
{
TRACE ("Last Diag Id is " + $lastDiagId)
$diagnosticId = $record["DiagnosticId"];
TRACE ("diagnosticId = " + $diagnosticId)
$reasonString = $record["ReasonString"];
TRACE ("reasonString = " + $reasonString)
$description = "Calls ( with Diagnosis code '" + $DiagnosticId + "' ) are failing at higher than expected rates.`n" +
"`n" +
"Here are the details for this Call Failure Alert:`n" +
"Diagnosis Code = " + $diagnosticId + "`n" +
"Diagnosis Reason = " + $reasonString + "`n" +
"Diagnosis Description = " + $record["Description"] + "`n" +
"`n" +
"The failure happens for the following modalities:" + "`n" ;
$modalities = $record["Modalities"]
foreach($modality in $modalities)
{
$description += "Media Type = " + $modality["Media"] + "`n" +
"Distinct Users Impacted = " + $modality["DistinctUsers"] + "`n" +
"Total Calls = " + $modality["VolumePerModality"] + "`n" +
"Failed Calls = " + $modality["NumberOfCalls"] + "`n" +
"Failure Percentage = " + $modality["PercentageOfVolume"] + "`n"
}
TRACE ("Description = " + $description)
# TODO:NAFIX
# Remove the hardcoding. Report name needs to be externalized
# Report parameter names need to be externalized
# 1 is conf, 5 is MediaId Audio, and 2 is Error Category of Unexpected errors.
# This need to be read from some common place
if([system.string]::IsNullOrEmpty($ReportingServiceUrl))
{
$reportUrlMessage = "Reporting URL is not available because the reporting pack is not installed.`n" +
"To see trouble-shooting reports, please install the reporting pack.`n"
}
else
{
$url = $null
if([System.Uri]::TryCreate($ReportingServiceUrl, [System.UriKind]::RelativeOrAbsolute, [ref]$url))
{
if($url -ne $null -and $url.Segments.Length -gt 2)
{
$ReportingServiceUrl = $url.Scheme + "://" + $url.Authority + $url.Segments[0] + $url.Segments[1]
$reportUrlMessage = "Please copy/paste this URL into your browser to get a detailed information about this failure alert:`n" +
$ReportingServiceUrl +
"/Pages/ReportViewer.aspx?/LyncServerReports/Reports_Content/Failure%20Distribution%20Report" +
"&PeriodStartUTC=" + (CDR_EncodeUrl (CDR_FormatUTCTime $StartUTCTime) ) +
"&PeriodEndUTC=" + (CDR_EncodeUrl (CDR_FormatUTCTime $EndUTCTime) ) +
"&Filter_MsDiagId=" + $diagnosticId
# Create an EventData Array.
# Individual event data elements can then used to set AlertParameters in the Alerting Rule.
$eventData = @(
$displayName ,
$description ,
$reportUrlMessage ,
$diagnosticId ,
$reasonString ,
$record["Description"]
)
TRACE ("Logging an event in Operations Manager log with all this info. Event Id = " + $CDRRuleEventId)
$EventLog.WriteEvent($EventInstance, $eventData)
TRACE ("Logged the event in Operations Manager log.")
Home
Show References: