If you have been using System Center Orchestrator for any period of time there are chances that you have many different unused runbooks in your environment.  Many organizations will version their runbooks and create new versions of runbooks without removing the old runbooks for fear that they might still be referenced through other runbooks.  I have been working with a customer recently that was afraid to delete their older runbooks because someone else might be using them.  After doing some digging in the Orchestrator database, I came up with a couple of queries that are really helpful in determining if another runbook is referencing an existing runbook.

The first query will return a list of all runbooks that call other runbooks:

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.UniqueID
inner join [Orchestrator].[Microsoft.SystemCenter.Orchestrator].[Runbooks] RB1 on RB1.ID = ACT.RunbookId
inner join [Orchestrator].[Microsoft.SystemCenter.Orchestrator].[Runbooks] RB2 on RB2.ID = TPOL.PolicyObjectID
where ACT.TypeName = 'Trigger Policy'


If you wanted to see information about a specific runbook you could easily modify the query as follows:


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.UniqueID
inner join [Orchestrator].[Microsoft.SystemCenter.Orchestrator].[Runbooks] RB1 on RB1.ID = ACT.RunbookId
inner join [Orchestrator].[Microsoft.SystemCenter.Orchestrator].[Runbooks] RB2 on RB2.ID = TPOL.PolicyObjectID
where ACT.TypeName = 'Trigger Policy' and RB2.Path = 'Path to your runbook'


These queries should allow you to know what runbook is being used by another and give you the information so you can update the runbooks to point to the new version and decommission (Export and delete) the older versions.

Enjoy!