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})
#######################################################################

Schedule Activity: First Business Day of the Month

Maybe this is more straightforward than I originally thought it was....sometimes I tend to over think and complicate things than they actually are.  So just in case someone else finds this helpful.... :)

I recently got a request for a workflow to run on the 1st calendar day of the month or first business day if the 1st fell on a weekend.  I first though that it wasn't possible to do with the native scheduling and PowerShell was needed.  After I wrote up a Posh script to do the heavy lifting, it dawned on me how to use the native schedule activity.  It's not as straight forward as using the script, but does work.

Orchestrator's built-in schedule activity supports two options:
1)  Specifying the calendar day(s) of the month to run

2)  Specifying the week day to run

Here is the "main" runbook for the schedule that will invoke the rest of the workflow.  It requires four different schedule activities to accomplish the logic.  Each of the following links (green) have the logic "confirms to schedule equal to true".

Here is the PowerShell script to accomplish the same schedule result.  The "$Success" variable would be in the Published Data to use in the link logic to continue or not if Success equals true.

$DayOfWeek = [DateTime]::Now.DayOfWeek
$DayOfMonth = [DateTime]::Now.Day

If (  (($dayOfMonth -eq 1) -and ($dayOfWeek -ge [DayOfWeek]::Monday) -and ($dayOfWeek -le [DayOfWeek]::Friday)) -or ((($dayOfMonth -eq 2) -or ($dayOfMonth -eq 3)) -and ($dayOfWeek -eq [DayOfWeek]::Monday))  )
{
    $Success = $true   
}
Else {
    $Success = $false
}

Orchestrator and Excel Automation

I figured I'd throw this out here in case anyone is looking to automate Excel tasks through Orchestrator and PowerShell and running into issues.  I was tasked with modifying an Excel file (removing worksheets and saving as a csv file) in SCOrch as part of a workflow prior to processing the file.

I used the PowerShell script below in the Run .Net Script activity.

Script
#Remove Worksheets
$objExcel = new-object -Com Excel.Application
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
$objExcel.Visible = $false
$objExcel.displayalerts = $false
$objWorkbook = $objExcel.Workbooks.Open("\\PathtoMyFile\MyFile.xlsx")
$objWorksheets = $objWorkbook.Worksheets | Where {($_.Name -ne "Data")}
If ($objWorksheets) {
    ForEach ($objWorksheet in $objWorksheets) {
        $objWorksheet.Delete()
    }
}
$objWorkbook.Save()
$objExcel.Workbooks.Close()
$objExcel.quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objWorkbook)
Remove-Variable objExcel
Remove-Variable objWorkbook

Running this script manually on the runbook server worked fine and as expected.  However, running the script in SCOrch throws the following error (or similar error with opening the file).

Error
Exception calling "Open" with "1" argument(s): "Microsoft Excel cannot access the file '\\PathtoMyFile\MyFile.xlsx'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook."


To solve this issue, create an empty folder called "Desktop" under the systemprofile folder as shown below:

C:\Windows\SysWOW64\config\systemprofile\Desktop

C:\Windows\System32\config\systemprofile\Desktop 

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.