I had a client request that I search within all scripts within their PowerShell objects in Orchestrator for a string. They had over 1,000 Runbooks and an unknown number of PowerShell scripts. The key is to search in the QIKOBJECT table, joined to the OBJECTS table, within the Properties field under QIKOBJECT.
Here is an example:
select f.Name, p.Name, o.Name,q.Properties from [dbo].[QIKOBJECT] q
INNER JOIN [dbo].[OBJECTS] o ON q.UniqueID = o.UniqueID
INNER JOIN [POLICIES] p on p.UniqueID = o.ParentID
INNER JOIN [dbo].[FOLDERS] f ON f.UniqueID = p.ParentID
WHERE q.Properties like ‘%string%’
AND o.Deleted <> 1
ORDER BY f.Name, p.Name, o.Name
Find PowerShell objects within this query:
select * FROM [dbo].[OBJECTTYPES] WHERE [Name] Like ‘%PowerShell%’
Or the title of an Activity:
select * from [dbo].[OBJECTS] WHERE [Name] = ‘title of an activity’
List all Runbooks in a Folder:
select TOP 10 f.Name,* from [dbo].[POLICIES] p
INNER JOIN [dbo].[FOLDERS] f ON p.ParentID = f.UniqueID
WHERE f.[Name] = ‘name of folder’
AND p.Deleted = 0
ORDER BY f.name, p.name
Some good notes: http://blogs.technet.com/b/orchestrator/archive/2012/05/29/ips-and-activities-and-how-those-are-represented-in-the-database.aspx
