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