Orchestrator, SCORCH, SQL

Useful SQL queries for System Center Orchestrator

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.


  • 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:

SQL Query:
SELECT P.Name AS [Runbook Name], O.Name AS [Activity Name], OT.Name AS [Activity Type], OA.ActionCASE 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]- 
THEN 'NEW ACTIVITY' ELSE OA.Attribute END AS AttributeOA.OldValueOA.NewValueCIH.DateTime AS 
[Change Timestamp], S.Account AS [UserFROM OBJECT_AUDIT AS OA INNER JOIN 
WHERE (O.Deleted = 0ORDER BY [Change TimestampDESC



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 
  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:

                  POLICIES ON CHECK_IN_HISTORY.ObjectID = POLICIES.UniqueID 



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] 
  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:




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.StatusPOLICIES.NamePI.TimeEndedPI.TimeStartedPI.Computer 
(SELECT PI1.PolicyIDPI1.TimeStartedPI1.TimeEndedPI1.StatusACTIONSERVERS.Computer 
FROM PolicyInstances AS PI1 
WHERE PI1.TimeEnded = (SELECT MAX(PI2.TimeEndedFROM PolicyInstances AS PI2 WHERE PI2.PolicyID = PI1.PolicyID) 
) AS PI ON PI.PolicyID = Policies.UniqueID 



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:

  1. 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.
  2. 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:

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 UniqueIDParentIDLastModifiedDeleted 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s