OpsMgr (SCOM) - Alerts per group SQL Query (+) Datazen Dashboard

Since i showed up how to get it in a last post, a PFE friend of mine noticed something it migh consern, and to be honest i didn't pointed out because to me was not an issue, but, i understand it might me to some of you.

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,