4 Replies Latest reply on Jan 7, 2016 8:10 PM by RichardLetts

    Writing a report that records downtime due to power outages.

    murogive

      I am not an SQL guru for that matter thus am in dire need of your help in writing a report that can enable me monitor a network and report on the times when this network goes down, not because the ISP is not doing its job, but rather when the router at that site goes down due to a power outage. We should Also consider the fact that this monitoring is only relevant during working hours, i.e. 8 AM - 8 PM. Thus this report should put that into consideration. My biggest challenge came when trying to determine the attribute on the router/node that I should monitor to enable me record events that are bought about my power outages on the router. Thank you

        • Re: Writing a report that records downtime due to power outages.
          silverbacksays

          I think the main challenge you're going to have, is making SolarWinds aware of a issue with mains power at a remote site. Monitoring a router only will not give you this information, as the device will not have any information on the state of mains, be it available or not.

           

          What you're going to need is some way to have a separate device tell you when mains power is lost. Most of the time, this would be via a SNMP trap received from some form of UPS. When the power fails, and the UPS has to start providing power, most vendors support sending SNMP traps out saying "Hey! Power has gone down!". For the rest of my post I'm going to assume you have this in place.

           

          What you'd need to do is this:

           

          1. Create a new alert, naming it accordingly and setting the relevant properties in the 'Properties' tab.
          2. In trigger conditions, select I want to alert on "Custom SQL Alert".
          3. In the box, you need to specify the relevant SQL query to search for the SNMP trap from your remote site UPS. The table you need to point the query at is "taps". It's easier if you only have traps for power failures coming from the remote device, as you then only need to search for the IP address of the device.
          4. At the bottom of the trigger window, expand 'advanced conditions' and then click the box to enable them.
          5. Add a secondary section, selecting 'and then after' for the logic.
          6. In the secondary section, specify nodes as the search, and put in the specific node that will go down when the battery power of the UPS dies.
          7. Complete the rest of the alert definition as needed.

           

          With this in place, you should get an alert firing off when the nodes goes down after the UPS has said it has a problem. The trigger time will be the time the power failed, and when the node comes back up (and by default the alert resets if you've left the reset conditions to be 'when trigger conditions are no longer true'), you'll have the time power is restored.

           

          Then, all you need to do is create a report specifically looking for the trigger and reset times of this alert.

           

          I'd love to be able to give you the exact code for the first step, but I'm a neophyte DBA myself. I hope it at least helps you get to where you need to be!

          1 of 1 people found this helpful