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

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