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%'