odata | SCOrch

I’ve moved everything from this blog over to jmattivi.blogspot.com and updated all of the scripts to have straight quotes. If any don’t work as posted, please let me know!

For further posts please see jmattivi.blogspot.com.

Thanks!

There are many occasions where you may want to prevent someone (or schedule) from starting (queuing up) another instance of a Runbook if it’s already running. There are three ways I’ve found to accomplish this….using counters, the database query activity to look up a running job, and the web service.

While using counters may be the best solution to prevent workflows from running before a prerequisite workflow completes, it’s not the best solution to prevent inadvertently queuing up another runbook instance.

The database query activity can be used in this scenario to query the PolicyInstances table to see if a specific runbook is already running.

The query used would look like this. Note you need to use the specific Runbook name in the query.

Select POLICIES.Name,POLICYINSTANCES.Status,POLICYINSTANCES.TimeStarted,POLICYINSTANCES.TimeEnded
From POLICYINSTANCES,POLICIES
Where (POLICIES.UniqueID = POLICYINSTANCES.PolicyID) AND TimeEnded is null AND POLICIES.Name = ‘0.1 Runbook B’

Based on the query results, you can use the Link logic to either start the Runbook or send an email that the Runbook is already running and cannot start at this time.

The .NET Script activity can be used in this scenario to query the Jobs collection of the Web Service to see if a specific runbook is already running.

The Powershell script to query the Jobs collection is a little different than I’ve used in prior posts. Sometimes it’s tedious to look up the RunbookID guid, so this way you can specify the name of the runbook without the guid. This is accomplished by pointing to the Jobs collection, but also expanding the runbook collection as well to grab it’s associated data. Then you can use the filter and select statements in the odata query to filter on the name of the runbook.

Here is the URL for the GET request. Note you need to specify the collection name and property name together for the collection that’s expanded.

$url = “http://scorch.domain:81/Orchestrator2012/Orchestrator.svc/Jobs()?`$expand=Runbook&`$filter=(Status eq ‘Running’) and (Runbook/Name eq ‘0.1 Runbook B’)&`$select=Runbook/Name,Status”

Here is the complete script to query the web service (fields in red would need to be updated per your environment):

#########################################################################################

$user = “domain\username”
$pass = ConvertTo-SecureString “password” -AsPlainText -Force
$creds = New-Object System.Management.Automation.PsCredential($user,$pass)

$url = “http://scorch.domain:81/Orchestrator2012/Orchestrator.svc/Jobs()?`$expand=Runbook&`$filter=(Status eq ‘Running’) and (Runbook/Name eq ‘0.1 Runbook B‘)&`$select=Runbook/Name,Status”
$request = [System.Net.HttpWebRequest]::Create($url)
$request.Credentials = $creds
$request.Timeout = 120000
$request.ContentType = “application/atom+xml,application/xml”
$request.Headers.Add(“DataServiceVersion”, “2.0;NetFx”)
$request.Method = “GET”

$response = $request.GetResponse()
$requestStream = $response.GetResponseStream()
$readStream=new-object System.IO.StreamReader $requestStream
$Output = $readStream.ReadToEnd()
$readStream.Close()
$response.Close()
$Output

#########################################################################################

Here is the output from the request when Runbook B is NOT running:

<?xml version=”1.0″ encoding=”utf-8″ standalone=”yes”?>
<feed xml:base=”https://scorch.domain:81/Orchestrator2012/Orchestrator.svc/” xmlns:d=”http://schemas.microsof
t.com/ado/2007/08/dataservices” xmlns:m=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata” xmlns=”http://
http://www.w3.org/2005/Atom”>
<title type=”text”>Jobs</title>
<id>https://scorch.domain:81/Orchestrator2012/Orchestrator.svc/Jobs</id>
<updated>2012-02-19T18:59:26Z</updated>
<author>
<name />
</author>
<link rel=”self” title=”Jobs” href=”Jobs” />
</feed>

Here is the output when Runbook B is currently running:

<?xml version=”1.0″ encoding=”utf-8″ standalone=”yes”?>
<feed xml:base=”https://scorch.domain:81/Orchestrator2012/Orchestrator.svc/” xmlns:d=”http://schemas.microsof
t.com/ado/2007/08/dataservices” xmlns:m=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata” xmlns=”http://
http://www.w3.org/2005/Atom”>
<title type=”text”>Jobs</title>
<id>https://scorch.domain:81/Orchestrator2012/Orchestrator.svc/Jobs</id>
<updated>2012-02-19T19:01:19Z</updated>
<link rel=”self” title=”Jobs” href=”Jobs” />
<entry m:etag=”W/"datetime’2012-02-19T19%3A00%3A53.29’"”>
<id>https://scorch.domain:81/Orchestrator2012/Orchestrator.svc/Jobs(guid’4f104fcd-d626-421d-9f75-c8c83200
48e0′)</id>
<title type=”text”></title>
<published>2012-02-19T19:00:49-05:00</published>
<updated>2012-02-19T19:00:53-05:00</updated>
<author>
<name />
</author>
<link rel=”edit” title=”Job” href=”Jobs(guid’4f104fcd-d626-421d-9f75-c8c8320048e0′)” />
<link rel=”http://schemas.microsoft.com/ado/2007/08/dataservices/related/Runbook” type=”application/atom+xml;type=e
ntry” title=”Runbook” href=”Jobs(guid’4f104fcd-d626-421d-9f75-c8c8320048e0′)/Runbook”>
<m:inline>
<entry m:etag=”W/"datetime’2012-02-19T03%3A57%3A55’"”>
<id>https://scorch.domain:81/Orchestrator2012/Orchestrator.svc/Runbooks(guid’034d6f78-0529-4111-b31
7-24d75fb42493′)</id>
<title type=”text”>0.1 Runbook B</title>
<published>2012-02-19T03:57:20-05:00</published>
<updated>2012-02-19T03:57:55-05:00</updated>
<author>
<name />
</author>
<link rel=”edit” title=”Runbook” href=”Runbooks(guid’034d6f78-0529-4111-b317-24d75fb42493’)” />
<category term=”Microsoft.SystemCenter.Orchestrator.WebService.Runbook” scheme=”http://schemas.microsoft.com/
ado/2007/08/dataservices/scheme” />
<content type=”application/xml”>
<m:properties>
<d:Name>0.1 Runbook B</d:Name>
</m:properties>
</content>
</entry>
</m:inline>
</link>
<category term=”Microsoft.SystemCenter.Orchestrator.WebService.Job” scheme=”http://schemas.microsoft.com/ado/2007/0
8/dataservices/scheme” />
<content type=”application/xml”>
<m:properties>
<d:Status>Running</d:Status>
</m:properties>
</content>
</entry>
</feed>

So you can see in the output above, you can use link logic to find “<d:Status>Running</d:Status>” in the output from the query to determine if 0.1 Runbook B can be started.

I’ve moved everything from this blog over to jmattivi.blogspot.com and updated all of the scripts to have straight quotes. If any don’t work as posted, please let me know!

For further posts please see jmattivi.blogspot.com.

Thanks!

There’s not a lot I miss about the Opalis Operator Console compared to the new Orchestration Console….however, there was one feature that is not present in the new OC. That is the ability to stop all Runbook Jobs. Whenever there’s maintenance that needs to be done to the runbook servers or database, to prevent creating orphaned jobs, all running/pending jobs need to be stopped.

This is a job for the new web service and powershell! In my previous posts, I showed how to query runbooks that have a status of running or pending and how to stop/cancel a runbook job. This ties them together creating a workflow right in Orchestrator using a combination of the two to stop all running and pending jobs.

The runbook below queries jobs with a status of running and then invokes the runbook which actually stops the job. The query returns the necessary properties of the runbook to pass to the stopping job – jobID, RunbookID, and LastModifiedTime. I have another similar runbook that runs in parallel that queries for pending jobs and calls the same stopping runbook. These runbooks will loop until the total running/pending jobs = 0.

Be sure to exclude the runbooks that are running this workflow from the query or this runbook will stop itself :). Here is an example of the url in the query script to exclude the runbookIDs of the runbooks which run the workflow.

$url = “https://scorch.domain:81/Orchestrator2012/Orchestrator.svc/Jobs()?`$filter=Status eq ‘Running’ and RunbookId ne guid’10000000-0000-0000-0000-000000000000′ and RunbookId ne guid’20000000-0000-0000-0000-000000000000′ and RunbookId ne guid’30000000-0000-0000-0000-000000000000′&`$select=Id,RunbookId,LastModifiedTime,Status”

Figure A

Figure B

In Figure A, I reset a counter and then run the powershell script to query for running jobs. For every running job it finds, it then increments a counter and invokes the stop jobs runbook. I also decrement the same counter in Figure B so that we know every runbook found was processed successfully. After the junction, we check to make sure the counter was decremented back to 0. Then we query for any running jobs again and publish that back to the main runbook that will loop until that is 0.

In Figure B, I implement the Standard Logging IP by Charles Joy to log the stopping script. This will allow parsing the table for failures and reporting at the end. As mentioned above, on success or failure, I decrement the counter again to ensure the running job was processed.

Now with one click to start a runbook, you can stop all runbook jobs again! I had to come up with another workflow to automate starting all necessary jobs again that run with a Monitor Date/Time activity from a Sharepoint list….but that’ll be saved for the next post.

I’ve moved everything from this blog over to jmattivi.blogspot.com and updated all of the scripts to have straight quotes. If any don’t work as posted, please let me know!

For further posts please see jmattivi.blogspot.com.

Thanks!

This post I’ll show how to get all Running Runbook jobs.

I used the ConvertTo-SCOObject function Robert Hearn posted here to parse the xml response from the GET call.

#########################################################################

function ConvertTo-SCOObject {
[CmdletBinding()]
PARAM (
[Parameter(Mandatory=$true)]
[String] $XmlFeed
)
$xml =[xml]$XmlFeed

$ns = New-Object Xml.XmlNamespaceManager $xml.NameTable
$ns.AddNamespace( “d”, “http://schemas.microsoft.com/ado/2007/08/dataservices”)
$ns.AddNamespace( “m”, “http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”)
$ns.AddNamespace( “def”, “http://www.w3.org/2005/Atom”)

$objarray = @()

$entryNodes = $xml.SelectNodes(“//def:entry”,$ns)
foreach ($entryNode in $entryNodes)
{
$obj = New-Object PSObject
$objectType = $entryNode.SelectSingleNode(“.//def:category”, $ns).GetAttribute(“term”)

$obj | add-member Noteproperty -Name “Object Type” -Value $objectType.Split(“.”)[-1]

$propertiesNode = $entryNode.SelectSingleNode(“.//m:properties”, $ns)
foreach($propertyNode in $propertiesNode.ChildNodes)
{
$obj | add-member Noteproperty -Name $propertyNode.LocalName -Value $propertyNode.get_InnerText()
}

$links = $entryNode.SelectNodes(“.//def:link”, $ns)
foreach ($link in $links)
{
$obj | add-member NoteProperty -Name $link.title -Value $link.href
}
$objarray += $obj
}

return $objarray

#########################################################################

Due to the paging limits on the web service collections (Jobs being 500), I used this method for filtering the results. The most important part of this GET is building the query into the odata URI. You can build in filters, select statements, etc. on the Web Service collections shown like this.

http://scorch.domain:81/Orchestrator2012/Orchestrator.svc/Jobs()?$filter=Status eq 'Running'
\_______________________________________________________/ \____/  \_________________________/
                           |                                |                   |
                    service root URI                  resource path        query options

Putting this into practice w/ SCOrch, you can query the Jobs collection, filter for Jobs that have a Status of “Running”, and select the fields you would like to return – Id,RunbookId,LastModifiedTime,Status.

“http://scorch.domain:81/Orchestrator2012/Orchestrator.svc/Jobs()?`$filter=Status eq ‘Running’&`$select=Id,RunbookId,LastModifiedTime,Status”

So you can use this on any of the collections to filter results i.e. Jobs, Runbooks, RunbookInstances, Statistics, etc.

The GET request would look like this.

$creds = Get-Credential(“domain\username”)

$XmlFeed = $null
$count = 0
$url = “http://scorch.domain:81/Orchestrator2012/Orchestrator.svc/Jobs()?`$filter=Status eq ‘Running’&`$select=Id,RunbookId,LastModifiedTime,Status”
$request = [System.Net.HttpWebRequest]::Create($url)
$request.Credentials = $creds
$request.Timeout = 60000
$request.ContentType = “application/atom+xml,application/xml”
$request.Method = “GET”

$response = $request.GetResponse()
$requestStream = $response.GetResponseStream()
$readStream=new-object System.IO.StreamReader $requestStream
$XmlFeed = $readStream.ReadToEnd()
$readStream.Close()
$response.Close()

Using the ConvertTo-SCOObject function, you can then parse the response and set your variables and get a count of the running runbooks.

ConvertTo-SCOObject -XmlFeed $XmlFeed | %{$count++}
$Id = ConvertTo-SCOObject -XmlFeed $XmlFeed | %{$_.Id}
$RunbookId = ConvertTo-SCOObject -XmlFeed $XmlFeed | %{$_.RunbookId}
$LastModifiedTime = ConvertTo-SCOObject -XmlFeed $XmlFeed | %{$_.LastModifiedTime}
$Status = ConvertTo-SCOObject -XmlFeed $XmlFeed | %{$_.Status}

Here is the complete script for getting all Running Runbook Jobs. Fields in red would need to be updated per your environment.

##########################################################################################################
function ConvertTo-SCOObject {
[CmdletBinding()]
PARAM (
[Parameter(Mandatory=$true)]
[String] $XmlFeed
)
$xml =[xml]$XmlFeed

$ns = New-Object Xml.XmlNamespaceManager $xml.NameTable
$ns.AddNamespace( “d”, “http://schemas.microsoft.com/ado/2007/08/dataservices”)
$ns.AddNamespace( “m”, “http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”)
$ns.AddNamespace( “def”, “http://www.w3.org/2005/Atom”)

$objarray = @()

$entryNodes = $xml.SelectNodes(“//def:entry”,$ns)
foreach ($entryNode in $entryNodes)
{
$obj = New-Object PSObject
$objectType = $entryNode.SelectSingleNode(“.//def:category”, $ns).GetAttribute(“term”)

$obj | add-member Noteproperty -Name “Object Type” -Value $objectType.Split(“.”)[-1]

$propertiesNode = $entryNode.SelectSingleNode(“.//m:properties”, $ns)
foreach($propertyNode in $propertiesNode.ChildNodes)
{
$obj | add-member Noteproperty -Name $propertyNode.LocalName -Value $propertyNode.get_InnerText()
}

$links = $entryNode.SelectNodes(“.//def:link”, $ns)
foreach ($link in $links)
{
$obj | add-member NoteProperty -Name $link.title -Value $link.href
}
$objarray += $obj
}

return $objarray
}

$creds = Get-Credential(“domain\username“)

$XmlFeed = $null
$count = 0
$url = “http://scorch.domain:81/Orchestrator2012/Orchestrator.svc/Jobs()?`$filter=Status eq ‘Running’&`$select=Id,RunbookId,LastModifiedTime,Status”
$request = [System.Net.HttpWebRequest]::Create($url)
$request.Credentials = $creds
$request.Timeout = 60000
$request.ContentType = “application/atom+xml,application/xml”
$request.Method = “GET”

$response = $request.GetResponse()
$requestStream = $response.GetResponseStream()
$readStream=new-object System.IO.StreamReader $requestStream
$XmlFeed = $readStream.ReadToEnd()
$readStream.Close()
$response.Close()

ConvertTo-SCOObject -XmlFeed $XmlFeed | %{$count++}
$Id = ConvertTo-SCOObject -XmlFeed $XmlFeed | %{$_.Id}
$RunbookId = ConvertTo-SCOObject -XmlFeed $XmlFeed | %{$_.RunbookId}
$LastModifiedTime = ConvertTo-SCOObject -XmlFeed $XmlFeed | %{$_.LastModifiedTime}
$Status = ConvertTo-SCOObject -XmlFeed $XmlFeed | %{$_.Status}
##########################################################################################################

A few good resources for working with the Orchestrator Web Service.

http://blogs.technet.com/b/scorch/archive/2011/06/17/fun-with-the-orchestrator-2012-beta-the-web-service-and-powershell.aspx

http://www.odata.org/developers/protocols/uri-conventions

SCOrch Everything System Center Orchestrator

Archive for the ‘odata’ Tag

Prevent Multiple Invocations of a Runbook Leave a comment

Workflow to Stop all Runbook Jobs Leave a comment

Get all Running Runbook Jobs Leave a comment

Recent Posts

Categories

Archives

Visitors