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