SQL Queries for Orchestrator

I have written some useful SQL queries which will help to get data from Orchestrator database.

Query 1:

The following query will help us to get the job details for a Runbook.

Select RB.Name, RB.Path, RT.Id, RT.RunbookServerId, A.Computer , RT.Status, RT.Parameters, RT.LastModifiedTime, RT.LastModifiedBy from 

[Microsoft.SystemCenter.Orchestrator.Runtime].[Jobs] as RT

inner join [Orchestrator].[Microsoft.SystemCenter.Orchestrator].[Runbooks] RB on RB.Id= RT.RunbookId 

inner join ACTIONSERVERS A on A.UniqueID = RT.RunbookServerId

order by RT.CreationTime desc

Query 2:

To get the Runbook servers details, we need to run the below queries.

Select * from ACTIONSERVERS

Select * FROM [Orchestrator].[dbo].[CLIENTCONNECTIONS]

Query 3:

To Delete a Runbook server from Orchestrator database, we need to run the below query.

DELETE FROM [Orchestrator].[dbo].[CLIENTCONNECTIONS]

WHERE [ClientMachine] = 'SCORCHRB'

-- Here SCORCHRB is my Runbook Server Name.

DELETE  FROM [Orchestrator].[dbo].[ACTIONSERVERS]

  WHERE [Computer] = 'SCORCHRBServer'

-- Here SCORCHRBServer is my Runbook Server Name.

DELETE FROM dbo.OBJECTS

where Name = ‘SCORCHRBServer’

-- Here SCORCHRBServer is my Runbook Server Name.

Query 4:

Query to get Variables.

Select Ob.Name, V.Value from VARIABLES V
inner join OBJECTS Ob on Ob.UniqueID = V.UniqueID

Query 5:

Query to get Counters.

Select Ob.Name, C.DefaultValue from COUNTERS C
inner join OBJECTS Ob on Ob.UniqueID = C.UniqueID

Query 6:

Query to get Schedules.

/****** Script for SelectTopNRows command from SSMS ******/
SELECT Ob.Name, [DaysOfWeek] ,[DaysOfMonth],[Monday] ,[Tuesday] ,[Wednesday] ,[Thursday] ,[Friday] ,[Saturday] ,[Sunday] ,[First] ,[Second] ,[Third] ,[Fourth]
,[Last] ,[Days] ,[Hours] FROM SCHEDULES S
Inner join OBJECTS Ob on Ob.UniqueID = S.UniqueID

Query 7:

Select Version,
CASE
When Version = '7.2.84.0' THEN 'SCORCH 2012 R2 RTM'
When Version = '7.2.114.0' THEN 'SCORCH 2012 R2 UR2' 
When Version = '7.2.1665.0' THEN 'SCORCH 2012 R2 UR3' 
When Version = '7.2.171.0' THEN 'SCORCH 2012 R2 UR7' 
When Version = '7.2.200.0' THEN 'SCORCH 2012 R2 UR8' 
When Version = '7.2.239.0' THEN 'SCORCH 2012 R2 UR14' 
When Version = '7.3.149.0' THEN 'SCORCH 2016 RTM'
When Version = '7.3.164.0' THEN 'SCORCH 2016 UR1' 
When Version = '7.3.185.0' THEN 'SCORCH 2016 UR4' 
When Version = '7.3.273.0' THEN 'SCORCH 2016 UR5' 
When Version = '7.3.285.0' THEN 'SCORCH 2016 UR6' 
When Version = '7.3.310.0' THEN 'SCORCH 2016 UR7' 
When Version = '7.3.327.0' THEN 'SCORCH 2016 UR8' 
When Version = '10.19.40.0' THEN 'SCORCH 2019 RTM' 
When Version = '10.19.100.0' THEN 'SCORCH 2019 UR1' 
When Version = '10.19.119.0' THEN 'SCORCH 2019 UR2' 
When Version = '10.19.126.0' THEN 'SCORCH 2019 UR3' 
When Version = '10.22.2.0' THEN 'SCORCH 2022 RTM'
END
From ACTIONSERVERS