Reporting

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

Find more posts tagged with

npm

web reports

Accepted answers

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.

All comments

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.

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);