SCOM Alerts to MPs

Hi its been a while since I last posted but I have just written a very useful SQL query for SCOM which lists all Alerts to the Management Packs when run against the DataWarehouse

SELECT va.AlertGuid,
 va.AlertName,
 va.AlertDescription,
 va.Category,
 va.Priority,
 va.Severity,
 vad.DWCreatedDateTime,
 vad.Owner,
 vad.CustomField9,
 vad.CustomField10,
 vme.Path,
 vme.DisplayName,
 vme.ManagedEntityDefaultName,
 vmp.ManagementPackDefaultName,
 vmp.ManagementPackSystemName,
 vmt.ManagedEntityTypeDefaultName

FROM Alert.vAlert va with (nolock)

Inner Join [dbol.[vManagedEntity] vme with (nolock) on vme.ManagedEntityRowld=va.ManagedEntityRowld
Inner Join [dbol.[vManagedEntityTypel vmt with (nolock) on vmt.ManagedEntityTypeRowld=vme.ManagedEntityTypeRowld
Inner Join [Alertl.[vAlertDetail] vad with (nolock) on va.AlertGuid=vad.AlertGuid

Left outer join [dbo].[vMonitorl vm with (nolock) on va.MonitorAlertlnd=1 AND vm.MonitorRowId=va.WorkflowRowId
Left outer join vr with (nolock) on va.MonitorAlertlnd=0 AND vr.RuleRowld=va.WorkflowRowld

Inner Join vManagementPack vmp on vmp.ManagementPackRowld=lsNull(vm.[ManagementPackRowId],vr.ManagementPackRowId)

WHERE vmp.ManagementPackDefaultName like '%active directory%'

order by ManagementPackDefaultName

Note – please see https://technet.microsoft.com/en-us/library/gg508713.aspx for the schema details I used to create this query