Number of alerts raised per day for last 28 days

/*Number of alerts raised per day for last 28 days.*/ USE OperationsManagerDW SELECT CONVERT(VARCHAR(10), DBCreatedDateTime, 101) AS Date, COUNT(*) AS Alerts FROM  Alert.vAlert WHERE (DBCreatedDateTime BETWEEN DATEADD(day, -27, GETDATE()) AND GETDATE()) GROUP BY CONVERT(VARCHAR(10), DBCreatedDateTime, 101) ORDER BY Date DESC Back to SQL queries main menu

2

Number of events collected per day for last 28 days

/*Number of events collected per day for last 28 days.*/ USE OperationsManagerDW SELECT CONVERT(VARCHAR(10), DateTime, 101) AS Date, COUNT(*) AS Events FROM  Event.vEvent WHERE (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

2

Number of state change events per day for last 28 days

/*Number of state change events per day for last 28 days.*/ USE OperationsManagerDW SELECT CONVERT(VARCHAR(10), DateTime, 101) AS Date, COUNT(*) AS ‘State Changes’ FROM  State.vStateRaw WHERE (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

0

Number of unit monitor state changes per day for last 28 days

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

0

Top 50 alert generating rules and monitors in last 7 days, sorted by alert + repeat count

/*Top 50 alert generating rules and monitors in last 7 days, sorted by alert + repeat count.*/ USE OperationsManagerDW SELECT TOP (50) Alerts.AlertName, SUM(1) AS ‘Alert Instances’, SUM(Alerts.RepeatCount + 1) AS ‘Alert + Repeat Count’,                CASE Alerts.MonitorAlertInd WHEN 1 THEN vMonitor.MonitorDefaultName WHEN 0 THEN vRule.RuleDefaultName ELSE ‘Not found’ END AS ‘Rule or Monitor Name’,…

0

Top 50 alert generating rules and monitors in last 7 days, sorted by alert instances

/*Top 50 alert generating rules and monitors in last 7 days, sorted by alert instances.*/ USE OperationsManagerDW SELECT TOP (50) Alerts.AlertName, SUM(1) AS ‘Alert Instances’, SUM(Alerts.RepeatCount + 1) AS ‘Alert + Repeat Count’,                CASE Alerts.MonitorAlertInd WHEN 1 THEN vMonitor.MonitorDefaultName WHEN 0 THEN vRule.RuleDefaultName ELSE ‘Not found’ END AS ‘Rule or Monitor Name’,                CASE…

0

Top 50 event inserts in last 7 days

/*Top 50 event inserts in last 7 days.*/ USE OperationsManagerDW SELECT TOP (50) COUNT(*) AS Count, Event.vEvent.EventDisplayNumber AS ‘EventId’, vRule.RuleDefaultName AS ‘Collection Rule’,                vManagementPack.ManagementPackDefaultName FROM  Event.vEventRule INNER JOIN                vRule ON Event.vEventRule.RuleRowId = vRule.RuleRowId INNER JOIN                Event.vEvent ON Event.vEventRule.EventOriginId = Event.vEvent.EventOriginId INNER JOIN                vManagementPack ON vRule.ManagementPackRowId = vManagementPack.ManagementPackRowId WHERE (Event.vEvent.DateTime BETWEEN DATEADD(day,…

0

Top 50 performance sample inserts for last 10 days (by default)

/*Top 50 Performance Sample Inserts for last 10 days by default.*/ USE OperationsManagerDW SELECT TOP (50) COUNT(*) AS Count, vPerformanceRule.ObjectName AS Object, vPerformanceRule.CounterName AS Counter,                vPerformanceRuleInstance.InstanceName AS Instance, vRule.RuleDefaultName AS ‘Collection Rule’,                vManagementPack.ManagementPackDefaultName AS ‘Management Pack’ FROM  Perf.vPerfRaw INNER JOIN                vPerformanceRuleInstance ON Perf.vPerfRaw.PerformanceRuleInstanceRowId = vPerformanceRuleInstance.PerformanceRuleInstanceRowId INNER JOIN                vPerformanceRule ON vPerformanceRuleInstance.RuleRowId =…

0

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…

0

Discovery Check

These were initially published by Daniele Grandini and have been a great tool for finding noisy discoveries.  Only small modifications to time window has been made. /*Top discovery rules in the last 24 hours*/ USE OperationsManagerDW SELECT ManagedEntityTypeSystemName, DiscoverySystemName, COUNT(*) AS ‘Changes’ FROM  (SELECT DISTINCT                               MP.ManagementPackSystemName, MET.ManagedEntityTypeSystemName, METP.PropertySystemName, D.DiscoverySystemName,                               D.DiscoveryDefaultName, MET1.ManagedEntityTypeSystemName AS ‘TargetTypeSystemName’,…

0