2 Replies Latest reply on Jul 20, 2016 4:18 AM by hansons

    Reporting

    hansons

      Hi All,

       

      Just looking to see if it is possible to create a custom report to count the number of times an alert has been triggered during the past month?

       

      Thanks for any information

       

      Stuart

        • Re: Reporting
          rickrocks

          Hello Stuart,

           

          You can create an Advance SQL report from your Report Writer. You should know the specific alert you want to count, here's a sample SQL Query:

           

          SELECT Count(Message) FROM [dbo].[AlertHistory]

          where Message = 'Node is down'

          AND TimeStamp BETWEEN '6/1/2016' AND '6/30/2016'

           

          The sql query will count all triggered alerts with message 'Node is down' from the AlertHistory table for the last month. If you want to verify, you can change the query with the following:

           

          SELECT Message,Timestamp FROM [dbo].[AlertHistory]

          where Message = 'Node is down'

          AND TimeStamp between '6/1/2016' AND '6/30/2016'

           

          Hope this helps.

          1 of 1 people found this helpful
            • Re: Reporting
              hansons

              Hi RickRocks,

               

              Thanks for pointing me in the right direction, I used your Query to get me the basic info and then edited to get exactly what i needed below is what i used just incase anyone else is looking for a similar thing

               

              SELECT
              [Message] ,

                    
              CAST(EventTime
              AS DATE) AS [Event Date] ,

                    
              COUNT([Message]) AS [Number of
              Occurrences]

              FROM  
              [DatabaseName].[dbo].[Events]

              WHERE 
              [Message] IN (

                    
              'event log message',

                    
              'event log message' )

                    
              AND EventTime >
              GETDATE() - 30

              GROUP BY [Message] ,

                    
              CAST(EventTime
              AS DATE);