Determine which Runbook Server ran a runbook instance

When you’re troubleshooting an issue with a runbook and you have multiple runbook servers, it can be useful to know which runbook server the runbook was executed on. Recently I found myself in such a situation, and after a quick google search I couldn’t find any method of finding the runbook server. I decided to figure out how to get the information out of the database.

The diagram in this post was hugely helpful, and provided the table names for all of the tables related to running a runbook. Pretty much every object in the Orchestrator database is represented by a GUID, so matching these GUIDs to objects of interest was a key part of developing the SQL query. If you’re not interested in the methodology, scroll down to the bottom of the post for the SQL query.

The runbook I wanted to troubleshoot was called New-Incident. My first step was to find the runbook GUID, or UniqueID. The first query I tried was:

SELECT *
FROM POLICIES
WHERE Name LIKE 'New-Incident'

There were 5 records with this name, so I needed to narrow down the query further. I could see that most of the records were deleted (the deleted flag was set to 1), so I narrowed down the query:

SELECT *
FROM POLICIES
WHERE Name LIKE 'New-Incident' AND Deleted = 0

This returned the UniqueID (also called PolicyId in the PolicyInstances table).

Now that I knew the GUID for the runbook, I needed to find the specific runbook instance that I wanted more information about. I found the runbook instance in the Orchestrator console, which included the job ID:

The runbook (or policy) instances are stored in a table called POLICYINSTANCES. So putting together the PolicyID and JobId, I queried the POLICYINSTANCES table:

SELECT *
FROM POLICYINSTANCES
WHERE PolicyID = '8D1FDD1A-A19E-4EF8-A490-401DF70A39D2' AND JobId = '529c0268-b921-4727-a2c8-4305a69cf5b5'

This gave me more information about the runbook, including the action server GUID.

The only thing left now was to figure out which table corrolates the runbook GUID and server name. This took a bit of browsing through the database tables, but it didn’t take me long to find the right table and query it:

SELECT *
FROM [SCOrchestrator].[Microsoft.SystemCenter.Orchestrator.Runtime].[RunbookServers]
WHERE Id = 'B02D5F24-62B1-4AA7-BAEE-EE196E3CFBA0'

Now that I knew how to get the information from the database, I joined the above steps into a single SQL query. As promised, here it is:

SQL query to determine which Runbook Server ran a runbook instance

SELECT pol.Name AS RunbookName,
pi.Status,
pi.TimeStarted,
pi.TimeEnded,
rs.Name AS ActionServer
FROM POLICIES AS pol
JOIN POLICYINSTANCES AS pi ON pi.PolicyID = pol.UniqueID
JOIN [Orchestrator].[Microsoft.SystemCenter.Orchestrator.Runtime].[RunbookServers] AS rs ON rs.Id = pi.ActionServer
WHERE jobID = '529c0268-b921-4727-a2c8-4305a69cf5b5'

If you don’t have the job ID, or want to view results over a period of time, try this query instead:

Get last 2 days run history for a specific runbook

SELECT pol.Name AS RunbookName,
pi.Status,
pi.TimeStarted,
pi.TimeEnded,
rs.Name AS ActionServer
FROM POLICIES AS pol
JOIN POLICYINSTANCES AS pi ON pi.PolicyID = pol.UniqueID
JOIN [SCOrchestrator].[Microsoft.SystemCenter.Orchestrator.Runtime].[RunbookServers] AS rs ON rs.Id = pi.ActionServer
WHERE Pol.Name = 'New-Incident' AND pi.TimeStarted > DATEADD(DAY, -2, GETDATE())
ORDER BY TimeStarted DESC

Bear in mind that the results from both queries are in UTC time.

 

Rate this post:
Share this post:

Leave a Reply