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

    Custom SQL Variable

    Jason.Henson

      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:

      ${SQL:select...}

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

      Example:
      ${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?


      Thanks,
      Jason Henson
      Loop1 Systems
      www.Loop1Systems.com

        • Re: Custom SQL Variable
          netlogix

          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
            Karlo.Zatylny

            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:

            apm_GetComponentStatusErrorDescription
            apm_GetComponetsForApplication

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