Once you have been working with Orchestrator for a long time, you may end up with different hierarchies of folders where you have your Runbooks organized. Sometimes these are logical and easy to understand, and sometimes it can be very difficult to find the Runbook you need. Orchestrator does natively provide a search utility to help you find these Runbooks in the Orchestrator Web Console, and as an alternative, I have created a PowerShell script GUI to perform this search.
Web Console Search Utility
My intuition suggested that this information would be easily found in the database, so I performed a web search and found https://automationnext.wordpress.com/2014/07/15/sql-query-to-get-absolute-path-of-all-orchestrator-runbooks/. This blog provided a database query that provided about 80% of the information required. After poking through the database structure, I was able to add to this query to also return the Runbook names.
As an automation engineer, I found it easy to write the script to get the information: it took me about 45 minutes to not only figure out the database query, but to then add it to a PowerShell script to return the required information. GUIs are a whole different story though. After downloading and installing Visual Studio and referencing Chris Conte’s content on using an XAML file to create the form, I then worked to provide a front end GUI to the script I had already written. The following is the product created.
Two files are required for the script to work: an XAML file and a PS1 file. Basic instructions are to put the XAML and PS1 files in the same directory and execute the PS1 file with an account that has at minimum read rights to the Orchestrator database. Once launched, you will see the following:
PowerShell Search Utility
Note: The connection string is built dynamically using the Orchestrator SQL Server name and connects to the default database name Orchestrator. If your database is on a non-default instance, you may need to find the SQL port used by the instance and append the hostname with : and the port number: labsql:1433. If you have a non-default database name, the script will need to be edited.
After entering the hostname of the SQL server where the Orchestrator database resides and a search string, select the Search button. The script will return any Runbook name that contains that string along with the Orchestrator Designer folder path to where it exists.
Once you initiate Search and the data is returned, three more fields are available: the Save to File button, a text field to enter the file path, and a message label.
The main reason I added the Save to File option is that when a lot of data is returned, you may not see it all in the form window. Having it in a file also makes it editable, selectable, etc.
Hope this is helpful to all. Please provide feedback on making it better. Happy automating!
Included files: Runbook Searcher