3 Replies Latest reply on Jan 8, 2015 3:57 PM by mharvey

    Custom Table for Alerts

    multipl3x

       

      I do not understand why this does not work

        • Re: Custom Table for Alerts
          mharvey

          The alerts in the reports, from what I can tell are looking at basic alerts, not advanced alerts, and as such will not pull data.  You'd have to create a SQL query to look at the alertlog table to see the advanced alerts that have triggered.  Either that or just add the All Active Alerts resource to the page.  I had to do something similar for an after hours alert report that was requested.  The SQL I came up with for that is:

           

          Select ObjectName as "Triggered Device",

          LogDateTime as "Alert Time",

          ad.AlertName as "Alert Triggered"

           

           

          from AlertLog a

          Join AlertDefinitions ad on a.AlertDefID = ad.AlertDefID

          Where  Message = 'Alert Triggered'

          AND LogDateTime >=dateadd(day,datediff(day,0,GetDate())- 1,0)

          AND

          (

            (Convert(Char,LogDateTime,108) <= '06:30:00 AM') OR

            (Convert(Char,LogDateTime,108) >= '18:00:00 PM')

          )

           

           

          Order by "Alert Time" asc

            • Re: Custom Table for Alerts
              multipl3x

              Hey thanks! How did you manage to come up with that query? I am running into more and more situations where using a SQL query is the only way to "get the job done," so to speak. In this case specifically, I was trying to create a table that would show routing alerts, I just really have no way of knowing how to go about doing that.

                • Re: Custom Table for Alerts
                  mharvey

                  The first thing I start with years ago was creating basic reports in the Orion Report Writer, then using the Report menu option and Show SQL to see the SQL query used behind the scenes. Between that and using w3schools for SQL tutorials I started developing a better understanding of using SQL.  From there I'd then move to doing select statements against the SolarWinds database finding out what information was stored where and how it was related.  I'm still learning more everyday, and I'm thankful for google when I come across an issue where I don't know how to create the SQL, I can find something similar and just manipulate it.