Hello there,
I know this platform is used for questions about SCOM, but I thought it will be nice to also use it to share ideas and scripts that are related to SCOM monitoring.
As a system administrator, I get to work with lots of different software products and servers that each has a different purpose. Also, I need to find a way to monitor these platforms using SCOM.
One of the software products we use in our organization is “System Center Orchestrator”. I always tried to look for a MP that will offer the ability to monitor and catch runbooks jobs failures that occur in Orchestrator, but couldn’t find one…
That led me to the idea of creating a rule monitor using the “Powershell community MP”, that will catch these failures and will raise an alarm for each runbook object that is displayed in SCOM.
It is needless to say that I used another MP that create these Orchestrator runbook objects in SCOM in order to monitor them (Link to this MP).
The monitor is SQL based, and is querying Orchestrator DB in order to find runbook jobs failuers. The script is finding all the runbook jobs failures that happened in the last hour (that can be changed). Then according to the result the script creates a readable description regarding the data that was collected from the DB, and it present it in the alarm description. The information we get is going all the way down to the level of the activity that caused the runbook to fail running.
Anyway, I thought it will be very useful for those who use Orchestrator and wants to have the ability to monitor their runbooks without having to log to the “Runbook Designer” and look for a critical events.
Here is the script for your use:
<#
.SYNOPSIS
Alerts on runbook jobs failures that occuer in Orchestrator.
.DESCRIPTION
This script will use SQL query in order to fetch any data related to runbook jobs that failed in Orchestrator.
It receives the ID of the runbook we wish to monitor as an argument, and search for any runbook jobs failures.
Upon finding runbook jobs failures, the script will create an array that will display the data regarding the job failure
in a readable format.
#>
param([string]$Arguments)
# Create SCOM objects
$API = New-Object -ComObject "MOM.ScriptAPI"
$PropertyBag = $API.CreatePropertyBag()
$Status = "OK"
$Description = " "
#$API.LogScriptEvent("MonitorRunbooks.ps1",9995,1,"We received a runbook ID of " + $Arguments)
#Query to get runbooks that failed running
$connectionString = "Data Source=ENTER YOUR DATA SOURCE HERE; " +
"Integrated Security=SSPI; " +
"Initial Catalog=Orchestrator"
$sconn = new-object System.Data.SQLClient.SQLConnection($connectionString)
$Query = @"
SELECT
r.Name [Runbook], r.Id,r.Description, r.Path, a.Name [Activity], oid.Value ,ai.Status, ai.StartTime
FROM
[Microsoft.SystemCenter.Orchestrator].[Runbooks] r
INNER JOIN [Microsoft.SystemCenter.Orchestrator].[Activities] a ON a.RunbookId = r.Id
INNER JOIN [Microsoft.SystemCenter.Orchestrator.Runtime].[ActivityInstances] ai ON ai.ActivityId = a.Id
INNER JOIN [Microsoft.SystemCenter.Orchestrator].[Resources] res ON res.UniqueId = r.Id
INNER JOIN dbo.OBJECTS OBJ on OBJ.ParentID = r.Id
INNER JOIN OBJECTINSTANCES OI on OI.ObjectID = OBJ.UniqueID
INNER JOIN OBJECTINSTANCEDATA OID on OID.ObjectInstanceID = Oi.UniqueID
WHERE
ai.StartTime >= DATEADD(HOUR, -1, GETDATE()) AND ai.Status = 'failed' AND OID.[Key] = 'ErrorSummary.Text' AND OID.Value <> ''
AND OI.StartTime >= DATEADD(HOUR, -1, GETDATE())
AND r.Id='$Arguments'
GROUP BY r.Id,r.Description, r.Path, a.Name , oid.Value ,ai.Status, ai.StartTime, r.Name
--ORDER BY Runbook;
"@
$cmd = new-object System.Data.SQLClient.SQLCommand ($Query,$sconn)
$sconn.Open()
$Adapter = New-Object System.Data.sqlclient.sqlDataAdapter $cmd
$DS = New-Object System.Data.DataSet
$Adapter.Fill($DS) | Out-Null
$Result = $DS.Tables
$sconn.Close()
if($DS.Tables[0].Rows.Count -gt 0){
$Status = "Error"
#$API.LogScriptEvent("MonitorRunbooks.ps1",9996,1,"This monitor has run with a status of " + $Status + " on the runbook with the ID: " + $Result)
if($ds.Tables[0].Rows.Runbook.count -eq 1){
$Description += @("`n Runbook : " + $ds.Tables[0].Rows.Runbook + "`r"
"Id : " + $ds.Tables[0].Rows.Id.Guid +"`r"
"Description : " + $ds.Tables[0].Rows.Description +"`r"
"Path : " + $ds.Tables[0].Rows.Path +"`r"
"Activity : " + $ds.Tables[0].Rows.Activity +"`r"
"Status : " + $ds.Tables[0].Rows.Status +"`r"
"StartTime : " + $ds.Tables[0].Rows.StartTime +"`r"
"Value : " + $ds.Tables[0].Rows.Value +"`n`n`n")
}
Else{
for($i=0; $i -lt $ds.Tables[0].Rows.Runbook.count; $i++){
$Description += @("`nRunbook : " + $ds.Tables[0].Rows.Runbook[$i] + "`r"
"Id : " + $ds.Tables[0].Rows.Id[$i].Guid +"`r"
"Description : " + $ds.Tables[0].Rows.Description[$i] +"`r"
"Path : " + $ds.Tables[0].Rows.Path[$i] +"`r"
"Activity : " + $ds.Tables[0].Rows.Activity[$i] +"`r"
"Status : " + $ds.Tables[0].Rows.Status[$i] +"`r"
"StartTime : " + $ds.Tables[0].Rows.StartTime[$i] +"`r"
"Value : " + $ds.Tables[0].Rows.Value[$i] +"`n`n`n")
}
}
}
#Sending data to SCOM
$PropertyBag.AddValue("Description",$Description)
$PropertyBag.AddValue("Status",$Status)
#$API.LogScriptEvent("MonitorRunbooks.ps1",9999,1,"This monitor has run with a status of " + $Status + " on the runbook with the ID: " + $Arguments)
# Send output to SCOM
$PropertyBag
If there are any questions or issues related to this script, please let me know and I’ll do my best to help.
Alon.
