Alerts Per Day, for a Specific SCOM Group?

OdgeUK 41 Reputation points
2020-12-21T10:43:22.757+00:00

Has anyone ever been able to provide a daily count (either by direct SQL query on the Data Warehouse, or by Powershell) of 'Alerts per Day' from a SCOM group (typically a group containing SCOM Windows Server objects + Health Service objects)?

This query below will do Alerts per Day for all Alerts in the DW, but how to scope to a SCOM Group?

SELECT CONVERT(VARCHAR(20), RaisedDateTime, 102) AS DayAdded, COUNT(*) AS NumAlertsPerDay
FROM Alert.vAlert WITH (NOLOCK)
WHERE RaisedDateTime is not NULL
GROUP BY CONVERT(VARCHAR(20), RaisedDateTime, 102)
ORDER BY DayAdded DESC

Operations Manager
Operations Manager
A family of System Center products that provide infrastructure monitoring, help ensure the predictable performance and availability of vital applications, and offer comprehensive monitoring for datacenters and cloud, both private and public.
1,419 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Peter Svensson 211 Reputation points
    2020-12-21T13:23:33.403+00:00

    Try this query. Just replace the numbers of days (default 60 days) and the name of the group

    -- Alerts for a group (directly contained objects only)
    DECLARE @60dago DateTime;SET @60dago = DATEADD(day, -60, GETUTCDATE())
    SELECT COUNT(*),
    CAST(CONVERT(VARCHAR(20), RaisedDateTime, 102) As DateTime) as DateTime
    FROM OperationsManagerDW.Alert.vAlert AL
    JOIN OperationsManagerDW.dbo.vManagedEntity ME ON ME.ManagedEntityRowId = AL.ManagedEntityRowId
    JOIN OperationsManagerDW.dbo.vManagedEntity TME ON TME.ManagedEntityRowId = ME.TopLevelHostManagedEntityRowId
    JOIN OperationsManagerDW.dbo.vRelationship RE ON TME.ManagedEntityRowId = RE.TargetManagedEntityRowId
    JOIN OperationsManagerDW.dbo.vManagedEntity GRP ON GRP.ManagedEntityRowId = RE.SourceManagedEntityRowId
    WHERE GRP.DisplayName = 'Name of Group' AND [RaisedDateTime] >= @60dago
    GROUP BY CAST(CONVERT(VARCHAR(20), RaisedDateTime, 102) As DateTime)