2 Replies Latest reply on Jul 17, 2017 3:15 PM by ekis

    Alert Report

    ravicv

      Hi

       

       

      I am using below script to run a report for High Link utilization for past 7 days from alert history.

      In that I need to add 2 more columns like alert triggered time and the related device location.

      Suggest for any changes or modifications

       

       

      SELECT DISTINCT 
      ahv.Name 'Alert Name' 
        ,ahv.RelatedNodeCaption 'Parent Device'   
        ,ahv.EntityCaption 'Alert Object' 

        ,Message
         ,ISNULL(lastSeven.Qty,0) 'Last 7 Days' 
      FROM AlertHistoryView ahv 

      FULL OUTER JOIN 
        (SELECT   
      Name  = 'High Interface Transmit Percent Utilization'
        ,RelatedNodeCaption   
        ,EntityCaption 
        ,COUNT(1) 'Qty' 
        FROM AlertHistoryView   
        WHERE EventTypeWord = 'Triggered'   
        AND TimeStamp > (GetDate()-7)   
        GROUP BY Name, RelatedNodeCaption, EntityCaption) lastSeven ON ahv.Name = lastSeven.Name AND ahv.RelatedNodeCaption = lastSeven.RelatedNodeCaption AND ahv.EntityCaption = lastSeven.EntityCaption 

        • Re: Alert Report
          ekis

          The Alert Triggered Time is under the TimeStamp column of AlertHistoryView.

          The location of the related node/device is not in the AlertHistoryView. You can try joining with the Nodes view to get the node custom properties and then in the WHERE clause, you have to mention the specific custom property name and value regarding the node's location.

          Hope this helps.

           

           

          1 of 1 people found this helpful
          • Re: Alert Report
            ekis

            Also, it looks like you want to focus only on High Link utilization data.

            It appears that the query you composed has FORCED the name by including the part :

             

            Name  = 'High Interface Transmit Percent Utilization'

             

            However, that will not filter out any other alert names. You need to do the filtering in the WHERE Clause.