2 Replies Latest reply on Dec 6, 2010 3:43 PM by netlogix

    Returning SQL within email alert

    cstaniforth

      I am attempting to create an alert based on the status of the battery for a UPS, that when triggered (loss off mains power) lists all nodes that rely on the UPS for power.  I have created a custom property called CORESITE that links the nodes to the UPS.


      I have attempted to use the below code to list all the nodes within the email triggered by the alert. But it is only returning the first record! I need it to return all records that match the SQL criteria. Any ideas how I could achieve this???


       ${SQL: select Caption from Nodes Where CORESITE = '${Node.CORESITE}'}

        • Re: Returning SQL within email alert
          pyro13g

          Here's an Idea.  Although not exactly what you want. Don't see why it wouldn't work.

          Turn your SQL into a report (you can also turn the report into a resource to make a custom view)

          Schedule Report in report Scheduler and pick your email recipients

          Go to your Orion directory and look for Job-xxxxxx files

          Find the one for the Report you Created and Scheduled (you'll need to open them up to look)

          In your alert add Run External Program.  \orion\runorionjob.exe [your job name] you may need the path in the job name.

           

          The report will be emailed shortly after the alert arrives

          • Re: Returning SQL within email alert
            netlogix

            Try:

            ${SQL: select Caption+", " as a from Nodes Where CORESITE = '${Node.CORESITE}' FOR XML PATH}

            May have some silly stuff in it, but should give you a "," separated list.