How to Identify Alerts That Violate the Service Level Exception Policy (297810)

SUMMARY

This article describes a SQL query that you can use to identify the alerts that have been open beyond the time limits that are set in the Service Level Exception policy in Microsoft Operations Manager (MOM).

You can view these alerts by expanding the Monitor folder and then clicking the All Service Level Exceptions view.

MORE INFORMATION

There is no native mechanism within MOM to alert administrators to violations of the Service Level Exception policy. Service level exceptions are alerts that have remained in a particular resolution state in excess of the specified service level agreement time. These alerts may need attention as soon as possible.

The Service Level Exception policy is configured in the MOM Administrator console, in the Alert Resolution States node under Global Settings.

The following example Microsoft SQL query identifies the current alerts that are in violation of the established Service Level Exception policy:

DECLARE  @ServerUTC datetime
DECLARE  @TZ_Bias   smallint

EXEC master..xp_coordinatetime @TZ_Bias OUTPUT, @ServerUTC OUTPUT

select * from AlertView AV

inner join ResolutionState RS ON RS.ResolutionId = AV.ResolutionState

where LastTimeStateWasModified < dateadd(mi,-(RS.ExpectedMaxTime * RS.ExpectedMaxTimeUnits),@ServerUTC)

and ResolutionState < 255