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

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

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

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

/*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',               ...

/*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',               ...

/*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',               ...

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

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

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...

/*Operational groom settings*/ USE OperationsManager SELECT ObjectName, DaysToKeep AS Days FROM  PartitionAndGroomingSettings ORDER BY ObjectName Back to SQL queries main menu

/*Data Warehouse retention settings*/ USE OperationsManagerDW SELECT Dataset.DatasetDefaultName AS Dataset, StandardDatasetAggregation.MaxDataAgeDays AS Days,                CASE StandardDatasetAggregation...

Here are some useful scripts to check and update AD Integration settings using the Command Shell.  Thanks to Lincoln Atkinson for helping me figure this one out. Check current Ldap query get-managementserver | where {$_.name -eq " fqdn "...

This will set a failover Management Server for a Gateway role, leaving the currently configured primary Management Server. Beware of word wrap on the last two lines (should be one line). $gw = get-GatewayManagementServer | where {$_.Name -eq " gw_fqdn...