It doesn't give you a recursive membership alerts.
My older post will only give you object specific alerts on that group, "nothing else"!
So, i started to think on how i could help me (and you!) out with this "issue", and came up with this ideia.
First, i've showed how to do this by powershell.
All alerts for specific group, in a specific time range, and for specific severity - this were my filters.
But, how can i "translate" powershell into SQL ?
SQL-Profiler!
Basically when you run a powershell cmd-let like :
Get-SCOMAlert -Criteria (...)
You're connecting into OperationsManager database and making a query.
So, the query goes as follow:
First, i create a temp table i can put my "groups".
DECLARE @TMP_GROUP_TABLE table(BaseManagedEntityId uniqueidentifier, DisplayName varchar(50));
INSERT INTO @TMP_GROUP_TABLE
SELECT BaseManagedEntityId, DisplayName FROM basemanagedentity WITH (nolock)
WHERE DisplayName = ('Group Name Here')
OR DisplayName = ('Other Group')
OR DisplayName = ('Other Group')
OR DisplayName = ('Other Group')
OR DisplayName = ('Other Group')
OR DisplayName = ('Other Group')Then, the magic query - it's already made up to join the temp table and add the group name in the end of it :) (Promise i'll make a post about SQL-Profiler!)
DECLARE @LanguageCode1 varchar(3)
DECLARE @LanguageCode2 varchar(3)
DECLARE @ParentManagedEntityId uniqueidentifier
DECLARE @ResolutionState0 nvarchar(max)
DECLARE @Severity0 nvarchar(max)
DECLARE @TimeRaised0 datetime
SET @LanguageCode1='ENU' ; SET @LanguageCode2=NULL
SET @ResolutionState0=N'0' ; SET @Severity0=N'1'; SET @TimeRaised0='2016-04-04 00:00:00'
SELECT DISTINCT [AlertView].[Id],[AlertView].[Name],[AlertView].[Description],[AlertView].[MonitoringObjectId],[AlertView].[ClassId],[AlertView].[MonitoringObjectDisplayName],[AlertView].[MonitoringObjectName],
[AlertView].[MonitoringObjectPath],[AlertView].[MonitoringObjectFullName],[AlertView].[IsMonitorAlert],[AlertView].[ProblemId],[AlertView].[RuleId],[AlertView].[ResolutionState],[AlertView].[Priority],
[AlertView].[Severity],[AlertView].[Category],[AlertView].[Owner],[AlertView].[ResolvedBy],[AlertView].[TimeRaised],[AlertView].[TimeAdded],[AlertView].[LastModified],[AlertView].[LastModifiedBy],
[AlertView].[TimeResolved],[AlertView].[TimeResolutionStateLastModified],[AlertView].[CustomField1],[AlertView].[CustomField2],[AlertView].[CustomField3],[AlertView].[CustomField4],[AlertView].[CustomField5],
[AlertView].[CustomField6],[AlertView].[CustomField7],[AlertView].[CustomField8],[AlertView].[CustomField9],[AlertView].[CustomField10],[AlertView].[TicketId],[AlertView].[Context],[AlertView].[ConnectorId],
[AlertView].[LastModifiedByNonConnector],[AlertView].[MonitoringObjectInMaintenanceMode],[AlertView].[MonitoringObjectHealthState],[AlertView].[ConnectorStatus],[AlertView].[RepeatCount],
[MT_Computer].[NetbiosComputerName],[MT_Computer].[NetbiosDomainName],[MT_Computer].[PrincipalName],[AlertView].[LanguageCode],[AlertView].[AlertParams],[AlertView].[SiteName],
[AlertView].[MaintenanceModeLastModified],[AlertView].[StateLastModified],[AlertView].[TfsWorkItemId],[AlertView].[TfsWorkItemOwner],
[TEMP].DisplayName AS GroupName
FROM dbo.fn_AlertView(@LanguageCode1, @LanguageCode2) AS AlertView
LEFT OUTER JOIN dbo.MT_Computer ON AlertView.TopLevelHostEntityId = MT_Computer.BaseManagedEntityId
INNER JOIN dbo.RecursiveMembership AS RM ON AlertView.MonitoringObjectId = RM.ContainedEntityId
INNER JOIN @TMP_GROUP_TABLE AS TEMP ON RM.ContainerEntityId = TEMP.BaseManagedEntityId
WHERE (AlertView.[ResolutionState] = @ResolutionState0
AND AlertView.[Severity] >= @Severity0
AND AlertView.[TimeRaised] > @TimeRaised0)
AND (((RM.ContainerEntityId IN ( SELECT BaseManagedEntityId FROM @TMP_GROUP_TABLE ) )))
ORDER BY [AlertView].[LastModified] DESC
Since you got your data into place, let's make the Datazen Dashboard for your teams!
This is a 5 minutes dashboard, you can edit the query to set some "alert thresholds" and create a more "KPI" oriented alert view dashboad in Datazen.
Cheers,
