I wanted to share some helpful Orchestrator SQL queries that could save you some time when managing or troubleshooting your Orchestrator environment.
Since there are not many tools available for Orchestrator I find it easiest by doing SQL queries straight in the Orchestrator database.
Contents
- See all changes in Runbooks
- See all checked out Runbooks
- See all checked in Runbooks
- See all client connections
- Find the PID of a specific Runbook
- Check the Runbook job status
- Get the Runbook GUID
- Find a deleted folder
- Recover a deleted folder
- Find a deleted Runbook
- Recover a deleted Runbook
See all changes in Runbooks
If you need to audit changes that’s been done to your runbooks, you can use the following SQL query to list all changes in all runbooks:
SELECT P.Name AS [Runbook Name], O.Name AS [Activity Name], OT.Name AS [Activity Type], OA.Action, CASE WHEN OA.Attribute LIKE '%[0-F][0-F][0-F][0-F][0-F][0-F][0-F][0-F]-[0-F][0-F][0-F][0-F]- [0-F][0-F][0-F][0-F]-[0-F][0-F][0-F][0-F]-[0-F][0-F][0-F][0-F][0-F][0-F][0-F][0-F][0-F][0-F] [0-F][0-F]%' THEN 'NEW ACTIVITY' ELSE OA.Attribute END AS Attribute, OA.OldValue, OA.NewValue, CIH.DateTime AS [Change Timestamp], S.Account AS [User] FROM OBJECT_AUDIT AS OA INNER JOIN OBJECTS AS O ON OA.ObjectID = O.UniqueID INNER JOIN POLICIES AS P ON O.ParentID = P.UniqueID INNER JOIN OBJECTTYPES AS OT ON OA.ObjectType = OT.UniqueID INNER JOIN CHECK_IN_HISTORY AS CIH ON CIH.UniqueID = OA.TransactionID INNER JOIN SIDS AS S ON CIH.CheckInUser = S.SID WHERE (O.Deleted = 0) ORDER BY [Change Timestamp] DESC
See all checked out Runbooks
Not sure if you have checked in all runbooks? No problem! With the following SQL query you can see all checked out runbooks:
SQL Query:
SELECT p.Name ,p.CheckOutTime ,p.CheckOutLocation ,p.Description ,s.Account FROM Orchestrator.dbo.POLICIES P Join Orchestrator.dbo.SIDS S on P.CheckOutUser = S.SID Where CheckOutUser is not NULL and p.Deleted = 0
See all checked in Runbooks
To make sure you have checked in all your runbooks, you can double check it by running this SQL query:
SQL Query:
SELECT TOP (10) SIDS.Account, POLICIES.Name, CHECK_IN_HISTORY.Comment, CHECK_IN_HISTORY.DateTime FROM CHECK_IN_HISTORY INNER JOIN SIDS ON CHECK_IN_HISTORY.CheckInUser = SIDS.SID INNER JOIN POLICIES ON CHECK_IN_HISTORY.ObjectID = POLICIES.UniqueID ORDER BY CHECK_IN_HISTORY.DateTime DESC
Check all current client connections
If you want to check who’s connected to your Orchestrator environment’s console, you can do it with the following query:
SQL Query:
SELECT TOP 10 [UniqueID] ,[ManagementServer] ,[ClientMachine] ,[ClientUser] ,[ClientVersion] ,[ConnectionTime] ,[LastActivity] FROM [Orchestrator].[dbo].[CLIENTCONNECTIONS]
Find the process ID of a runbook
Ever had a runbook that gets stuck or drain a lot of memory? The solution is then to kill the runbook’s process but first you will need to know the process ID. Here’s how you can find the process ID of a runbook:
SQL Query:
SELECT POLICYINSTANCES.TimeStarted, POLICYINSTANCES.TimeEnded, POLICYINSTANCES.ProcessID, POLICYINSTANCES.SeqNumber, POLICIES.Name FROM POLICYINSTANCES INNER JOIN POLICIES ON POLICYINSTANCES.PolicyID = POLICIES.UniqueID ORDER BY POLICYINSTANCES.TimeStarted DESC
Check the runbook job status
You can of course check the status of a runbook in the log in Orchestrator console, but what if you need to check the status of many runbooks which are spread out all over the place? Here’s the solution for you:
SQL Query:
SELECT PI.Status, POLICIES.Name, PI.TimeEnded, PI.TimeStarted, PI.Computer FROM POLICIES INNER JOIN (SELECT PI1.PolicyID, PI1.TimeStarted, PI1.TimeEnded, PI1.Status, ACTIONSERVERS.Computer FROM PolicyInstances AS PI1 INNER JOIN ACTIONSERVERS ON PI1.ActionServer = ACTIONSERVERS.UniqueID WHERE PI1.TimeEnded = (SELECT MAX(PI2.TimeEnded) FROM PolicyInstances AS PI2 WHERE PI2.PolicyID = PI1.PolicyID) ) AS PI ON PI.PolicyID = Policies.UniqueID WHERE (POLICIES.Deleted = 0) AND (POLICIES.CheckOutUser IS NULL)
Get the GUID of a runbook
There’s a few ways of getting to know a GUID of a runbook, I will post two ways here:
- By going to the Orchestrator’s Web GUI, browse to a runbook and highlight the URL string. Paste it into a text editor and you can pick out the GUID.
- By running the following query in the Orchestrator database:
Note: You need to insert the name of your runbook in the SQL query.
SQL Query:
Select lower(POLICIES.UniqueID) as RunbookID, lower(CUSTOM_START_PARAMETERS.UniqueID) as ParameterID, CUSTOM_START_PARAMETERS.value From POLICIES INNER JOIN OBJECTS on POLICIES.UniqueID = OBJECTS.ParentID LEFT OUTER JOIN CUSTOM_START_PARAMETERS on OBJECTS.UniqueID = CUSTOM_START_PARAMETERS.ParentID Where POLICIES.Name = 'My Runbook Name' and policies.deleted = 0
Find a deleted folder
Accidentally deleted a folder in your Orchestrator environment? Don’t worry, all is not lost! All deleted objects can still be found in the Orchestrator database.
Note: You will need to know the name of the folder that was deleted.
SQL Query:
Select UniqueID, ParentID, LastModified, Deleted from FOLDERS where Name like 'My deleted folder name'
Recover a deleted folder
To recover a deleted folder, you will need to know the UniqueID of the folder.
By running the previous SQL query to “Find a deleted folder” you will get the UniqueID that you need for your query to recover your deleted folder.
SQL Query:
UPDATE FOLDERS set Deleted = 0 where UniqueID = 'The UniqueID of the deleted folder, you can get it by running the "Find a deleted folder SQL query"'
If the SQL query is ran successfully you should see the (1 row(s) affected).
Note: Remember to click refresh in your Orchestrator console to make the folder reappear.
Find a deleted runbook
If you accidently delete a runbook, you can recover it from the Orchestrator database.
Basically a deleted object gets the value “1” in the database, a non deleted object has the value “0”.
By running the following SQL query you will find all deleted runbooks within a certain period of time:
SQL Query:
select * from POLICIES where Deleted = 1 and LastModified > '2018-01-01 01:00:00.000' and LastModified < '2018-04-29 23:00:00.000'
Recover a deleted Runbook
To recover your deleted runbook(s) you basically change the deleted value in the database to from “1” to “0”.
SQL Query:
UPDATE POLICIES Set Deleted = 0 where Deleted = 1 and LastModified > '2018-01-01 01:00:00.000' and LastModified < '2018-04-28 23:00:00.000'
If the SQL query is ran successfully you should see the (1 row(s) affected).
Note: Remember to click refresh in your Orchestrator console to make the runbook reappear.
