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 - 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

Lookup Where Variables Are Used in Runbooks and Activities

********Updated 8/31
 Added code to exclude searching object/policy instance tables to reduce time the query runs.

 AND (@TableName != '[dbo].[OBJECTINSTANCEDATA]') AND (@TableName != '[dbo].[OBJECTINSTANCES]') AND (@TableName != '[dbo].[POLICYINSTANCES]')
********

I've seen many people (including myself) submit enhancement requests for the product to include some sort of functionality of variable "mapping" to runbooks and activities.

With over  2600 variables in my production database, it can be very time consuming to track down everywhere variables are used and to determine if they can be deleted if no longer used....

Well....after some time of beating my head against a wall w/ SQL, I've finally gotten a working query to find the RunbookPath, RunbookName, ActivityName, and Table.Column (ActivityType.Field) where the variable is used in an Orchestrator instance!!!!

For now, this will find where variables are used.  I'd like to update the query to also be able to find counter and schedule instances as well.  This should also be dynamic to find variables used in all Integration Packs (including OIT).

I found a query to search across all tables and columns for a specific string on this site that got me in the right direction.
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

From there I edited it to also include the other tables/fields to make the data useful in regards to SCOrch.

You would need to edit the highlighted field below with the variable name you would like to search for.  The only caveat is there cannot be any duplicate variable names.  If there are duplicates, a few lines can be edited out.  Then you could set @VarID equal to the guid of the variable you're attempting to lookup.

You can use this query to lookup the guid  by variable name if there are duplicates:

Select VARIABLES.UniqueID
From VARIABLES
INNER JOIN OBJECTS ON OBJECTS.UniqueID = VARIABLES.UniqueID
Where OBJECTS.Name = 'MyVariableName' and OBJECTS.Deleted != 1


Please let me know if you find any bugs or run into any errors w/ the query.  Also, if anyone sees any room for improvement w/ the query (by no means am I an expert DBA :)).

Here is an example of the output from the query.



****Depending on the size of the database, this query may take several minutes to run.

Here is the query used to find variables throughout the database:

--Originally Written by: Narayana Vyas Kondreddi
--Modified By: Jon Mattivi
--Purpose: Search all tables and columns in the Orchestrator database to find variable instances

DECLARE @VarName nvarchar(100), @VarID nvarchar(100)
SET @VarName = 'MyVariableName'

SET @VarID = (Select VARIABLES.UniqueID
From VARIABLES
INNER JOIN OBJECTS ON OBJECTS.UniqueID = VARIABLES.UniqueID
Where OBJECTS.Name = @VarName and OBJECTS.Deleted != 1)

   
CREATE TABLE #Results (RunbookPath nvarchar(1000), RunbookName nvarchar(250), ActivityName nvarchar(250), [Table.Column] nvarchar(370))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @VarID + '%','''')

WHILE @TableName IS NOT NULL
   
BEGIN
    SET @ColumnName = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND (TABLE_SCHEMA) = 'dbo'
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) AND (@TableName != '[dbo].[OBJECTINSTANCEDATA]') AND (@TableName != '[dbo].[OBJECTINSTANCES]') AND (@TableName != '[dbo].[POLICYINSTANCES]') AND ((SELECT TOP 1 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(@TableName, 1) AND (COLUMN_NAME) = 'UniqueID' AND (DATA_TYPE) = 'uniqueidentifier' AND (TABLE_SCHEMA) = 'dbo') is not null)
           
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'datetime', 'decimal', 'int', 'money', 'ntext', 'nvarchar', 'varbinary', 'varchar')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )
        IF @ColumnName IS NOT NULL
           
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'IF EXISTS (Select TOP 1 ' + @TableName + '.' + @ColumnName + 'From ' + @TableName + ' (NOLOCK) WHERE ' + @TableName + '.' + @ColumnName + ' LIKE ' + @SearchStr2 + ')' +
                'BEGIN ' +
                'SELECT Resources2.[Path], Policy.[Name], ActObj.[Name],''' + @TableName + '.' + @ColumnName + '''' +
                'FROM ' + @TableName + ' (NOLOCK) ' +
                'INNER JOIN [dbo].[OBJECTS] ActObj (NOLOCK) ON ' + @TableName + '.[UniqueID] = ActObj.[UniqueID]' +
                'INNER JOIN [dbo].[POLICIES] Policy (NOLOCK) ON ActObj.[ParentID] = Policy.[UniqueID]' +
                'INNER JOIN [Microsoft.SystemCenter.Orchestrator.Internal].[Resources] Resources2 (NOLOCK) ON Policy.[UniqueID] = Resources2.[UniqueId]' +
                'WHERE ' + @TableName + '.' + @ColumnName + ' LIKE ' + @SearchStr2 + 'and ActObj.[Deleted] != 1 ' +
                'END'
            )
        END
    END   
END

SELECT * FROM #Results
Order By RunbookPath
   
DROP TABLE #Results

Run Program Activity issue with Server 2008

****
Update 9/7/2012
The issue lies at the OS level w/ the UAC settings.  The changes reflect the differences from 2008 to 2008 R2....
i.e. 
Run all administrators in Admin Approval mode
Behavior of the elevation prompt for administrators in admin approval mode
****

****
Update 8/30/2012
This appears to only happen when using alternate credentials in the "Run As" field.  I've also repro'd this back in Opalis, so it's not something w/ the updated Run Program activity in SCOrch.  More troubleshooting to come....
****

Yesterday I came across an issue w/ the Run Program standard activity.  I have installed the RU2 hotfix release late in July to resolve the issue running the activity against Server 2003/2008/2008SP2.

The activity does run "successfully" in the console.  However, it does not seem to be running the specified command or program in an elevated UAC session.

Pure Output from running "ipconfig /flushdns" via Run Program on a 2008 server:

The requested operation requires elevation.


While expected behavior on a 2008 R2 server:
Windows IP Configuration

Successfully flushed the DNS Resolver Cache.


 I've also posted the question to the TechNet forums to see if this is a known issue since RU2.
http://social.technet.microsoft.com/Forums/en-US/scogeneral/thread/b03cb650-4abc-44a6-a097-9c1bf4d979a9

Get Child Runbook Success/Failure via Web Service

As external applications begin to incorporate Orchestrator to automate a subset of tasks in a larger workflow (outside of SCOrch), there may be times that the external app needs to check to see if SCOrch is done processing before continuing on.

In this scenario, the external application would use a POST to invoke the SCOrch workflow and then use two GET calls to poll the runbook's status when it finishes to determine if the SCOrch job was successful or failed.

First the SCOrch parent runbook which is invoked by the POST to the web service.




The key is setting the "Wait for completion" in the Invoke Runbook activity.






The Child Runbook is shown here.



The child runbook utilizes return data so the parent knows if it succeeded or failed.



In this case, if the runbook succeeds it returns Success = true and Success = false if it fails on the copy file or run .Net script.

Now that the Orchestrator runbooks are in order, the external application can invoke it via the web service.

First it would send the http POST payload with parameters.
url:
https://scorch.domain:8443/Orchestrator2012/Orchestrator.svc/Jobs/

payload:
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<entry xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
    <content type="application/xml">
        <m:properties>
            <d:Parameters>
                &lt;Data&gt;
                &lt;Parameter&gt;
                    &lt;Name&gt;FileName&lt;/Name&gt;
                    &lt;ID&gt;{3dc82b35-6ec0-45ab-9702-c04499d5a147}&lt;/ID&gt;
                    &lt;Value&gt;MyFile&lt;/Value&gt;
                &lt;/Parameter&gt;
                &lt;/Data&gt;
            </d:Parameters>
            <d:RunbookId m:type="Edm.Guid">71fd19ae-f0fb-499a-8a0b-d66a0e120921</d:RunbookId>
        </m:properties>
    </content>
</entry>

From the POST, the web service sends a response which you need to parse for these lines (specifically the guids):
<d:Id m:type="Edm.Guid">59d84f93-f504-4271-ae11-0a26eedb478c</d:Id>
<d:RunbookId m:type="Edm.Guid">71fd19ae-f0fb-499a-8a0b-d66a0e120921</d:RunbookId>

We need these guids for the first GET call.  This returns the runbook instance id.
url:
https://scorch.domain:8443/Orchestrator2012/Orchestrator.svc/RunbookInstances()?`$filter=RunbookId eq guid'71fd19ae-f0fb-499a-8a0b-d66a0e120921' and JobId eq guid'59d84f93-f504-4271-ae11-0a26eedb478c'&`$select=Id


The response would be parsed to find the guid in this line:
<d:Id m:type="Edm.Guid">c3cf1a99-ac0b-4505-b876-44ae8af9a9d3</d:Id>

The final GET call returns the activity instance data to find the results of the returned data from the child runbook.
https://scorch.domain:8443/Orchestrator2012/Orchestrator.svc/ActivityInstances()?`$expand=Data&`$filter=RunbookInstanceId eq guid'c3cf1a99-ac0b-4505-b876-44ae8af9a9d3'&`$select=Data/Name,Data/Value

This gives a response which we would parse for these lines

<d:Name>Success</d:Name>

<d:Value>true</d:Value>

The value would show whether or not the SCOrch workflow succeeded or failed.