/*Number of unit monitor state changes per day for last 28 days.*/
USE OperationsManagerDW
SELECT CONVERT(VARCHAR(10), DateTime, 101) AS Date, COUNT(*) AS 'State Changes'
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
(DateTime BETWEEN DATEADD(day, - 27, GETDATE()) AND GETDATE())
GROUP BY CONVERT(VARCHAR(10), DateTime, 101)
ORDER BY Date DESC

Back to SQL queries main menu