SystemCenter

All about System Center Articles and Tutorials

SPECIFIC ALERT HISTORY IN SCOM

Posted by on Jun 30, 2016 in SCOM 2012, SQL

SPECIFIC ALERT HISTORY IN SCOM

Recently someone asked me from the Lync Application team to send the alert history of a particular alert from SCOM which was recurring from last few months. I checked the out of box reports in SCOM however couldn’t find any report which can fulfill the purpose.

Then I thought giving it a try through SQL. I logged in to the SCOM DW and fired below simple query:

SELECT TOP (10000)

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=[LYNC] Number of occasions conference processing is delayed

 

You can modify the number of rows to be displayed as per requirement. I selected top 10000 rows only.

Provide the alert name against alt.AlertName table name in query.

The query took less than one second and provided the desired output:

1

The same way we can check the number of particular alert rose in last one week. Below is the query:

select * FROM Alert.vAlert WHERE AlertName=’Alert Name’ AND RaisedDateTime >= DATEADD(day,-7, GETDATE())

Example:

select * FROM Alert.vAlert WHERE AlertName=’[LYNC] User failed to join the conference.‘ AND RaisedDateTime >= DATEADD(day,-7, GETDATE())

Output:

2

For any query or suggestions please comment below.

Submit a Comment

Your email address will not be published. Required fields are marked *