A simple SQL query can be run on the CMDistribution database to bring back the status of the Replication Agents. Using this information an email alert can be triggered using Orchestrator.
I've scheduled the parent runbook to execute once a day using a Windows scheduled task and SCOJobrunner.exe.
The resulting email will look something like this (if you have 4 SCCM MP's):
Overview
(Parent runbook)
(Child Runbook)
Runbook Activity's
The first step invokes the child runbook, this is done so that the data retrieved using the SQL query is flattened to prevent sending an email per MPReplica.
- Run SQL query to retrieve the status of each MPReplica from CMDistribution DB. Status values are as follows:
- Started
- Succeeded
- In Progress
- Idle
- Retrying
- Failed
SELECT status AS ReplicationAgentsStatus
FROM dbo.MSReplication_monitordata
WHERE publication = 'ConfigMgr_MPReplica'
- We only need to alert when the status = 5 or 6. So the links from the SQL query will split based on this.
(Go to 'Error' when SQL string contains 5 or 6)
(Go to "OK" when SQL string contains 1,2,3 or 4)
- In order to create a Returned Data value and generate some events (just in case SCOM needed to collect the status in the future) I used 'Send Event Log Message' activity's.
- The 'Return Data' activity's include the activity name from either 'Send Event Log Message'
(Return Activity Name from 'OK')
(Return Activity Name from 'Error")
(Parent runbook Returned Data from child runbook)
- Using the flattened return data, determine whether to send an email alert or not. If there are 4 SCCM MPReplica's with no issues the data will look like: "OK,OK,OK,OK". If there was an issue with one of the servers it would look like: "OK,Error,OK,OK". The links to the subsequent activity look like this:
(Go to 'Send Email' if returned data contains the word 'Error')
(Go to OK if returned data doesn't contain 'Error')
- If there is an error, use PowerShell script to generate an email with the current status of MPReplica's in HTML format. This PS script uses the SCORCH best practice template (More info) because PS v.2 doesn't support the SQL functions needed.
The SQL and SMTP details are stored as Orchestrator variables which are called in the PS script.
$Username = "\`d.T.~Vb/{9995A45F-87F0-40B9-8D7A-AA113E4EB251}\`d.T.~Vb/"
$Password = "\`d.T.~Vb/{AC9E30A6-7F4F-4E2E-BA15-82ECE7D58301}\`d.T.~Vb/"
$Database = "CMDistribution"
$SQLServer = "\`d.T.~Vb/{68A5FAA6-AFEC-4DE0-A40F-D06DEB80BCA6}\`d.T.~Vb/"
$FROM="\`d.T.~Vb/{E755220C-D529-46B9-B72E-D105CE15C5BC}\`d.T.~Vb/"
$SMTP = "\`d.T.~Vb/{806E12A2-90B9-41BE-9F2F-7E32BAEF57B0}\`d.T.~Vb/"
$smtpUsername = "TFL\\`d.T.~Vb/{3E209AB6-5BE8-4D67-AB26-6A797DC68F91}\`d.T.~Vb/"
$smtpPassword = "\`d.T.~Vb/{A2168CE3-4E2A-404B-9A1B-2D245165A526}\`d.T.~Vb/"
#-----------------------------------------------------------------------
## Initialize result and trace variables
# $ResultStatus provides basic success/failed indicator
# $ErrorMessage captures any error text generated by script
# $Trace is used to record a running log of actions
$ResultStatus = ""
$ErrorMessage = ""
$Trace = (Get-Date).ToString() + "`t" + "Runbook activity script started" + " `r`n"
# Create argument array for passing data bus inputs to the external script session
$argsArray = @()
$argsArray += $Username
$argsArray += $Password
$argsArray += $Database
$argsArray += $SQLServer
$argsArray += $FROM
$argsArray += $SMTP
$argsArray += $smtpUsername
$argsArray += $smtpPassword
# Establish an external session (to localhost) to ensure 64bit PowerShell runtime using the latest version of PowerShell installed on the runbook server
# Use this session to perform all work to ensure latest PowerShell features and behavior available
$Session = New-PSSession -ComputerName localhost
# Invoke-Command used to start the script in the external session. Variables returned by script are then stored in the $ReturnArray variable
$ReturnArray = Invoke-Command -Session $Session -Argumentlist $argsArray -ScriptBlock {
# Define a parameter to accept each data bus input value. Recommend matching names of parameters and data bus input variables above
Param(
[ValidateNotNullOrEmpty()]
[string]$Username,
[ValidateNotNullOrEmpty()]
[string]$Password,
[ValidateNotNullOrEmpty()]
[string]$Database,
[ValidateNotNullOrEmpty()]
[string]$SQLServer,
[ValidateNotNullOrEmpty()]
[string]$FROM,
[ValidateNotNullOrEmpty()]
[string]$SMTP,
[ValidateNotNullOrEmpty()]
[string]$smtpUsername,
[ValidateNotNullOrEmpty()]
[string]$smtpPassword
)
# Define function to add entry to trace log variable
function AppendLog ([string]$Message)
{
$script:CurrentAction = $Message
$script:TraceLog += ((Get-Date).ToString() + "`t" + $Message + " `r`n")
}
# Set external session trace and status variables to defaults
$ResultStatus = ""
$ErrorMessage = ""
$script:CurrentAction = ""
$script:TraceLog = ""
try
{
# Add startup details to trace log
AppendLog "Script now executing in external PowerShell version [$($PSVersionTable.PSVersion.ToString())] session in a [$([IntPtr]::Size * 8)] bit process"
AppendLog "Running as user [$([Environment]::UserDomainName)\$([Environment]::UserName)] on host [$($env:COMPUTERNAME)]"
AppendLog "Parameter values received:Username=[$Username] Database=[$Database] SQLServer=[$SQLServer] FROM=[$FROM] SMTP=[$SMTP] smtpUsername=[$smtpUsername]"
# The actual work the script does goes here
AppendLog "SQL Query to retrieve accounts"
$pass = ConvertTo-SecureString -AsPlainText $smtpPassword -Force
$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $smtpUsername,$pass
$Array = @(Invoke-SQLCmd -query "SELECT agent_name,status,lastrefresh,last_distsync,time_stamp,cur_latency FROM dbo.MSReplication_monitordata WHERE publication = 'ConfigMgr_MPReplica'" -Server $SQLServer -Username $Username -Password $Password -Database $Database) | ConvertTo-Html -Fragment
# Build and send email for each contact.
AppendLog "Build and send email"
# Config at top of script
# Param for sending mail
$smtpsettings = @{
To = "\`d.T.~Vb/{57C5F4D7-D2FA-46E5-8D10-A31FACFE16EB}\`d.T.~Vb/"
From = $From
SmtpServer = $SMTP
Subject = "ConfigMgr MPReplica Status Error"
Port = "587"
Credential = $cred
}
$date = (Get-Date).AddDays(-60)
$htmlhead = "<html>
<style>
BODY{font-family: Calibri; font-size: 11pt;}
H1{font-size: 22px; font-family: 'Segoe UI Light','Segoe UI','Lucida Grande',Verdana,Arial,Helvetica,sans-serif;}
H2{font-size: 18px; font-family: 'Segoe UI Light','Segoe UI','Lucida Grande',Verdana,Arial,Helvetica,sans-serif;}
H3{font-size: 16px; font-family: 'Segoe UI Light','Segoe UI','Lucida Grande',Verdana,Arial,Helvetica,sans-serif;}
TABLE{border: 1px solid black; border-collapse: collapse; font-size: 8pt;}
TH{border: 1px solid #969595; background: #dddddd; padding: 5px; color: #000000;}
TD{border: 1px solid #969595; padding: 5px; }
td.pass{background: #B7EB83;}
td.warn{background: #FFF275;}
td.fail{background: #FF2626; color: #ffffff;}
td.info{background: #85D4FF;}
</style>
<body>
<p>Hello<br>There is an issue with replication on the SCCM MP Replica's. Please see below for more information.<br><br>
Yours Faithfully,<br>
Orchestrator</p>"
$htmltail = "</body></html>"
$body = $htmlhead + $array + $htmltail
Send-MailMessage @smtpsettings -body $body -BodyAsHtml
# Validate results and set return status
AppendLog "Finished work, determining result"
$EverythingWorked = $true
if($EverythingWorked -eq $true)
{
$ResultStatus = "Success"
}
else
{
$ResultStatus = "Failed"
}
}
catch
{
# Catch any errors thrown above here, setting the result status and recording the error message to return to the activity for data bus publishing
$ResultStatus = "Failed"
$ErrorMessage = $error[0].Exception.Message
AppendLog "Exception caught during action [$script:CurrentAction]: $ErrorMessage"
}
finally
{
# Always do whatever is in the finally block. In this case, adding some additional detail about the outcome to the trace log for return
if($ErrorMessage.Length -gt 0)
{
AppendLog "Exiting external session with result [$ResultStatus] and error message [$ErrorMessage]"
}
else
{
AppendLog "Exiting external session with result [$ResultStatus]"
}
}
# Return an array of the results. Additional variables like "myCustomVariable" can be returned by adding them onto the array
$resultArray = @()
$resultArray += $ResultStatus
$resultArray += $ErrorMessage
$resultArray += $script:TraceLog
$resultArray += $myCustomVariable
return $resultArray
}#End Invoke-Command
# Get the values returned from script session for publishing to data bus
$ResultStatus = $ReturnArray[0]
$ErrorMessage = $ReturnArray[1]
$Trace += $ReturnArray[2]
$MyCustomVariable = $ReturnArray[3]
# Record end of activity script process
$Trace += (Get-Date).ToString() + "`t" + "Script finished" + " `r`n"
# Close the external session
Remove-PSSession $Session
0 comments:
Post a Comment