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

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

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

High Availability for Runbook Servers on Invoking Runbooks

Here's just a quick tidbit for specifying runbook servers used in the Invoke Runbook standard activity.  It’s beneficial to keep the runbook server names stored in Global Variables.  This way you can insert the variable in the Invoke Runbook activity instead of hardcoding the server name.

In case a runbook server has issues and is powered down or unavailable, the invoke runbook activity will automatically start the invoked runbook on the next runbook server in line.  Another benefit is the ability to add/remove runbook servers and just update a variable than trying to find/replace every invoke runbook activity w/ the new server name.

You can also use the variables to group runbook servers based on their role in the environment.  So to load balance runbooks, depending on how many runbook servers you have (for this example we’ll say three internal).  You could specify three variables which have the following values.

Primary:  myrunbookserver1;myrunbookserver2;myrunbookserver3
Secondary:  myrunbookserver2;myrunbookserver3;myrunbookserver1
Tertiary:  myrunbookserver3;myrunbookserver1;myrunbookserver2

So for runbook servers interacting w/ servers on an internal domain you could use:



So for runbook servers interacting w/ servers on a dmz domain you could use:



Finally, subscribe to the variable in the Invoke Runbook activity.