Documentation forSQL Sentry

SQL Sentry Event Chains

Introduction

An event chain is a series of dependencies between events running on the same server or different servers. There is no limit to the length of a chain or the number of events that can be defined for a chain. To navigate to event chains, select the Event Chains node from the Navigator pane.

Add an Event Chain tab with sections to add a name and description.

Item Description
Name Contains the name of the event chain. This also is the value displayed in the tree view under the Event Chains node.
Description Descriptive text for the event chain.
Created by Displays the chain creator and the date/time of chain creation.
Updated by If the chain is updated, displays the updater and date/time of update.
Select Chain Instance Use this drop-down list to select a past runtime for the chain to view its history.
End Time When viewing a historical chain instance, this shows the time the chain completed.
Status Displays the status of the chain being viewed as Ready, Running, or Completed.
Chain Enabled Option to enable or disable the event chain.
Edit To add nodes or make changes to a chain, select this button.
Cancel Cancels adding an event chain. 
Auto refresh Determines how often the chain workspace refreshes to show updated information when the chain is currently running.
Note:  Only the initial job of the event chain needs to be scheduled for the chain to run.

Event Chain Examples

Example A

When Job1 (SQL Server job) completes, execute Job2 (SQL Server job). If Job2 succeeds, execute Task A (Windows Task Scheduler Task). If Job2 fails, execute Job3.

Event Chain example with 4 jobs and 2 tasks. Job 1 completes and job 2 executes. If job 2 succeeds, Task A executes.

To accommodate a path that encompasses the same event more than once, and to avoid a circular reference, the concept of nodes are used. A node is simply a unique representation of an event. Even though Job1 is listed twice, the chain can treat the event as two separate instances and apply varying conditions to each instance that provides a greater degree of flexibility.

Circular References

An event chain is a logical workflow through a series of one or more event objects, with each occurrence of an event object represented by a unique event node. A circular reference occurs if the workflow was built to reference an event node a second time. Such a workflow is not allowed in an event chain.

For example, when Event A completes, execute Event C. When Event C completes, execute Event A.

SQL Sentry Circular reference example. When Event A completes, execute Event C. When Event C completes execute Event A.

To allow an event to occur a second time in an event chain, add the object Event A a second time to the object list. Change the To Node in the second link to reference the newly created node. This method can be repeated to allow the event to occur a discrete number of times.  

SQL Sentry Circular reference A1. To allow an event to occur a second time, Object Event A is added as Event A Node A_1.

Adding an Event Chain

  1. Double-click the Event Chains node in the Navigator pane, or select New in the context menu.
     SQL Sentry Navigator Pane with the Event Chains context menu opened and the New option selected.
  2. Assign the event chain a name and optional description. Add an Event Chain tab with the name Test Event Chain added.
  3. Right-click to add objects that are used in the event chain, or drag-and-drop jobs from the Navigator pane onto the Event Chain diagram. Node names are given to each object.
    Add an Event Chain tab with an added test object that was dragged from the Navigator pane.
  4. Add links to the chain by right-clicking the appropriate node, and then select Workflow. Add an Event Chain tab with 5 added objects and the context menu opened to Workflow Completed for the test object.
  5. Select the status condition for execution and the Target node. Nodes can be dragged and dropped to reposition for easier viewing of the chain. The Auto Layout toolbar button may also be used once links are established.
    Add an Event Chain tab with 5 added objects. Each object has a workflow completed arrow indicating the order they execute.
  6. Select Save in the File menu or select the Save icon SQL Sentry Save toolbar button that displays as a floppy disk. to save the chain.

Editing an Event Chain

  1. Select the Event Chain node from the tree view in the Navigator pane, and then select Edit.
    SQL Sentry Navigator Pane with TestIt event chain selected and the Edit context menu option highlighted.
  2. Select the necessary properties of the event chain. Edit Event Chain tab for TestIt with an Instance selected and the Properties context menu option highlighted.
  3. Select Save in the File menu to save the chain.

Remapping Nodes in an Event Chain

Event Chain nodes can be remapped to other event objects. This can save you time over deleting and recreating nodes in your event chain. When you remap a node in an event chain, the next step(s) in the workflow remain intact. To remap a node, select Remap Node available through the node context menu.

SQL Sentry Edit Event Chain tab with a node selected and the Remap Node context menu option highlighted.

Important:  If any Event Chain nodes or workflow steps are changed, including the remapping of a node, the history for the chain becomes invalid and is lost.

Chain History

To view a historical run of a chain, select the runtime from the Select Chain Instance box. Runtime and status information is displayed for each node. If the object is completed successfully, it has a green background. If it fails, it has a red background. This is also shown for currently executing chains. Currently executing nodes have an orange background.

Event Chain Test Event Chain tab with the Select Chain Instance option highlighted and set to <Current>.

Context Menus

Navigator Pane

SQL Sentry Navigator Pane Event Chain Context menu options.

Item Description
Open Opens the event chain.
Edit Opens the event chain in edit mode.
Start Chain Starts all lead nodes of this event chain.
Stop Tracking Node Failures Stops tracking any node failures. 
Delete Deletes the chain.
Reset Resets a currently running chain. 
Disable Disables the chain. 
Change Folder Allows you to move the specified chain to a different organizational folder.

View Mode

Workspace

Event Chain tab displaying 5 events with connected workflows, and the workspace context menu options highlighted.

Item Description
Refresh Refreshes the current view. Also done by pressing F5.
Print Prints the chain diagram.
Print Preview Previews the printed chain layout.
Zoom > Zooms in or out to view entire chain.

Node - <Current> Chain Instance

Event Chain tab displaying 5 events with connected workflows, and the node context menu options highlighted.

Item Description
Execute Node Allows you to manually run a node in a chain. If the node is a lead node for a chain, then any child nodes are executed upon completion. No child nodes start if the node being executed is not a lead node.
Jump To >
-- This Instance Brings up a calendar displaying the selected node's current instance.
-- Last Failure Brings up a calendar displaying the last failure for the selected node.
-- Last Instance Brings up a calendar displaying the last instance of the selected node.
-- Next Instance Brings up a calendar displaying the next instance of the selected node.
-- Navigator Navigates to the selected node in the Navigator pane.
Show >
-- History >
---- 4 Hours Displays the last four hours of job history for the selected node.
---- 1 Day Displays the last one day of job history for the selected node.
---- 3 Days Displays the last three days of job history for the selected node.
---- 7 Days Displays the last seven days of job history for the selected node.
---- 14 Days Displays the last 14 days of job history for the selected node.
---- 30 Days Displays the last 30 days of job history for the selected node.
-- Runtime Stats Shows the current runtime stats for the selected node.
Properties Views the properties of the object.

Node - Historical Chain Instance

Item Description
Execute Node Allows you to manually run a node in a chain. If the node is a lead node for a chain, then any child nodes is executed upon completion. No child nodes starts if the node being executed is not a lead node.
Jump To>
-- This Instance Brings up a calendar displaying the selected node's current instance.
-- Last Failure Brings up a calendar displaying the last failure for the selected node.
-- Last Instance Brings up a calendar displaying the last instance of the selected node.
-- Next Instance Brings up a calendar displaying the next instance of the selected node.
-- Navigator Navigates to the selected node in the Navigator pane.
Show >
-- History >
---- 4 Hours Displays the last four hours of job history for the selected node.
---- 1 Day Displays the last one day of job history for the selected node.
---- 3 Days Displays the last three days of job history for the selected node.
---- 7 Days Displays the last seven days of job history for the selected node.
---- 14 Days Displays the last 14 days of job history for the selected node
---- 30 Days Displays the last 30 days of job history for the selected node.
-- Runtime stats Shows the current runtime stats for the selected node.
Start Chain Here Allows you to execute a chain from a node other than a lead node. When this option is selected, a new chain instance is created that contains a copy of the nodes status for the instance you are viewing.
Properties Views the properties of the object.

Edit Mode

Workspace

Edit Event Chain tab displaying 6 events with connected workflows, and the workspace context menu options highlighted.

Item Description
Add Nodes Allows you to add new jobs or tasks as nodes in the chain.
Save Saves any changes to the chain.
Print Prints the chain diagram.
Print Preview Previews the printed chain layout.
Auto Layout Organizes and arranges the chain into a default flowchart pattern.
Zoom > Zooms in or out to view the entire chain.

Node

Edit Event Chain tab displaying 6 events with connected workflows, and the node context menu options highlighted.

Item Description 
Workflow Allows you to specify the condition on which one node executes another.
Rename Node Renames the node. This doesn't rename the actual job or task.
Delete Node Deletes the existing node from the chain.
Remap Node Remaps the node. 
Properties Views the properties of the job or task.

Event Chain Notifications

General Actions

Condition Fired When
Event Chain: Started Any lead node for an event chain has completed.
Event Chain: Completed After last node in an event chain has completed.
Event Chain: Success Every node in an event chain runs.
Event Chain: Failure One or more nodes in an event chain don't run.
Event Chain: Runtime Threshold Max The event chain runtime exceeds the Maximum Runtime Threshold settings specified for the event chain.
Event Chain: Runtime Threshold Min The event chain runtime exceeds the Minimum Runtime Threshold settings specified for the event chain.
Event Chain Node: Started The Event Chain node starts.
Event Chain Node: Completed The Event Chain node completes.
Event Chain Node: Success The Event Chain node completes, reporting a success.
Event Chain Node: Failure The Event Chain node completes, reporting a failure.
Event Chain Node: Runtime Threshold Max The Event Chain node exceeds the maximum runtime threshold specified for the node.
Event Chain Node: Runtime Threshold Min The Event Chain node exceeds the minimum runtime threshold specified for the node.

When more than one workflow path exists in a chain, the Chain Success and Chain Failure conditions become less valuable since they are based exclusively on whether or not all nodes completed. At that point the Node Success and Node Failure conditions should be used for more accurate status information about the respective nodes and/or paths.

Settings

Event chains and Event Chain nodes support Runtime Threshold settings. These settings are specified as absolute runtimes for both the minimum and maximum runtime thresholds.

SQL Sentry Settings pane displaying Event Chain Node settings.

Setting Description
Inherit From Parent Specified whether the Runtime Threshold settings are inherited from the parent object.
Maximum Runtime Threshold The maximum allowed runtime for the chain/node.
Minimum Runtime Threshold The minimum allowed runtime for the chain/node.

Audit Actions

Condition Fired When
Event Chain Modified Modifications are made and saved to an event chain.

Event View

SQL Sentry Event Calendar Filter tab displaying varying filter options.

All event chains contain an Event View sub-node. Double-clicking or selecting Open from the context menu produces a calendar of all the objects that are a part of the selected chain. As with any Custom EventView, you have the ability to add Performance Counters to any Event Chain View.

Event Chain Calendar Info

SQL Sentry Event Calendar with an Event Chain selected and the event info pop-up displayed.

All event source objects that appear on a calendar because of an event chain display a small chain glyph in the top left corner.

Additionally, the event source object tooltip displays information about any chain(s) that this object is associated with. An * is placed next to the actual chain that created this instance.

Reporting Services Jobs in Event Chains

It is a known limitation that Reporting Services jobs don't work within chains in versions prior to 2.7. This limitation occurs because Reporting Services jobs are filtered out of SQL Sentry as they have no duration and don't reflect the accurate information about the report.

A workaround is available in version 2.7 and later. Normally, SQL Sentry filters out a Reporting Services job. You can force them to stay in the history and alert chaining that they have completed by placing an additional space after the AddEvent call.

For example, if you currently have:

AddEvent @EventType='Test', @EventData= '5B0537EA-7A87-4C8B-B002-03A51DD5100C'

You could change it to:

AddEvent  @EventType='Test', @EventData= '5B0537EA-7A87-4C8B-B002-03A51DD5100C'

After the change is made, reset the chain.

Important:  If you manually modify the SSRS scheduler job step text as described previously, and at a later date make modifications to the report schedule in any SSRS tool, the manual step text changes you made will be overwritten. It is important to remember to make these modifications to the step text again if you have included an SSRS report as a node in an event chain.

Copy Chain

You may run into a situation where you want to create a second chain that is similar to an existing chain, or you want to use an existing chain as a template for a new chain. You can effectively copy a chain using the Save Chain As option on the File menu.

SQL Sentry File toolbar options with the Save Chain As option highlighted.

You will then be asked to enter a new name and optional description for the chain. 

Note:  To see new event chains created by other client users, right-click on the Event Chains root node, and then select Refresh.

Organizing Event Chains

Event chains can be organized using custom folder names. Any event chain can be moved into another folder by dragging and dropping it into another folder.

SQL Sentry Navigator Pane with the Event Chains context menu options highlighted.