SQL Query in email body for alert

rjackson

Hello Everyone,

I am trying to get our Node is down alert to include all devices with % loss higher than 40, I can get the SQL query right but when I put it into the email body inside the node is down alert I only get the top left data, no other entries. Am I going about this the wrong way?

Select Caption As Device, PercentLoss AS Loss FROM Orion.nodes WHERE PercentLoss >= '40'

mesverrum

Using a SQL variable in the alert message will always only give you the first cell of the results, it is intended to pull a single value not an entire table of data.  I've seen people work around it with various tricks such as making a top 10 list by putting the variable in there 10 times and shifting each one by using tricks like select .... where rownum = 1, rownum = 2, etc

There are also SQL tricks like concatenating values from a set or using for xml path like how they do it in this thread SQL variable definition to return multiple rows