Useful Orchestrator T-SQL Queries

In this article, I will share some useful T-SQL queries for Orchestrator 2012.


--List all active Runbooks in your environment

SELECT
      [Name]
   
  FROM [Orchestrator].[dbo].[POLICIES]
  where [Deleted] = '0'



--Find Runbook/Parameter GUIDs for Web Service call

Select lower(POLICIES.UniqueID) as RunbookID, lower(CUSTOM_START_PARAMETERS.UniqueID) as ParameterID, CUSTOM_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 = 'CloseIncident' and policies.deleted = 0


--List  Runbooks are currently running

SELECT
J.[RunbookId],
P.[Name],
A.[Computer]
FROM
[Orchestrator].[Microsoft.SystemCenter.Orchestrator.Runtime.Internal].[Jobs] J
INNER JOIN [dbo].POLICIES P ON J.RunbookId = P.UniqueID
INNER JOIN [dbo].[ACTIONSERVERS] A ON J.RunbookServerId = A.UniqueID
WHERE
J.[StatusId] = 1
ORDER BY
P.[Name] DESC

/**
StatusId is current stats of the job.
0 = Pending
1 = Running
2 = Failed
3 = Cancelled
4 = Completed
**/

-- Checked out Runbooks

SELECT [UniqueID]
, [Name]
, [CheckOutUser]
, [CheckOutTime]
, [CheckOutLocation]
FROM [Orchestrator].[dbo].[POLICIES] WHERE [CheckOutUser] IS NOT NULL

--Whatt Jobs are running in SCORCH?

Use Orchestrator
SELECT POLICIES.Name,
COUNT(*)
FROM [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].Jobs INNER JOIN
POLICIES ON [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].Jobs.RunbookId = POLICIES.UniqueID
WHERE ([Microsoft.SystemCenter.Orchestrator.Runtime.Internal].Jobs.StatusId NOT LIKE '4')
AND ([Microsoft.SystemCenter.Orchestrator.Runtime.Internal].Jobs.StatusId NOT LIKE '3')
AND ([Microsoft.SystemCenter.Orchestrator.Runtime.Internal].Jobs.StatusId NOT LIKE '2')
group by POLICIES.Name
order by COUNT(*) DESC


-- Job history for Runbooks

SELECT      js.Name AS Status,  job.CreationTime, pol.Name
                       -- , job.LastModifiedTime
FROM            [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].Jobs AS job INNER JOIN
                         dbo.POLICIES AS pol ON job.RunbookId = pol.UniqueID INNER JOIN
                         [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].JobStatus AS js ON job.StatusId = js.Id
WHERE        (pol.Deleted = 0)

--get variable values

Select *
From VARIABLES