SQL Query to get absolute path of all Orchestrator Runbooks

Orchestrator stores information about folders and Runbooks in parent-child hierarchy within SQL Server Database. Folders and Runbooks can be nested with each other in great depths. At times, we need the entire path of the Runbook from the hierarchy and there is no easy out of box way to retrieve it through the product. SQL Query comes to the rescue!!

We can use the below sql query to obtain all the folder and Runbook paths within an Orchestrator installation. It will get runbooks that are not deleted and are enabled. ‘00000000-0000-0000-0000-000000000000’ in below query is the top level parent in the hierarchy and refers to the Policies folder.

with RunbookPath as
(
select 'Policies\' + cast(name as varchar(max)) as [path], uniqueid from dbo.folders b
where b.ParentID='00000000-0000-0000-0000-000000000000' and disabled = 0 and deleted= 0
union all
select cast(c.[path] + '\' + cast(b.name as varchar(max)) as varchar(max)), b.uniqueid from dbo.FOLDERS b
inner join
RunbookPath c on b.ParentID = c.UniqueID
where b.Disabled = 0 and b.Deleted = 0
)
select [Path] from RunbookPath

Cheers!!

Advertisements