System Center Orchestrator SQL Query–Query failed Runbooks

For your routine Orchestrator Checks, it can be vefy useful to see all failed Runbooks in a period. Of course you can go to each Runbook, and check the Logs, but to be honest, this is not fun.

So, there is a SQL Query to help on this. At the end of the the Query, there is a number, which will control the Timespan of the Query, in our example we want all failed Runbooks from the last 300 Hours, change this to your needs.

SQL Query get failed Runbooks

use Orchestrator

Select Name, TimeStarted, TimeEnded, POLICYINSTANCES.Status
From [Microsoft.SystemCenter.Orchestrator.Runtime].Jobs AS Jobs
 INNER JOIN POLICIES ON Jobs.RunbookId = POLICIES.UniqueID
 inner join POLICYINSTANCES on jobs.Id = POLICYINSTANCES.JobId 
 where POLICYINSTANCES.Status != 'success' 
 and  TimeEnded > dateadd(HOUR, -300, getdate()) 
 order by Name

SQL Query get success Runbooks

And, as i think, it is also interesting to see all successful Runbooks, here is the Query

use Orchestrator

Select Name, TimeStarted, TimeEnded, POLICYINSTANCES.Status
From [Microsoft.SystemCenter.Orchestrator.Runtime].Jobs AS Jobs
 INNER JOIN POLICIES ON Jobs.RunbookId = POLICIES.UniqueID
 inner join POLICYINSTANCES on jobs.Id = POLICYINSTANCES.JobId 
 where POLICYINSTANCES.Status = 'success' 
 and  TimeEnded > dateadd(HOUR, -300, getdate()) 
 order by Name

Here you can see an example

Michael Seidl aka Techguy