Command Shell Examples
Useful SQL Queries
Browse by Tags - Jonathan Almquist on Operations Manager - Site Home - TechNet Blogs

Browse by Tags

Related Posts
  • Blog Post: 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...
  • Blog Post: All groups

    SELECT     ManagedEntityGenericView.DisplayName, ManagedEntityGenericView.FullName FROM         ManagedEntityGenericView INNER JOIN                          ...
  • Blog Post: 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...
  • Blog Post: All groups and their contained instances

    SELECT     SourceMonitoringObjectDisplayName AS 'Group', TargetMonitoringObjectDisplayName AS 'Member' FROM         RelationshipGenericView WHERE     (SourceMonitoringObjectDisplayName IN                          ...
  • Blog Post: 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...
  • Blog Post: 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                   ...
  • Blog Post: 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...
  • Blog Post: Operational groom settings

    /*Operational groom settings*/ USE OperationsManager SELECT ObjectName, DaysToKeep AS Days FROM  PartitionAndGroomingSettings ORDER BY ObjectName Back to SQL queries main menu
  • Blog Post: Group members (DW)

    /* Return Health Service instances hosting one or more instances contained in system.group */ USE OperationsManagerDW SELECT vManagedEntity.DisplayName AS Computer FROM  vManagedEntity INNER JOIN                vManagedEntityType...
  • Blog Post: 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...
  • Blog Post: 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...