1 Reply Latest reply on Jan 22, 2018 6:46 PM by mesverrum

    SQL Query in email body for alert

    rjackson
    rjackson Jan 22, 2018 12:27 PM

      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'

      • 303 Views
      • Categories: Reports
      • Tags:
        • Re: SQL Query in email body for alert
          mesverrum
          mesverrum Expert Jan 22, 2018 6:46 PM (in response to rjackson)

          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