5 Replies Latest reply on Feb 10, 2015 7:08 AM by simonp73

    Custom SQL alert against a SNMP trap condition

    simonp73

      Hi

       

      I want to run some custom SQL script against information in the Trapvarbinds and Traps tables of the DB.  However, I can't find where to do this in the SNMP Trap viewer under the create alerts bit

      trapview.png

      , nor can I do it from NPM and using custom SQL as i have to use the first part that I cannot change like select nodes .......

       

      I have written my custom SQL, and i just want to use that as the trigger condition but I have to relate it to nodes or one of the types in the set up your trigger query and I canno select SNMP traps!

      sql alert.png

       

      I want to be able to write all of the SQL statement myself, which I have done, but want to run it as an alert rather than a scheduled task.  unfortunately the scheduled task on the main polling server is looking more like what I may have to do.

       

      Please can anyone help?

        • Re: Custom SQL alert against a SNMP trap condition
          bps

          have you found a sollution? I am looking for something like that...

           

          regards Pascal

          • Re: Custom SQL alert against a SNMP trap condition
            mraky

            Hi,

             

            any luck ? Have oyu found any workaround ?

            • Re: Custom SQL alert against a SNMP trap condition
              simonp73

              Yes I have a work around, had to write a custom SQL query into the alert to pull together all of the information we needed to get this to work

              1 of 1 people found this helpful
              • Re: Custom SQL alert against a SNMP trap condition
                simonp73

                It took a lot of work to get this how we wanted, including a lot of failed attempts.  With what we were wanting to achieve we found the best possible way was to have the SQL run directly at the database.  We quickly realised that we didn't need to raise an alert and to get the alert to then fire a script to update the nodes table was going to be hard to achieve, so we looked at other ways to do this, including stored procedures within SQL - however, this would not work in our environment.  After much head scratching and wrong turns, I had a brain wave - why not create the SQL as an SQL Server User Experience Monitor - then we could run the SQL query/update as a monitor and if the monitor ever failed for any reason we would get an alert to this.

                 

                So what was it I wanted to monitor - Simple (or so I thought when I started this piece of work!).  When an ESX host is put into standby mode by DRS/DPM I wanted the appropriate host in solarwinds to also become unmanaged and vice versa. Yes I hear you shout thats easy, unfortunately not!  Virtualisation manager does not pull the host state back from vCenter and it was going to be difficult to do, plus if it did, the data was in 2 seperate databases.  The monitor was an SQL update query that looks for a specific phrase in the trapvarbind description, it uses the variable of the host name to then run an update query in the nodes table to put the host into an unmanaged state and set the description to contain the phrase Auto Unmanaged.  When the host comes back out of standby, there is another specific phrase that appears in the description and uses this to update the host to be managed again using a second SQL Server user experience Monitor.  In essence each monitor is 2 nested SQL queries/updates, we have a query that pulls the node information from the trapvarbinds table and an update query that then updates the necessary fields within the Nodes table.  These run every few minutes and have been working now for several months.  This work is helping us with automating our virtual environment

                 

                I know its not the Thwack way, but I'm not allowed to give the complete specifics on how we created this monitoring, but if anyone wants to get in touch if they are trying to produce something similar then I will do my best to help out.

                 

                I hope this goes a little way to making thins clearer - please feel free to get in touch.

                 

                Simon