Thursday, September 26, 2019

SCORCH: Find runbook in folder structure

When you know a runbook exists but you can't find the folder it resides in, you can try this SQL query. To retrieve the complete path from the root of the folder structure down to the runbook, i used a Common Table Expression (CTE). It works like a recursive function.
with ItemHierarchy (UniqueID, Name, ParentID, Path, Level) as
(
 select fol.UniqueID, fol.Name, fol.ParentID, CAST(fol.Name as varchar(max)), 0
 from FOLDERS fol
 where fol.ParentID IS NULL

 union all

 select fol.UniqueID, fol.Name, fol.ParentID, CAST(par.Path + '\' + fol.Name AS varchar(max)), par.Level + 1
 from FOLDERS fol
 inner join ItemHierarchy par on fol.ParentID = par.UniqueID
)
select pol.Name as 'PolicyName', ith.Name as 'FolderName', ith.Path as 'Folder Path'
from POLICIES pol
inner join ItemHierarchy ith on pol.ParentID = ith.UniqueID
where pol.Name like '%RunbookName%'

2 reacties:

Unknown said...

I just tried this script and I get the following error:
Invalid object name 'FOLDERS'

Any suggestions?

Michiel Wouters said...

Hi Unknown, I don't know whether the database model has changed, but if not, you can try to explicitly set the database name at the top of the query: Use Orchestrator. Furthermore you can try to add the database and schema to the table name in the FROM clause, like [Orchestrator].[dbo].[FOLDERS]

Post a Comment