SQL Query to report on how many times a particular alert has triggered

Here is a very useful SQL Query that you can use to determine how many times a particular alert has fired, when it fired and what host it fired on.

Open up Microsoft SQL Server Management Studio on your Datawarehouse server

Choose your datawarehouse database and run the query listed below starting at SELECT

NOTE 1:-You will need to amend the last line of the query (red text) with the name of your alert

NOTE 2:-This will list up to 100 instances

SELECT  TOP (100)  adt.Owner,

adt.TicketId,

alt.AlertName,

alt.AlertDescription,

alt.Severity,

alt.Priority,

alt.Category,

alt.RaisedDateTime,

alt.RepeatCount,

vManagedEntity.DisplayName,

vManagedEntity.Name,

vManagedEntity.Path

FROM Alert.vAlertResolutionState  AS  ars  INNER  JOIN

Alert.vAlertDetail  AS  adt  ON  ars.AlertGuid=adt.AlertGuid  INNER  JOIN

Alert.vAlert  AS  alt  ON  ars.AlertGuid=alt.AlertGuid  INNER  JOIN

vManagedEntity ON  alt.ManagedEntityRowId=vManagedEntity.ManagedEntityRowId

WHERE

alt.AlertName=‘Available Megabytes of Memory is too low’

 

ADDITIONAL NOTE (not part of SQL Query)

You will probably  see duplicate results but you can export to excel by right clicking on the output and choose select all and then copy with headers and paste it into excel so you can remove the duplicate entries

 

 

 

 

 

 

 

Advertisements
This entry was posted in SQL Queries and tagged , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s