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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s