How grooming and auto-resolution work in the OpsMgr 2007 Operational database
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.
