Documentation forSQL Sentry

System Parameters

Applies To: Execute SQL, Execute Process, and Execute PowerShell actions allow for the passing of parameters in the command text.

System Parameters

There are nine system parameters that are available in SQL Sentry. The following is a list of the system parameters:

System Parameter Description
ServerName* Name of the instance where the object exists.
ObjectName Name of the object.
ObjectType SQL Server Agent Job, Maintenance Plan, etc.
Owner* Owner of the object.
StepName* Name assigned to the step.
Message* Output text associated with the step.
Category* Category as defined in the properties of the object.
Condition Failure, Completed, etc.
MessageText Providing the body text of the standard SentryOne notification.
*Available only for General Actions. These parameters are passed to the command text by surrounding the parameter name with <% %>, like <%ServerName%>.

User Parameters

User defined parameters can also be defined in the output text of an event. The syntax within the output must be: PARAM=12345, PARAM: 12345, or PARAM:12345. Use <%PARAM%> within the command text of the execute action to pass that value. In the following example, we'll demonstrate how user parameters are defined. 

Scenario

Your organization keeps a table on its Issues database called FailedAccountingJobs that has columns for JobOwner, JobName, LastOrderProcessed, and Time. The accounting jobs create a step output that records the last OrderID processed in case of failure, using OrderID as the user defined parameter. Set up an Execute SQL action on the SQL Agent Job: Failure condition that points to the server that holds the Issues database with a T-SQL command:

INSERT INTO Issues..FailedJobs (JobOwner, JobName, LastOrderProcessed, Time)
VALUES (<%Owner%>, <%ObjectName%>,<%OrderID%>, GetDate())

The values for the owner, name of the object, and OrderID are automatically provided by SQL Sentry and inserted into the command when it runs.

Note:  User defined parameters may not be retrievable from the output of retried SQL Agent Job steps. When passing the Message Text parameter, double quotes may be needed. Remember to use SET QUOTED_IDENTIFIER OFF in this case.

Additional Options

Other options are available for the StepName and Message parameters. StepName and Message can also be passed using the format <%StepName:[Identifier]%> or <%Message:[Identifier]%>. If the [Identifier] is a number, then the value from that step ID is used, starting with step one.

Additionally, the [Identifier] can also be one of the following values:

  • FirstStep—The value for the step name or message of the first completed step.
  • LastStep—The value for the step name or message of the last completed step.
  • FirstFailure—The value for the step name or message of the first failed step.
  • LastFailure—The value for the step name or message of the last failed step.

Example Script

INSERT INTO TABLE (MyCustomMessage) VALUES ('The last step failure was on step <%StepName:LastFailure%> and the text for that was <%Message:LastFailure>')