Orchestrator PowerShell script / SQL query to get last ran jobs

I got asked how to get the last ran jobs in Orchestrator by a SQL query. This is what I came up with.

select * from [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].Jobs AS jobs join POLICIES ON jobs.RunbookId = POLICIES.UniqueID where jobs.CreationTime >= dateadd(hh,-2,getdate())


I then decided to add some PowerShell around the SQL query. This because I wanted a fast access to the result when running PowerShell together with Orchestrator. The reason is that I found it faster than accessing the result by using the Orchestrator Webservice. Ether copy the script below or download it at the following link: GetRunningRunbooks

Remember you will need read access to the Orchestrator database.

# Input strings for the script
$DatabaseServerName = "localhost"
$DatabaseName = "Orchestrator"
$h = 2

try
{
    $Connection = new-object system.data.sqlclient.sqlconnection
    $Connection.ConnectionString ="server=$DatabaseServerName;database=$DatabaseName;trusted_connection=True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlQuery = @"
        select * from [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].Jobs AS jobs join POLICIES ON jobs.RunbookId = POLICIES.UniqueID  where jobs.CreationTime >= dateadd(hh,-$h,getdate())
"@

    $Connection.open()
    $SqlCmd.CommandText = $SqlQuery
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlCmd.Connection = $Connection
    $DataSet = New-Object System.Data.DataSet
    $q = $SqlAdapter.Fill($DataSet)
    [array]$r = $DataSet.Tables[0]
    $DataSet.Tables.Clear()

    ""
    Foreach ($id in $r)
    {
         $id.Name +" - "+ $id.CreationTime
    }
    ""
    $Connection.Close()
}
Catch
{
    Write-host "Connection to database unsuccessful."
    $Connection.Close()
}

Screenshot of the result when running the PowerShell script

Orchestrator PowerShell script SQL query to get last ran jobs1

No comments yet.

Leave a Reply