2 Replies Latest reply on Mar 17, 2011 9:49 AM by Karlo.Zatylny

    Custom SQL Variable


      If I write a custom SQL variable for the Advanced Alert Engine to use to populate information from more than one field in the database:


      ... it seems to only report back the first value. 

      ${SQL:select NodeID, IP_Address, DNS from nodes where nodeid like '1' or nodeid like '2'}

      This returns a value of '1'. 

      We are trying to use this to populate information for multiple devices in a cluster when an alert fires one of the device in that cluster. 

      Is this possible?

      Jason Henson
      Loop1 Systems

        • Re: Custom SQL Variable

          It's kinda ugly, but if you put "FOR XML PATH" at the end of the query, it shoves everything into one value with XML formatting in it.

          • Re: Custom SQL Variable

            You could do something like this:

            declare @List NVARCHAR(4000)
            select @List = COALESCE(@List + ', ' + convert(nvarchar(10), nodeid) + ' ' + caption, convert(nvarchar(10), nodeid) + ' ' + caption)  from nodes
            where NodeID = 1 OR NodeID = 2
            select @List

            This returns one value as a string of concatenated text.  Obviously, change the format, where clause, and columns to your needs, but the concept is the same.

            APM does something similar with their built-in SQL Functions:


            You may consider creating your own function or stored procedure to pass in params on the fly.