System Center Orchestrator Query: Mapping Invoke Runbook Activities to Destination Runbooks

If you have attended any Orchestrator training, best practices session, read any book, blog, or article about design practices, you have most probably been advised to keep your Runbooks small and modular. So for example, instead of having one large Runbook which includes an activity to place a computer into Operations Manager maintenance mode, have one Runbook which has a sole purpose of only placing computers into maintenance mode. You can then use an Invoke Runbook activity to execute this Runbook at any time that you need to place a computer into maintenance mode. You now have a Runbook module or function.

There is allot of merit to this approach including the re-use of existing Rubooks, ease of development and troubleshooting if things go wrong. One drawback though is that it is possible to get into a situation in which you do not have a clear understanding of every Runbook that may be invoking another. As time passes you may configure more and more Runbooks to invoke a modular Runbooks (Operations Manager Maintenance Mode in this example). What happens if this Runbook needs to be replaced with a new Runbook? Worse yet what if I come into the office some Monday morning and see that my Operations Manager maintenance mode Runbook has a pending queue of 50,000, how do I quickly backtrack through all of my other Rubooks identifying those which may have created the mess? It is easy enough to look at each invoke Runbook activity and see what Runbook is being invoked, however how can we get this information in reverse? How can we quickly and concisely take a single Runbook and find all Invoke Runbook activites that map to this Runbook. 

The solution is obviously in the data – I hope you find this query as useful as I have found it.

Query: Run the following query against your Orchestrator database.

SELECT RB1.Name as 'Runbook Name',RB1.Path as 'Runbook Path',ACT.Name as 'Activity Name',RB2.Name as 'Called Runbook Name',RB2.Path as 'Called Runbook Path' FROM [Orchestrator].[Microsoft.SystemCenter.Orchestrator].[Activities] ACT inner join [Orchestrator].[dbo].[TRIGGER_POLICY] TPOL on ACT.Id = TPOL.UniqueIDinner join [Orchestrator].[Microsoft.SystemCenter.Orchestrator].[Runbooks] RB1 on RB1.ID = ACT.RunbookId inner join [Orchestrator].[Microsoft.SystemCenter.Orchestrator].[Runbooks] RB2 on RB2.ID = TPOL.PolicyObjectIDwhere ACT.TypeName = 'Trigger Policy'

Results: We are returned with the invoking Runbook name and path (which is important as we can have many Rubooks with the same name), the name of the activity that invokes the second Runbook (important because we can have multiple Invoke Runbook Activities per invoking Runbook), and finally the name and path of the invoked Runbook.

Click Image for a better view:

Throw in a Where statement specifying the name of the Called Runbook (WHERE RB2.Name = 'RunbookName'), and you can quickly find every Runbook that is configured to invoke a specific Runbook.

There you have it - simple, informative.

Good Day!