Alerts Per Day, for a Specific SCOM Group?

Question

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

Answer 1

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)