Orchestrator Runbook Jobs Failures Rule

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.

2 Likes

Thanks for sharing - at present we use the Infront Management Pack for Orchestrator and in a previous life used this:

https://blog.tyang.org/2015/09/14/updated-mp-authors-system-center-2012-orchestrator-management-pack/

Not sure if the latter will still work with SCOM 2016 \ 180x

Cheers

Graham

Hi there Graham,

Thanks for replying!

At the moment I’m using it with SCOM v.1801, and it’s working fine (Orchestrator is also of this version) :slight_smile:

Another advantage I think SCOM users will have, is the fact that they’ll be able to see the alerts about runbook failure, and the activity that failed with the reason for its failure, without having to access “Runbook Designer”. I think that SCOM isn’t meant only to monitor and present alert when needed to, but also to give us a real, detailed info about the problem, which I haven’t seen in other MP’s till far…

Plus, it’s free thus it’s more accessible to others who use SCOM in my opinion ;).