Retrieve a list of monitors in a critical state

One thing the OpsMgr team 'forgot' to do was allow us to view all Monitors in a Critical state. This is obviously something a lot of people would need as it's nice to have an overview of what isn't in a healthy state without trawling through Health Explorer Windows.

Unfortunately there is no elegant way to do this with the SDK and/or Powershell, as this needs to be addressed on a "per monitoringobject" basis, meaning we have to loop through classes and objects and that will take a crazy amount of time.

Once again, SQL to the rescue:

Select [TimeGenerated],a.BaseManagedEntityId, a.DisplayName, a.TopLevelHostEntityId, e.DisplayName as ParentDisplayname, e.FullName as ParentFullName,
f.TypeName,
DateDiff(mi,TimeGenerated,GETUTCDate())[TimeInState],ISNULL(a.path,a.DisplayName)[Path],OperationalStateName,
a.DisplayName[Problem],d.DisplayName[Problem Description],d.Description[Detailed Description]
from BaseManagedEntity a
INNER JOIN ManagedEntityAvailabilityView b on a.BaseManagedEntityId =b.BaseManagedEntityId
INNER JOIN StateView c on b.BaseManagedEntityId =c.BaseManagedEntityId
INNER JOIN MonitorView d on c.MonitorID= d.ID
INNER JOIN BaseManagedEntity e on e.BaseManagedEntityId = a.TopLevelHostEntityId
INNER JOIN ManagedType f on a.BaseManagedTypeId=f.ManagedTypeId
INNER JOIN 
            
            (Select BaseManagedEntityID,NewHealthState,max(TimeGenerated)[TimeGenerated]
            From StateChangeEventView
            Group By BaseManagedEntityID,NewHealthState)SC
            On b.BaseManagedEntityID = SC.BaseManagedEntityId

Where HealthState = 3
and AlertPriority = 1
and IsUnitMonitor = 1
and SC.NewHealthState = 3
--and e.DisplayName = 'COMPUTERNAME'
Order By a.[Path]