How Grooming and Auto-Resolution works in the OpsMgr 2007 Operations DB

Warning – don’t read this if you are bored easily. 

In a simplified view to groom alerts…..

Grooming of the ops DB is called once per day at 12:00am…. by the rule:  “Partitioning and Grooming”  You can search for this rule in the Authoring space of the console, under Rules.  It is targeted to the “Root Management Server” and is part of the System Center Internal Library.

It calls the “p_PartitioningAndGrooming” stored procedure, which calls p_Grooming, which calls p_GroomNonPartitionedObjects (Alerts are not partitioned) which inspects the PartitionAndGroomingSettings table… and executes each stored procedure.  The Alerts stored procedure in that table is referenced as p_AlertGrooming which has the following sql statement:

    SELECT AlertId INTO #AlertsToGroom

    FROM dbo.Alert

    WHERE TimeResolved IS NOT NULL

    AND TimeResolved < @GroomingThresholdUTC

    AND ResolutionState = 255

So…. the criteria for what is groomed is pretty simple:  In a resolution state of “Closed” (255) and older than the 7 day default setting (or your custom setting referenced in the table above)

We won’t groom any alerts that are in New (0), or any custom resolution-states (custom ID #).  Those will have to be set to “Closed” (255)…. either by autoresolution of a monitor returning to healthy, direct user interaction, our built in autoresolution mechanism, or your own custom script.

Ok – that covers grooming.

However – I can see that brings up the question – how does auto-resolution work?

That specifically states “alerts in the new resolution state”.  I don’t think that is completely correct:

That is called upon by the rule “Alert Auto Resolve Execute All” which runs p_AlertAutoResolveExecuteAll once per day at 4:00am.  This calls p_AlertAutoResolve twice…. once with a variable of “0” and once with a “1”.

Here is the sql statement:

IF (@AutoResolveType = 0)

    BEGIN

        SELECT @AlertResolvePeriodInDays = [SettingValue]

        FROM dbo.[GlobalSettings]

        WHERE [ManagedTypePropertyId] = dbo.fn_ManagedTypePropertyId_MicrosoftSystemCenterManagementGroup_HealthyAlertAutoResolvePeriod()

        SET @AutoResolveThreshold = DATEADD(dd, -@AlertResolvePeriodInDays, getutcdate())

        SET @RootMonitorId = dbo.fn_ManagedTypeId_SystemHealthEntityState()

        -- We will resolve all alerts that have green state and are un-resolved

        -- and haven't been modified for N number of days.

        INSERT INTO @AlertsToBeResolved

        SELECT A.[AlertId]

        FROM dbo.[Alert] A

        JOIN dbo.[State] S

            ON A.[BaseManagedEntityId] = S.[BaseManagedEntityId] AND S.[MonitorId] = @RootMonitorId

        WHERE A.[LastModified] < @AutoResolveThreshold

        AND A.[ResolutionState] <> 255

        AND S.[HealthState] = 1

<snip>

    ELSE IF (@AutoResolveType = 1)

    BEGIN

        SELECT @AlertResolvePeriodInDays = [SettingValue]

        FROM dbo.[GlobalSettings]

        WHERE [ManagedTypePropertyId] = dbo.fn_ManagedTypePropertyId_MicrosoftSystemCenterManagementGroup_AlertAutoResolvePeriod()

        SET @AutoResolveThreshold = DATEADD(dd, -@AlertResolvePeriodInDays, getutcdate())

        -- We will resolve all alerts that are un-resolved

        -- and haven't been modified for N number of days.

        INSERT INTO @AlertsToBeResolved

        SELECT A.[AlertId]

        FROM dbo.[Alert] A

        WHERE A.[LastModified] < @AutoResolveThreshold

        AND ResolutionState <> 255

So we are basically checking that Resolution state <> 255….. not specifically “New” (0) as we would lead you to believe by the wording in the interface.  There are simply two types of auto-resolution:  Resolve all alerts where the object has returned to a healthy state in “N” days….. and Resolve all alerts no matter what, as long as they haven’t been modified in “N” days.