Orchestrator SQL Database Queries
I wanted to share some Orchestrator SQL queries that might be of use to you.
Sometimes we need to audit or troubleshoot a runbook and doing it straight from the database is an easy way.
1. See all changes in Runbooks
This SQL query will list the following:
- The runbook name.
- The runbook activity name.
- The runbook activity type.
- The action.
- The attribute.
- The old value of the activity.
- The new value of the activity.
- The change timestamps.
- The AD account name of the user who made the modification.
SQL
Edit|Remove
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
2. See all checked out Runbooks
This SQL query will list the following:
- The runbook name.
- The check out time of the runbook.
- The check out location.
- The description.
- The AD account name of the user that checked out the runbook.
SQL
Edit|Remove
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
3. See all checked in Runbooks
This SQL query will list the following:
- The AD account name of the user that checked in the runbook.
- The runbook name.
- The runbook check in comment.
- The time and date.
SQL
Edit|Remove
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
4. See all client connections
This SQL query will list the following:
- The AD account name of the user.
- The runbook name.
- The time and date.
SQL
Edit|Remove
SELECT TOP 10 [UniqueID]
,[ManagementServer]
,[ClientMachine]
,[ClientUser]
,[ClientVersion]
,[ConnectionTime]
,[LastActivity]
FROM [Orchestrator].[dbo].[CLIENTCONNECTIONS]
5. Find the PID of a specific Runbook
This SQL query will list the following:
- The time when the runbook started.
- The time when the runbook finished.
- THe process ID of the runbook.
- The runbook name.
SQL
Edit|Remove
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
6. Check the Runbook job status
This SQL query will list the following:
- The runbook status.
- The runbook name.
- The time when the runbook finished.
- The time when the runbook started.
- Which runbook server did the runbook run on.
SQL
Edit|Remove
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)
7. Get the Runbook GUID
This SQL query will list the following:
- The runbook ID.
SQL
Edit|Remove
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
8. Find a deleted folder
This SQL query will list the following:
- The uniqueID of the folder.
- The parentID of the folder.
- The last modified timestamp.
- The deleted value (1=deleted, 0= not deleted).
SQL
Edit|Remove
Select UniqueID, ParentID, LastModified, Deleted from FOLDERS where Name like 'My deleted folder name'
9. Recover a deleted folder
If the SQL query is ran successfully we should see the (1 row(s) affected).
Remember to click refresh in your Orchestrator console to make the folder reappear.
SQL
Edit|Remove
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"'
10. Find a deleted Runbook
This SQL query will list the following:
- The uniqueID of the runbook.
- The runbook name.
- The parentID of the runbook.
- The runbook creation time.
- The runbook creator.
- Last modified time and date.
- Last modified by SID.
- The deleted value (1=deleted, 0= not deleted).
SQL
Edit|Remove
select * from POLICIES where Deleted = 1 and LastModified > '2018-01-01 01:00:00.000' and LastModified < '2018-04-28 23:00:00.000'
11. Recover a deleted Runbook
If the SQL query is ran successfully we should see the (1 row(s) affected).
Remember to click refresh in our Orchestrator console to make the runbook reappear.
SQL
Edit|Remove
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'