Flatten Multi-Valued Published Data - Part 1

This will probably be a two-part post.

I recently made some enhancements to one of my scripts to take flattened published data and format it so that it's readable (while still flattened).

A little background where this might apply....

If you have many data values that need to be emailed out, it's not likely that you want to send an email for each value.  Ideally, all of the values (filenames, database fields, etc.) would be emailed in a readable format in one email.

I most often use this PowerShell script immediately after a Query Database activity that has published data flattened seperated by "__%__" (without quotes).  The Query Database activity would read multiple rows from a database that meet specified criteria.  This particular example uses the Standard Logging IP by Charles Joy.

The published data passed into the Run .Net Script is the "Full line as string with fields separated by ';'".

Ex. Table Query Results:

Ex. Full line as string.... flattened with __%__ Published Data (not very pretty!)

6/14/2013 3:46:02 PM;3 - Failure;Server4;ActivityName:  Copy File From A to B;ErrorSummary:  Access Denied__%__6/14/2013 3:44:25 PM;3 - Failure;Server3;ActivityName:  Copy File From A to B;ErrorSummary:  File Does Not Exist__%__6/14/2013 3:42:07 PM;4 - Completed;Server2;Copy Completed;__%__6/14/2013 3:41:05 PM;4 - Completed;Server1;Copy Completed;

Posh Script to get the published data:

The Run .Net Script activity also has the published data flattened, but with line breaks.  The table variable would then be sent in the email.

Ex.
6/14/2013 3:46:02 PM
3 - Failure
Server4
ActivityName:  Copy File From A to B
ErrorSummary:  Access Denied

6/14/2013 3:44:25 PM
3 - Failure
Server3
ActivityName:  Copy File From A to B
ErrorSummary:  File Does Not Exist

6/14/2013 3:42:07 PM
4 - Completed
Server2
Copy Completed

6/14/2013 3:41:05 PM
4 - Completed
Server1
Copy Completed


#######################################################################
$table = @()

$RawFailures = @'
<Published Data seperated by __%__>
'@

$Regex = [regex] '/*__%__'
$Failures = $Regex.Split("$RawFailures")
ForEach($Data in $Failures)
    {
    If ($Data.Split(";")[0].Length -ne 0) {
    $Field1 = $Data.Split(";")[0]}
    Else {$Field1 = $null}
    If ($Data.Split(";")[1].Length -ne 0) {
    $Field2 = $Data.Split(";")[1]}
    Else {$Field2 = $null}
    If ($Data.Split(";")[2].Length -ne 0) {
    $Field3= $Data.Split(";")[2]}
    Else {$Field3 = $null}
    If ($Data.Split(";")[3].Length -ne 0) {
    $Field4= $Data.Split(";")[3]}
    Else {$Field4 = $null}
    If ($Data.Split(";")[4].Length -ne 0) {
    $Field5= $Data.Split(";")[4]}
    Else {$Field5 = $null}

    IF ($Data.Length -ne 0) {
    $table += $Field1, $Field2, $Field3, $Field4, $Field5, ""
    }
    }
   
$table = @($table | Where-Object {$_ -ne $null})
#######################################################################

Orchestrator Runbook Migrations - Invoke by path Property

****The following process is not supported by Microsoft!  This directly updates data in the Orchestrator database!  Use at your own risk!****


While migrating policies from Opalis to Orchestrator or between Orchestrator environments, you may run into the issue where Invoke Runbook activities magically get updated w/ the "Invoke by path" property set to True (or checked).

This occurs when a runbook targets another runbook that was not included in the export file.  Orchestrator attempts to keep the relationship chain by specifying the path to the target runbook since the runbook id guid is no longer valid.

To find all active Invoke Runbook activities that have this property set to true, you can execute the following SQL query to identify them.

The first query will group the target runbooks so you know how many are affected (we'll use these individual values later....).


Select TRIGGER_POLICY.PolicyPath AS TargetRunbookPath
From TRIGGER_POLICY
INNER JOIN OBJECTS ON TRIGGER_POLICY.UniqueID = OBJECTS.UniqueID
INNER JOIN POLICIES ON OBJECTS.ParentID = POLICIES.UniqueID
Where TRIGGER_POLICY.TriggerByPolicyPath != 0 and OBJECTS.Deleted != 1 and POLICIES.Deleted != 1
Group By TRIGGER_POLICY.PolicyPath
Order By TRIGGER_POLICY.PolicyPath




Select POLICIES.UniqueID, POLICIES.Name AS SourceRunbook, TRIGGER_POLICY.PolicyObjectID AS TargetRunbookID, TRIGGER_POLICY.PolicyPath AS TargetRunbookPath, TRIGGER_POLICY.TriggerByPolicyPath, TRIGGER_POLICY.TargetActionServers
From TRIGGER_POLICY
INNER JOIN OBJECTS ON TRIGGER_POLICY.UniqueID = OBJECTS.UniqueID
INNER JOIN POLICIES ON OBJECTS.ParentID = POLICIES.UniqueID
Where TRIGGER_POLICY.TriggerByPolicyPath != 0 and OBJECTS.Deleted != 1 and POLICIES.Deleted != 1
Order By TRIGGER_POLICY.PolicyPath


Now if you have a loooot of individual activities that have this property set (shown from the 2nd query above), you can update to update them by target runbook in mass rather than tediously going to each and every activity and updating the target runbook, unchecking the invoke by path property, and updating any parameters if applicable.

From the first query above, you can copy the target runbook name and paste it into the highlighted section below.

**Note the query below will only SELECT the rows that will be ultimately updated.  You'll need to comment out the 'Select' line and remove the comments from the 'Update', and two Set cmds.


Declare @TargetPath varchar(250)
Declare @UpdatedID varchar(250)

Set @TargetPath = 'Policies\Path_To_Runbook'
Set @UpdatedID = (Select '{' + CAST(Resources.UniqueId as varchar(250)) + '}'
    From [Microsoft.SystemCenter.Orchestrator.Internal].Resources AS Resources
    Where Resources.Path = SUBSTRING(@TargetPath,CHARINDEX('\Globals',@TargetPath,0),len(@TargetPath)))


Select POLICIES.UniqueID, POLICIES.Name, @UpdatedID, TRIGGER_POLICY.PolicyPath, TRIGGER_POLICY.TriggerByPolicyPath
--Update TRIGGER_POLICY
--Set TRIGGER_POLICY.PolicyObjectID = @UpdatedID
--    ,TRIGGER_POLICY.TriggerByPolicyPath = 0
From TRIGGER_POLICY
INNER JOIN OBJECTS ON TRIGGER_POLICY.UniqueID = OBJECTS.UniqueID
INNER JOIN POLICIES ON OBJECTS.ParentID = POLICIES.UniqueID
Where TRIGGER_POLICY.TriggerByPolicyPath != 0 and OBJECTS.Deleted != 1 and POLICIES.Deleted != 1 and TRIGGER_POLICY.PolicyPath = @TargetPath

Find Runbook/Parameter GUIDs for Web Service POST

I sometimes find it redundant looking up the runbook and parameter guids when creating a new powershell script to invoke a runbook from the web service.  Here is a SQL query to look up the runbook by name and every parameter that you would need to use the web service to start the runbook.

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 = 'My Runbook Name' and policies.deleted = 0


Note that the query uses the lower() function to force the guids to lowercase.  This is required for the parameters when invoking a runbook or you will receive a 500 Internal Server Error.  The runbook guid doesn't seem to matter if it's upper or lower case, but the parameters NEED to be in lowercase (bug??/feature??).

This will return the PolicyID (RunbookID), Parameter guid, and the name required to start the runbook from the web service.

Prevent Multiple Invocations of a Runbook

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 (highlighted fields 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://
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://
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/&quot;datetime'2012-02-19T19%3A00%3A53.29'&quot;">
<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/&quot;datetime'2012-02-19T03%3A57%3A55'&quot;">
<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.