7 Replies Latest reply on Mar 2, 2010 3:39 PM by viol8tor

    How to eMail results of an SQL Query

    TXNOGAS

      NPM 9.5.1

      I am trying to get the following SQL query results into an Advance Alert Triggered eMail:

      First of all I can't get this thing working from the Database Manager, running it directly on the DB itself???

      I keep recieving the an error "Incorrect syntax near '}'.

      I got everything working except the SQL Query (see bottom).

      Any help would be greatly appreciated.

      Thanks in advance,

      eMail Example:

       

        

      SQL Query:

      Select TriggerTimeOffset / 60 From AlertStatus Where AlertDefID = (Select AlertDefID From AlertDefinitions Where AlertName = '${AlertName}') AND ActiveObject = '${NodeID}'}

      Subject: SolarWinds_NPM_ALERT: ${NodeName} is ${Node.Status}

      Message:

      AlertName: ${RuleName}

      Description: ${NodeName} is ${Node.Status}

      IP Address: ${Node.IP_Address}

      Time of Event: ${TimeStamp}

        • Re: How to eMail results of an SQL Query
          viol8tor

          Are you wrapping your SQL query with ${SQL:...}?

          • Re: How to eMail results of an SQL Query
            viol8tor

            No, this won't run in the DB manager.  Anything that is ${} is a variable being passed to SQL by the alerting system.  You would have to actually substitute real values to test within DB Manager.

             

            -v

            • Re: How to eMail results of an SQL Query
              TXNOGAS

              This is what I receiving from the eMail:

               

               

               

              Actual eMail Received:

              AlertName: .TESTING_DOWN_UP

              Description: XREMOTE2gw is Up

              IP Address:  10.183.0.1

              Node Type:  Cisco 871

              Time of Event: 3/2/2010 1:52:53 PM

              Status: Up

              Severity: Low

              Duration: Select TriggerTimeOffset / 60 From AlertStatus Where AlertDefID = (Select AlertDefID From AlertDefinitions Where AlertName = '.TESTING_DOWN_UP') AND ActiveObject = '6372'}

              • Re: How to eMail results of an SQL Query
                TXNOGAS

                eMail Message Setup:

                Description: ${NodeName} is ${Status}
                IP Address:  ${Node.IP_Address}
                Node Type:  ${Node.MachineType}
                Time of Event: ${Date} ${LongTime}
                Status: ${Status}
                Severity: Low
                Duration: Select TriggerTimeOffset / 60 From AlertStatus Where AlertDefID = (Select AlertDefID From AlertDefinitions Where AlertName = '${AlertName}') AND ActiveObject = '${NodeID}'}

                • Re: How to eMail results of an SQL Query
                  TXNOGAS

                  Now I got the following error????

                  -----Original Message-----
                  From: SolarWinds_NPM_RESET_ALERT [mailto:]
                  Sent: Tuesday, March 02, 2010 2:53 PM
                  To:
                  Subject: SolarWinds_NPM_RESET_ALERT: XREMOTE2gw is Up

                   

                  AlertName: .TESTING_DOWN_UP

                  Description: XREMOTE2gw is Up

                  IP Address:  10.183.0.1

                  Node Type:  Cisco 871

                  Time of Event: 3/2/2010 2:53:03 PM

                  Status: Up

                  Severity: Low

                  DownTime Duration: MACRO SQL ERROR - The multi-part identifier ".TESTING_DOWN_UP" could not be bound.

                   

                  using:

                  AlertName: ${RuleName}
                  Description: ${NodeName} is ${Status}
                  IP Address:  ${Node.IP_Address}
                  Node Type:  ${Node.MachineType}
                  Time of Event: ${Date} ${LongTime}
                  Status: ${Status}
                  Severity: Low
                  DownTime Duration: ${SQL: Select TriggerTimeOffset / 60 From AlertStatus Where AlertDefID = (Select AlertDefID From AlertDefinitions Where AlertName = ${AlertName}) AND ActiveObject = ${NodeID}}