I would like some pointers about using the "Define SQL variable" feature in the Insert Variable window in the Advanced Alerts.
What are some examples of when this could be used? Can anyone provide a few formatting examples?
Thanks.
Bump....Anyone?
Here's a few good examples:
- Rounding of percent values
- Limiting characters in volume name
Stuart
This is for when the built-in variables just aren't cutting it, or the information you need is beyond the event immediately at hand. Say, for example, I'm monitoring a service on a server, and when that service goes down the alert is executing a script to restart it automatically. Now, lets say that in addition to knowing when that service goes down and restarting it automagically, I want to include information from an unrelated SAM monitor such as File Count Monitor because that service is supposed to be processing those files. If I can see the file count monitor number decreasing in the body of the alert message, I know that the service is continuing it's work, even though it's crashing. If it's not, it requires manual intervention.
I can use the SQL variable to collect that data, and deliver it in the service alert message. It works basically like ${SQL:[insert your SQL Query Here]}. Macro's should work in this format, too.
${SQL:SELECT Caption from Nodes where NodeID=${NodeID}}
Here are a couple of examples that I use
${SQL:Select ID From APM_Component Where Name like '${ComponentName}'}
${SQL:Select 'theshhold' = CASE WHEN '${c_threshold}' != '' THEN '${c_threshold}' ELSE '90' END}
So far, I use it for two different things. The first is to give me a status of all of my power supplies in our nexus switches. Note, this query has a union so I can supply a response of NOT PRESENT if the PS is missing.
Power Supply 1: ${SQL:SELECT CustomPollerStatus.Status, CustomPollerAssignment.CustomPollerID FROM CustomPollerStatus INNER JOIN CustomPollerAssignment ON (CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignment.CustomPollerAssignmentID) INNER JOIN CustomPollers ON (CustomPollers.CustomPollerID = CustomPollerAssignment.CustomPollerID) WHERE CustomPollerAssignment.NodeID = ${NodeID} AND CustomPollers.UniqueName = 'N7KPSPS1' UNION SELECT 'N/A',NULL WHERE NOT EXISTS ( SELECT 1 FROM CustomPollerStatus INNER JOIN CustomPollerAssignment ON (CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignment.CustomPollerAssignmentID) INNER JOIN CustomPollers ON (CustomPollers.CustomPollerID = CustomPollerAssignment.CustomPollerID) WHERE CustomPollerAssignment.NodeID = ${NodeID} AND CustomPollers.UniqueName = 'N7KPSPS1' )}
Power Supply 2: ${SQL:SELECT CustomPollerStatus.Status, CustomPollerAssignment.CustomPollerID FROM CustomPollerStatus INNER JOIN CustomPollerAssignment ON (CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignment.CustomPollerAssignmentID) INNER JOIN CustomPollers ON (CustomPollers.CustomPollerID = CustomPollerAssignment.CustomPollerID) WHERE CustomPollerAssignment.NodeID = ${NodeID} AND CustomPollers.UniqueName = 'N7KPSPS2’ UNION SELECT 'N/A',NULL WHERE NOT EXISTS ( SELECT 1 FROM CustomPollerStatus INNER JOIN CustomPollerAssignment ON (CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignment.CustomPollerAssignmentID) INNER JOIN CustomPollers ON (CustomPollers.CustomPollerID = CustomPollerAssignment.CustomPollerID) WHERE CustomPollerAssignment.NodeID = ${NodeID} AND CustomPollers.UniqueName = 'N7KPSPS2’ )}
I also use it to determine the battery status of the UPS for a location whenever a device goes down.
A couple of things to note: Carriage returns break this functionality. Also, if you use this on a custom poller that is not assigned, and you don't do a union like I'm doing above, you will receive the entire sql in your email alert.
