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

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]- 
[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 AttributeOA.OldValueOA.NewValueCIH.DateTime AS 
[Change Timestamp], S.Account AS [UserFROM 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 = 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 
      ,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 (10SIDS.AccountPOLICIES.NameCHECK_IN_HISTORY.CommentCHECK_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.TimeStartedPOLICYINSTANCES.TimeEndedPOLICYINSTANCES.ProcessIDPOLICYINSTANCES.SeqNumberPOLICIES.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.StatusPOLICIES.NamePI.TimeEndedPI.TimeStartedPI.Computer 
FROM POLICIES 
INNER JOIN 
(SELECT PI1.PolicyIDPI1.TimeStartedPI1.TimeEndedPI1.StatusACTIONSERVERS.Computer 
FROM PolicyInstances AS PI1 
INNER JOIN ACTIONSERVERS ON PI1.ActionServer = ACTIONSERVERS.UniqueID 
WHERE PI1.TimeEnded = (SELECT MAX(PI2.TimeEndedFROM PolicyInstances AS PI2 WHERE PI2.PolicyID = PI1.PolicyID) 
) AS PI ON PI.PolicyID = Policies.UniqueID 
WHERE (POLICIES.Deleted = 0AND (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:

  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:

Select lower(POLICIES.UniqueIDas RunbookIDlower(CUSTOM_START_PARAMETERS.UniqueIDas ParameterIDCUSTOM_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 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.

3,010 total views, 10 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.