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)
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
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:
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())
select * FROM Alert.vAlert WHERE AlertName=’[LYNC] User failed to join the conference.‘ AND RaisedDateTime >= DATEADD(day,-7, GETDATE())
For any query or suggestions please comment below.