Return all dirty aggregations in last X days

/*Return all dirty aggregations in last X days*/ DECLARE @Days AS int SET @Days = 1 –number of days to go back SELECT DS.DatasetDefaultName, CASE SDAH.AggregationTypeId WHEN 0 THEN ‘Raw’ WHEN 20 THEN ‘Hourly’ WHEN 30 THEN ‘Daily’ ELSE ‘Unknown’ END AS Type, SDAH.AggregationDateTime, DATEADD(HH, (datediff(HH, getutcdate(), getdate())), SDAH.AggregationDateTime) AS [LocalTime], SDAH.DirtyInd, SDAH.AggregationCount FROM Dataset…


Count dirty aggregations in last X days

/*Count dirty aggregations in last X days*/ DECLARE @Days AS int SET @Days = 1 –number of days to go back SELECT Count(*) AS [Failed], DS.DatasetDefaultName AS [Dataset], CASE SDAH.AggregationTypeId WHEN 0 THEN ‘Raw’ WHEN 20 THEN ‘Hourly’ WHEN 30 THEN ‘Daily’ ELSE ‘Unknown’ END AS [Type] FROM Dataset AS DS INNER JOIN StandardDatasetAggregationHistory AS…


Group members (DW)

/* Return Health Service instances hosting one or more instances contained in */ USE OperationsManagerDW SELECT vManagedEntity.DisplayName AS Computer FROM  vManagedEntity INNER JOIN                vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId WHERE (vManagedEntity.TopLevelHostManagedEntityRowId IN                    (SELECT DISTINCT ME1.TopLevelHostManagedEntityRowId                     FROM   vManagedEntity AS ME2 INNER JOIN                                    vRelationship ON ME2.ManagedEntityRowId = vRelationship.SourceManagedEntityRowId INNER JOIN                                    vManagedEntity AS…


All groups (DW)

Thanks to Daniel Savage for helping with this one. /* Get all groups from Data Warehouse */ USE OperationsManagerDW SELECT DISTINCT DisplayName FROM  vManagedEntity WHERE (ManagedEntityTypeRowId IN                    (SELECT ManagedEntityTypeRowId                     FROM   dbo.ManagedEntityDerivedTypeHierarchy                                        ((SELECT ManagedEntityTypeRowId                                          FROM   vManagedEntityType                                          WHERE (ManagedEntityTypeSystemName = ‘’)), 0)))   Back to SQL queries main menu


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’,…


Operational groom settings

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


DW Retention and Groom Settings

/*Data Warehouse retention settings*/ USE OperationsManagerDW SELECT Dataset.DatasetDefaultName AS Dataset, StandardDatasetAggregation.MaxDataAgeDays AS Days,                CASE StandardDatasetAggregation.AggregationTypeId WHEN 0 THEN ‘Raw’ WHEN 20 THEN ‘Hourly’ WHEN 30 THEN ‘Daily’ ELSE ‘Unknown’ END AS Type,                StandardDatasetAggregation.AggregationIntervalDurationMinutes AS AggInterval, StandardDatasetAggregation.GroomingIntervalMinutes AS GroomInterval,                StandardDatasetAggregation.MaxRowsToGroom, StandardDatasetAggregation.BuildAggregationStoredProcedureName AS SPROC FROM  Dataset INNER JOIN                StandardDatasetAggregation ON Dataset.DatasetId = StandardDatasetAggregation.DatasetId…


All disk sizes (GB)

/*Get each logical disk size, for each agent computer, by OS version. This helps in calculating the Logical Disk Free Space Monitor from my earlier post.  You can copy results into Excel, sort by system and non-system drives, and perform an average disk size formula.  Then plug Min, Max and Avg sizes into my Logical…


Operations Manager 2007 SQL Queries

Sharing this list of random SQL queries I have found useful on different occasions.  Refer to report dataset samples page for SQL queries you can use for custom reporting needs. Updated: 08-05-2011 OperationsManager OperationsManagerDW All groups DW Retention and Groom Settings All groups and their contained instances Discovery Check All disk sizes (GB) All groups…


All groups and their contained instances

SELECT     SourceMonitoringObjectDisplayName AS ‘Group’, TargetMonitoringObjectDisplayName AS ‘Member’ FROM         RelationshipGenericView WHERE     (SourceMonitoringObjectDisplayName IN                           (SELECT     ManagedEntityGenericView.DisplayName                             FROM          ManagedEntityGenericView INNER JOIN                                                        (SELECT     BaseManagedEntityId                                                          FROM          BaseManagedEntity WITH (NOLOCK)                                                          WHERE      (BaseManagedEntityId = TopLevelHostEntityId) AND (BaseManagedEntityId NOT IN                                                                                     (SELECT     R.TargetEntityId                                                                                       FROM          Relationship AS R WITH (NOLOCK) INNER JOIN                                                                                                              dbo.fn_ContainmentRelationshipTypes() AS CRT ON R.RelationshipTypeId…