There are nine system parameters that are available in SQL Sentry. The following is a list of the system parameters:
|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.|
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.
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.
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.
INSERT INTO TABLE (MyCustomMessage) VALUES ('The last step failure was on step <%StepName:LastFailure%> and the text for that was <%Message:LastFailure>')