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 2012 Web Service Request Issue

I've started running into an issue w/ external requests to start a runbook via the web service.  I'm interested to see if others are experiencing the same issue.

It seems sporadically that requests coming in (w/ or w/o parameters) will fail and the web service will return a 405 (Method Not Allowed) status code with the response.  Trying the same request again moments later would succeed.

Turning on Failed Request Tracing (FRT) in IIS for the "Microsoft System Center 2012 Orchestrator Web Service" will reveal more details w/ the error. 

From the site highlighted in IIS, you can enable FRT from the Actions pane.  After FRT is enabled, you can create the rule to capture specific status codes (400,404,405,500 in the example below).







After the issue occurs and the request fails w/ a code of 405, a log file will be generated in the following folder if you accepted the default path - C:\inetpub\logs\FailedReqLogFiles\W3SVC2.  Towards the bottom of the log file, you'll find this error information:

<Data Name="Buffer">&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot; standalone=&quot;yes&quot;?&gt;
&lt;error xmlns=&quot;http://schemas.microsoft.com/ado/2007/08/dataservices/metadata&quot;&gt;
  &lt;code&gt;&lt;/code&gt;
  &lt;message xml:lang=&quot;en-US&quot;&gt;The requested operation requires Publish permissions on the Runbook&lt;/message&gt;
&lt;/error&gt;</Data>

This appears to be a bogus error since sending the same request again will succeed using the same credentials.

After a little more digging, the error occurring seems to correlate w/ the ClearAuthorizationCache maintenance task in the SQL database.  Since this task computes/populates the folders, runbooks, and permissions....it theoretically make sense that while that task is running, incoming requests would fail since it thinks the user does not have permissions to the runbook (when it does!).  This could technically also affect requests being sent through the Orchestration Console to stop/start jobs.  By default, this task runs every 600 seconds (10 minutes).  I'm not sure of the duration it takes for the ClearAuthorizationCache task to complete, but it would depend on how much data there is to process (# of runbooks, folders, etc. in the environment).


If you have seen or do experience similar issues, I'd appreciate if you left a comment.  I also suggest opening a case w/ Microsoft to determine if this is a general product issue.

Orchestrator 2012 - Daylight Savings Time Scheduling Issue

I'm running a little behind on this post as Daylight Savings Time here in the US was back on Sunday, November 4.  For ease of example, all dates/times will reference the schedule for Daylight Savings in the United States.

There is an issue with scheduling Runbooks that start with the Monitor Date/Time activity during DST.

Daylight Saving Time (United States) 2012 began at 2:00 AM on Sunday, March 11.  At this time, 
the clocks would be moved ahead one hour to 3:00 AM.

• This means any Runbooks scheduled to run between the hours of  2:00 AM - 3:00 AM would be skipped entirely.

Daylight Saving Time (United States) 2012 ended at 2:00 AM on Sunday, November 4.  At this time, 
the clocks would be moved back one hour to 1:00 AM.

• This means any Runbooks schedule to run between the hours of 1:00 AM - 2:00 AM would run twice.

Depending on what the runbooks are executing, this could cause major issues and/or unscheduled downtime. It would also suggest Runbooks that are not set on an hourly interval (i.e. once daily) be scheduled outside of the hours of 1:00 AM - 3:00 AM to avoid any interruptions.  Unless you throw an exception into the schedule to work around the timing that the clocks are changed.

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

Run .NET Script: Catching PowerShell Output Into the Current Session

Here is a handy tip for catching PowerShell output into a variable as Published Data.

There are often times you need to execute a cmd within PowerShell that creates output in the cmd's output, but not PowerShell.

I came across a similar issue on the technet forums.
http://social.technet.microsoft.com/Forums/en-US/scogeneral/thread/a9511617-c174-4a5b-a531-3110471c9222

For example....

Running w/ PowerShell in the Run .NET Script activity cannot catch the output of the winrs cmd.  Even w/ adding the $Output = ... and adding the Output variable to published data.

$Output = winrs -r:server_name -u:server_name\administrator -p password net localgroup Administrators Domain\User /ADD

This will result in the Output variable in the published data being empty.

The trick is simply adding "2>&1" (without quotes) at the end of the cmd.  So the complete command would look like this.

 $Output = winrs -r:server_name -u:server_name\administrator -p password net localgroup Administrators Domain\User /ADD 2>&1

This will result in the $Output variable catching the result from winrs into the published data.  Also note, you may have to Flatten the data since the result may end up in multiple lines.




Here is a link that explains different methods for catching output in PowerShell.
http://mctexpert.blogspot.com/2010/11/what-does-2-mean-in-powershell.html

SCOrch FTP Integration Pack Update v1.3

I've just released v1.3 of the Orchestrator IP for FTP/SFTP.  You can find it on codeplex at this link:

https://scorchestrator.codeplex.com/releases/view/94807

Please install this in a test environment before deploying to production!  Also, please comment on any issues/bugs you may encounter.

********
Since adding the extra "UsePassive" and "UseBinary" input fields, please note the following steps required for existing installs upgraded to v1.3.  These fields must be commited to the database in the existing activity before they will work.

1)  Stop runbooks where the activities are located.
2)  Check out the runbook
3)  Open the activity and click "Finish" (no changes required)
4)  Check in the runbook
(Thanks to Jeff for the insight for commiting the added fields to existing activities!)
********

Activity Change Log:

 

Create Folder

Resolve issue where host key is not saved and cmd to change directory fails

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

Delete File

Resolve issue where host key is not saved and cmd to change directory fails

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

Delete Folder

Resolve issue where host key is not saved and cmd to change directory fails

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

Download File

Resolve issue where host key is not saved and cmd to change directory fails

Add option for deleting source file(s)

Add option for bin/ascii transfer

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

List Folder

Resolve issue where host key is not saved and cmd to change directory fails

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

Rename File

Resolve issue where host key is not saved and cmd to change directory fails

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

Upload File

Resolve issue where host key is not saved and cmd to change directory fails

Add option for deleting source file(s)

Add option for bin/ascii transfer

Add option for passive/active connection

Allow Paths/Filenames with spaces

Adjust FTP Timeout

Dir Change Error Handling

Empty File/0 byte Error Handling

Audit Jobs started from Orchestration Console (Who Started It)

This post I'll explain how to setup a runbook that runs a daily audit report of runbook jobs started from the Orchestration Console (OC).  As more end users and external applications get access to start runbooks, it's important to know who started a runbook at any given time.

The runbook itself is fairly simple.







First, we need to query the database for the jobs table ([Microsoft.SystemCenter.Orchestrator.Runtime].Jobs) which holds most of the data for the jobs.  One key to both queries is to flatten the data which we will later break back out into multi-valued data.




Select POLICIES.Name, Jobs.CreatedBy, Jobs.CreationTime, Jobs.Status
From [Microsoft.SystemCenter.Orchestrator.Runtime].Jobs AS Jobs
INNER JOIN POLICIES ON Jobs.RunbookId = POLICIES.UniqueID
Where Jobs.CreatedBy != 'S-1-5-500' and datediff(HOUR, Jobs.CreationTime, SYSUTCDATETIME ()) <= 24
Order By Jobs.CreationTime Desc


You can also extract parameters, runbook servers specified, etc... from the query if you like.  I joined the POLICIES table onto Jobs to get the name of the runbook from the guid.  Since this is a daily report, I selected jobs created in the past 24 hours.  I've also filtered out jobs started by the SID - "S-1-5-500".  This will narrow our results down to the OC.  Jobs started from the designer console or "internally" to SCOrch will have that SID.  Jobs created from the OC will show the SID of the user that started the job.

Next, we run almost the same query just to get the SIDs of the users we need to translate.


Select Jobs.CreatedBy
From [Microsoft.SystemCenter.Orchestrator.Runtime].Jobs AS Jobs
Where Jobs.CreatedBy != 'S-1-5-500' and datediff(HOUR, Jobs.CreationTime, SYSUTCDATETIME ()) <= 24
Group By Jobs.CreatedBy


Now to the script that puts it all together.  You'll notice from this screenshot where I've used the published data to parse.





Here is the script (I've left out the published data alphabet soup since it would be translated to the guid).

$RawResults = ""

$MyObjectCol = @()

$Regex = [regex] '/*__%__'
$Results = $Regex.Split("$RawResults")
ForEach($Data in $Results)
    {
    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}

    $MyObject = new-object psobject
    $MyObject | add-member -MemberType noteproperty -name RunbookName -value $Field1
    $MyObject | add-member -MemberType noteproperty -name InvokedBy -value $Field2
    $MyObject | add-member -MemberType noteproperty -name Date -value $Field3
    $MyObject | add-member -MemberType noteproperty -name JobStatus -value $Field4
    $MyObjectCol += $MyObject
    }

#Remove Empty Objects
$MyObjectCol = @($MyObjectCol | Where-Object {$_.RunbookName -ne $null})

#Convert SID to Username
$Users = @()

$SIDs = ""
$SIDs = $SIDs.Split("__%__")
ForEach ($SID in $SIDs) {
    If ($SID.Length -ne 0) {
        $objSID = New-Object System.Security.Principal.SecurityIdentifier ($SID)
        $objUser = $objSID.Translate( [System.Security.Principal.NTAccount])
        $User = $objUser.Value
        ForEach ($Object in $MyObjectCol) {
             $Object.InvokedBy = $Object.InvokedBy -Replace("$SID","$User")
        }
    }
}

#Convert UTC to Local Time
$strCurrentTimeZone = (Get-WmiObject win32_timezone).StandardName
$TZ = [System.TimeZoneInfo]::FindSystemTimeZoneById($strCurrentTimeZone)

ForEach ($Object in $MyObjectCol) {
     $Object.Date = [System.TimeZoneInfo]::ConvertTimeFromUtc($Object.Date, $TZ)
}

$MyObjectCol = $MyObjectCol | Sort Date -Desc | ft -AutoSize | Out-String -Width 4096 -Stream | %{ $_.Trim() }
$MyObjectCol | Out-File -FilePath "C:\Temp\JobAudit.txt"


I create a hash table to save the end results in.  The script converts the SIDs to usernames and also converts the time from UTC to local.  It then saves the output to a txt file which we'll email as an attachment.  I chose to do it this way so the data would be saved in columns and formatted cleanly to read.  You could also format it as a list to put directly into the body of the email.




Finally, just for housekeeping, the txt file is deleted.