Top 50 state changing unit monitors in last 7 days
/*Top 50 state changing unit monitors in last 7 days.*/
USE OperationsManagerDW
SELECT TOP (50) COUNT(*) AS Count, vMonitor.MonitorDefaultName, vManagementPack.ManagementPackDefaultName
FROM State.vStateRaw INNER JOIN
vManagedEntityMonitor ON State.vStateRaw.ManagedEntityMonitorRowId = vManagedEntityMonitor.ManagedEntityMonitorRowId INNER JOIN
vMonitor ON vManagedEntityMonitor.MonitorRowId = vMonitor.MonitorRowId INNER JOIN
vMonitorManagementPackVersion ON vManagedEntityMonitor.MonitorRowId = vMonitorManagementPackVersion.MonitorRowId INNER JOIN
vManagementPack ON vMonitor.ManagementPackRowId = vManagementPack.ManagementPackRowId
WHERE (vMonitorManagementPackVersion.UnitMonitorInd = 1) AND (State.vStateRaw.DateTime BETWEEN DATEADD(day, - 6, GETDATE()) AND GETDATE())
GROUP BY vMonitor.MonitorDefaultName, vManagementPack.ManagementPackDefaultName
ORDER BY Count DESC