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] = 'SCORCHRB'

-- Here SCORCHRB is my Runbook Server Name.

DELETE FROM dbo.OBJECTS

where Name = ‘SCORCHRB’

-- Here SCORCHRB 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

 

Leave a Reply

Your email address will not be published. Required fields are marked *