System Center Orchestrator – Runbook error alerts

When you want to keep track on how yours runbooks run, you can always get a select from Orchestrator database.

SELECT 
                            r.Name, r.Description, r.Path, a.Name, 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 OID.Value <> 'Policy stopped by user.'
                            AND OI.StartTime  >= DATEADD(HOUR, -1, GETDATE())
                            GROUP BY  r.Id,r.Description, r.Path, a.Name , oid.Value ,ai.Status, ai.StartTime, r.Name

From this point, you can create Runbook, which will check error events in the last hour.

After you use Query Database, it is time to manipulet string with Powershell.

$String ="Full line as a string with fields separated by ';' from SQL Runbook I.."
$RunbookName = $String.Split(";")[0]
$Description=$String.Split(";")[1]
$Path =$String.Split(";")[2]
$ActivitieName=$String.Split(";")[3]
$ErrorText=$String.Split(";")[4]
$Runbookstatus = $String.Split(";")[5]
$RunbookStartTime= $String.Split(";")[6]

Don’t forget to publish data, so you can do something with it.

For me, the best way was to send an e-mail to administrators.

Good luck!