/*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

Back to SQL queries main menu