9 Replies Latest reply on Sep 1, 2016 12:42 PM by zackm

    Counting and Listing Alerts triggered by Date

    Deltona

      Hi guys,

       

      I currently get alerts whenever an interface goes down and when it comes back up again.

      I have two alerts in total, one for the LAN and one for the WAN. The way I separate the two is by using a Custom Property on WAN interfaces.

       

      I'm good with the alerts that I currently receive and it works as it should, but I'd like to get a report based on these alerts, that counts the number of times the interface triggered the alert in the past.

      The report(s) should display the number of occurrences for this alert this week, this month, last week and last month.

       

      I'm having trouble building one such report or Custom Table resource based on SQL/SWQL. I've even tried basing the report off Events that the Alert creates. No luck so far. It either doesn't work or the count is way off. It might be due to the way historical alerts/events are stored. It may even be due to an issue with timestamps and alerts/events? I'm hoping I can get some help with this here.

       

      The format of the report would look something like this:

       

      Number of times ISP Links went down - this week

      CaptionObject NameTrigger Count
      Node NameThe name of the interface that triggered this alertThe number of times the Alert/Event has been triggered in the specific timeframe

       

      It resembles somewhat the Top X Objects by trigger count of this alert resource that you find in an Active Alert Details view.

       

      What I have so far is based on zackm's work posted here: Alert Triggered

       

      Any help greatly appreciated!

        • Re: Counting and Listing Alerts triggered by Date
          zackm

          can you post the alert names here?

            • Re: Counting and Listing Alerts triggered by Date
              Deltona

              Hi Zack,

               

              Alert Names: Primary WAN Link Utilization & Seconday WAN Link Utilization

                • Re: Counting and Listing Alerts triggered by Date
                  zackm

                  The first point I would make is that the Alert History is only kept as long as the Event Log (default 30 days). So, I'm going to keep that in consideration on these queries. If you extend your event log retention, you can get a "previous month" addition here, but without knowing your environment and how many events it produces, I cannot in good conscious recommend that. (i.e.; YMMV and extending the event log could add a LOT of rows to the table...) Not saying you cannot or should not, just that you should be aware of the repercussions.

                   

                  OK, so starting with this basic query:

                   

                  SELECT  
                    Name 'Alert Name'  
                    ,COUNT(1) 'Times Alert Triggered'  
                  FROM AlertHistoryView  
                  WHERE EventTypeWord = 'Triggered'  
                  AND TimeStamp > (GETDATE()-1)  
                  GROUP BY Name 
                  ORDER BY Name
                  

                   

                  We can then adjust to separate out the time limitations as such:

                   

                  SELECT DISTINCT
                    ahv.Name 'Alert Name'
                    ,lastseven.Qty 'Last 7 Days'
                    ,lastthirty.Qty 'Last 30 Days'
                  FROM AlertHistoryView ahv
                  JOIN
                    (SELECT Name, COUNT(1) 'Qty' 
                    FROM AlertHistoryView 
                    WHERE Name IN ('Alert me when an application goes down','Alert me when a component goes down') 
                    AND TimeStamp > (GetDate()-7)
                    AND EventTypeWord = 'Triggered'
                    GROUP BY Name) lastseven ON ahv.Name = lastseven.Name
                  JOIN
                    (SELECT Name, COUNT(1) 'Qty' 
                    FROM AlertHistoryView 
                    WHERE Name IN ('Alert me when an application goes down','Alert me when a component goes down') 
                    AND TimeStamp > (GetDate()-30)
                    AND EventTypeWord = 'Triggered'
                    GROUP BY Name) lastthirty ON ahv.Name = lastthirty.Name
                  ORDER BY ahv.Name
                  

                   

                  Which would look something like this:

                   

                  Alert NameLast 7 DaysLast 30 Days
                  Alert me when a component goes down919
                  Alert me when an application goes down818

                   

                  Now, to be a bit more precise, we can add the objects that triggered the alerts like so:

                   

                  SELECT  
                    Name 'Alert Name'
                    ,RelatedNodeCaption 'Device'  
                    ,EntityCaption 'Interface'  
                    ,COUNT(1) 'Times Alert Triggered'  
                  FROM AlertHistoryView  
                  WHERE EventTypeWord = 'Triggered'  
                  AND TimeStamp > (GETDATE()-1)
                  AND Name IN ('Alert me when an application goes down','Alert me when a component goes down')  
                  GROUP BY Name, RelatedNodeCaption, EntityCaption
                  ORDER BY Name, RelatedNodeCaption, EntityCaption 
                  

                   

                  And then we can again adjust to separate out the time limitations:

                   

                  SELECT DISTINCT
                    ahv.Name 'Alert Name'
                    ,ahv.RelatedNodeCaption 'Device'  
                    ,ahv.EntityCaption 'Interface'
                    ,lastseven.Qty 'Last 7 Days'
                    ,lastthirty.Qty 'Last 30 Days'
                  FROM AlertHistoryView ahv
                  JOIN
                    (SELECT  
                    Name
                    ,RelatedNodeCaption
                    ,EntityCaption
                    ,COUNT(1) 'Qty'
                    FROM AlertHistoryView  
                    WHERE EventTypeWord = 'Triggered'  
                    AND TimeStamp > (GETDATE()-7)
                    AND Name IN ('Alert me when an application goes down','Alert me when a component goes down')  
                    GROUP BY Name, RelatedNodeCaption, EntityCaption) lastseven ON ahv.Name = lastseven.Name AND ahv.RelatedNodeCaption = lastseven.RelatedNodeCaption AND ahv.EntityCaption = lastseven.EntityCaption
                  JOIN
                    (SELECT  
                    Name
                    ,RelatedNodeCaption
                    ,EntityCaption
                    ,COUNT(1) 'Qty'
                    FROM AlertHistoryView  
                    WHERE EventTypeWord = 'Triggered'  
                    AND TimeStamp > (GETDATE()-30)
                    AND Name IN ('Alert me when an application goes down','Alert me when a component goes down')  
                    GROUP BY Name, RelatedNodeCaption, EntityCaption) lastthirty ON ahv.Name = lastthirty.Name AND ahv.RelatedNodeCaption = lastthirty.RelatedNodeCaption AND ahv.EntityCaption = lastthirty.EntityCaption
                  

                   

                  Which would look something like this (obviously these are not interfaces, but we work with what the lab provides ):

                   

                  2016-05-04_9-04-26.png

                   

                  This should get you in the right direction. Just change the names of the alerts listed in the 2 subqueries (lines 17 and 28 in the last example)

                   

                  For bonus points, this is what the query would look like for you IF you had the full 62 days of retention needed to provide a "Last 7/This Month/Previous Month" type report:

                   

                  First, we need to work on declaring some variable for our DateTIme comparisons... (If you have SQL 2012 or later, you can play with the EOMONTH Function)

                   

                  DECLARE @startOfPreviousMonth DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
                  DECLARE @startOfCurrentMonth DATETIME = DATEADD(month, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
                  DECLARE @lastSevenDays DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-7, 0)
                  

                   

                  Which looks like this:

                  SELECT
                  @startofPreviousMonth 'Start of Previous Month'
                  ,@startOfCurrentMonth 'Start of Current Month'
                  ,@lastSevenDays 'Last 7 Days'
                  

                   

                  Start of Previous MonthStart of Current MonthLast 7 Days
                  2016-04-01 00:00:00.0002016-05-01 00:00:00.0002016-04-27 00:00:00.000

                   

                  Now, we just amend our query from above as such:

                   

                  DECLARE @startOfPreviousMonth DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
                  DECLARE @startOfCurrentMonth DATETIME = DATEADD(month, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
                  DECLARE @lastSevenDays DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-7, 0)
                  
                  
                  SELECT DISTINCT
                    ahv.Name 'Alert Name'
                    ,ahv.RelatedNodeCaption 'Device'  
                    ,ahv.EntityCaption 'Interface'
                    ,lastseven.Qty 'Last 7 Days'
                    ,thisMonth.Qty 'This Month'
                    ,prevMonth.Qty 'Previous Month'
                  FROM AlertHistoryView ahv
                  JOIN
                    (SELECT  
                    Name
                    ,RelatedNodeCaption
                    ,EntityCaption
                    ,COUNT(1) 'Qty'
                    FROM AlertHistoryView  
                    WHERE EventTypeWord = 'Triggered'  
                    AND TimeStamp > @lastSevenDays
                    AND Name IN ('Alert me when an application goes down','Alert me when a component goes down')  
                    GROUP BY Name, RelatedNodeCaption, EntityCaption) lastseven ON ahv.Name = lastseven.Name AND ahv.RelatedNodeCaption = lastseven.RelatedNodeCaption AND ahv.EntityCaption = lastseven.EntityCaption
                  JOIN
                    (SELECT  
                    Name
                    ,RelatedNodeCaption
                    ,EntityCaption
                    ,COUNT(1) 'Qty'
                    FROM AlertHistoryView  
                    WHERE EventTypeWord = 'Triggered'  
                    AND TimeStamp > @startOfCurrentMonth
                    AND Name IN ('Alert me when an application goes down','Alert me when a component goes down')  
                    GROUP BY Name, RelatedNodeCaption, EntityCaption) thisMonth ON ahv.Name = thisMonth.Name AND ahv.RelatedNodeCaption = thisMonth.RelatedNodeCaption AND ahv.EntityCaption = thisMonth.EntityCaption
                  JOIN
                    (SELECT  
                    Name
                    ,RelatedNodeCaption
                    ,EntityCaption
                    ,COUNT(1) 'Qty'
                    FROM AlertHistoryView  
                    WHERE EventTypeWord = 'Triggered'  
                    AND TimeStamp BETWEEN @startOfPreviousMonth AND @startOfCurrentMonth
                    AND Name IN ('Alert me when an application goes down','Alert me when a component goes down')  
                    GROUP BY Name, RelatedNodeCaption, EntityCaption) prevMonth ON ahv.Name = prevMonth.Name AND ahv.RelatedNodeCaption = prevMonth.RelatedNodeCaption AND ahv.EntityCaption = prevMonth.EntityCaption
                  

                   

                   

                  Hope that helps!

                   

                   

                  -ZackM

                  Loop1 Systems: SolarWinds Training and Professional Services

                  1 of 1 people found this helpful
                    • Re: Counting and Listing Alerts triggered by Date
                      Deltona

                      It helped all right, thanks a lot! Especially for the DateTime bits.

                      Fan-Flipping-Tastic!

                      • Re: Counting and Listing Alerts triggered by Date
                        I LIKE EGGS

                        Just landed on a gold mine of a thread nice work chaps

                        • Re: Counting and Listing Alerts triggered by Date
                          Deltona

                          I just noticed that if an object triggered 0 alerts in the last 7 days while having triggered say 12 alerts in the last 30 days then the entry doesn't show up.

                          Same goes the other way around. If an object triggered 0 alerts last month but did in fact trigger a couple alerts in the last 7 days then it won't show up.

                          It's as if 0's returned in a query annul the entire joined query. Does it make any sense?

                            • Re: Counting and Listing Alerts triggered by Date
                              zackm

                              maybe try changing the joins to a FULL OUTER JOIN type?

                               

                              I can look later, a bit swamped atm.

                              • Re: Counting and Listing Alerts triggered by Date
                                zackm

                                Try this one out:

                                 

                                DECLARE @startOfPreviousMonth DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)  
                                DECLARE @startOfCurrentMonth DATETIME = DATEADD(month, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)  
                                DECLARE @lastSevenDays DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-7, 0)  
                                  
                                  
                                SELECT DISTINCT  
                                  ahv.Name 'Alert Name'  
                                  ,ahv.RelatedNodeCaption 'Device'    
                                  ,ahv.EntityCaption 'Interface'  
                                  ,ISNULL(lastseven.Qty,0) 'Last 7 Days'  
                                  ,ISNULL(thisMonth.Qty,0) 'This Month'  
                                  ,ISNULL(prevMonth.Qty,0) 'Previous Month'  
                                FROM AlertHistoryView ahv  
                                
                                
                                FULL OUTER JOIN  
                                  (SELECT    
                                  Name  
                                  ,RelatedNodeCaption  
                                  ,EntityCaption  
                                  ,COUNT(1) 'Qty'  
                                  FROM AlertHistoryView    
                                  WHERE EventTypeWord = 'Triggered'    
                                  AND TimeStamp > @lastSevenDays   
                                  GROUP BY Name, RelatedNodeCaption, EntityCaption) lastseven ON ahv.Name = lastseven.Name AND ahv.RelatedNodeCaption = lastseven.RelatedNodeCaption AND ahv.EntityCaption = lastseven.EntityCaption  
                                
                                
                                FULL OUTER JOIN  
                                  (SELECT    
                                  Name  
                                  ,RelatedNodeCaption  
                                  ,EntityCaption  
                                  ,COUNT(1) 'Qty'  
                                  FROM AlertHistoryView    
                                  WHERE EventTypeWord = 'Triggered'    
                                  AND TimeStamp > @startOfCurrentMonth    
                                  GROUP BY Name, RelatedNodeCaption, EntityCaption) thisMonth ON ahv.Name = thisMonth.Name AND ahv.RelatedNodeCaption = thisMonth.RelatedNodeCaption AND ahv.EntityCaption = thisMonth.EntityCaption  
                                
                                
                                FULL OUTER JOIN  
                                  (SELECT    
                                  Name  
                                  ,RelatedNodeCaption  
                                  ,EntityCaption  
                                  ,COUNT(1) 'Qty'  
                                  FROM AlertHistoryView    
                                  WHERE EventTypeWord = 'Triggered'    
                                  AND TimeStamp BETWEEN @startOfPreviousMonth AND @startOfCurrentMonth   
                                  GROUP BY Name, RelatedNodeCaption, EntityCaption) prevMonth ON ahv.Name = prevMonth.Name AND ahv.RelatedNodeCaption = prevMonth.RelatedNodeCaption AND ahv.EntityCaption = prevMonth.EntityCaption
                                  
                                WHERE ahv.Name IN ('Alert me when an application goes down','Alert me when a component goes down')