Root Management Server Unavailable History with Reason

/* RMS Unavailable History with Reason http://blogs.technet.com/b/jonathanalmquist/ 04-19-2011 Updated 04-20-2011 with outage durations dd:hh:mm */ SELECT ME.Path, HSO.StartDateTime AS OutageStartDateTime, CAST (DATEDIFF(SECOND, HSO.StartDateTime, HSO.EndDateTime)/86400 AS VARCHAR(5)) + ‘:’ + CONVERT(CHAR(5), DATEADD(SECOND, DATEDIFF(SECOND, HSO.StartDateTime, HSO.EndDateTime), ”), 114) AS ‘Duration (dd:hh:mm)’, HSO.ReasonCode, DS.Name AS ReasonString FROM vManagedEntity AS ME INNER JOIN vHealthServiceOutage AS HSO ON HSO.ManagedEntityRowId =…

2

Inventory and Configuration Reports

Someone asked today if there was a way to generate a report displaying inventory/configuration of SQL Server.  Chris Harris, SCOM Program Manager, answered the question with one of our built-in configuration reports.  He was so kind as to provide screenshots and everything, and indicated that someone should blog it.  So, here you go.  Thanks, Chris! …

3

Report Dataset Samples

Putting this list of reporting dataset samples out here for your custom reporting needs or just to paste in for general purpose queries to the Data Warehouse.  I’ll update this list when I have new datasets to share. Updated: 09-23-2011 ·        Top alert generating monitors (tuning) http://blogs.technet.com/b/jonathanalmquist/archive/2011/04/09/top-monitor-generated-alerts-report-dataset.aspx ·        Top alert generating rules (tuning) http://blogs.technet.com/b/jonathanalmquist/archive/2011/04/08/top-rule-generated-alerts-report-dataset.aspx ·       …

4

Number of Events Collected Per Day (Report Dataset)

/*Number of events collected per day (trending) Jonathan Almquist (http://blogs.technet.com/b/jonathanalmquist/) 04-08-2011 */   DECLARE @RowCount AS INT, @MGID AS INT, @TimeZoneOffset AS INT, @OffSetDays AS INT, @StartDate AS DATE, @EndDate AS DATE SET @RowCount = 20 SET @MGID = 1 SET @TimeZoneOffset = 5 SET @OffSetDays = 60 SET @StartDate = DATEADD(hour, @TimeZoneOffset, DATEADD(day, -@OffSetDays,…


Number of State Change Events Per Day (Report Dataset)

/*Number of state change events per day (trending) Jonathan Almquist (http://blogs.technet.com/b/jonathanalmquist/) 04-08-2011 */   DECLARE @RowCount AS INT, @MGID AS INT, @TimeZoneOffset AS INT, @OffSetDays AS INT, @StartDate AS DATE, @EndDate AS DATE SET @RowCount = 20 SET @MGID = 1 SET @TimeZoneOffset = 5 SET @OffSetDays = 60 SET @StartDate = DATEADD(hour, @TimeZoneOffset, DATEADD(day,…


Number of Performance Samples Per Day (Report Dataset)

/*Number of performance samples per day (trending) Jonathan Almquist (http://blogs.technet.com/b/jonathanalmquist/) 04-08-2011 */   DECLARE @RowCount AS INT, @MGID AS INT, @TimeZoneOffset AS INT, @OffSetDays AS INT, @StartDate AS DATE, @EndDate AS DATE SET @RowCount = 20 SET @MGID = 1 SET @TimeZoneOffset = 5 SET @OffSetDays = 60 SET @StartDate = DATEADD(hour, @TimeZoneOffset, DATEADD(day, -@OffSetDays,…


Top Performance Samples Collected (Report Dataset)

/*Top performance samples collected Jonathan Almquist (http://blogs.technet.com/b/jonathanalmquist/) 04-08-2011 */   DECLARE @RowCount AS INT, @MGID AS INT, @TimeZoneOffset AS INT, @OffSetDays AS INT, @StartDate AS DATE, @EndDate AS DATE SET @RowCount = 20 SET @MGID = 1 SET @TimeZoneOffset = 5 SET @OffSetDays = 60 SET @StartDate = DATEADD(hour, @TimeZoneOffset, DATEADD(day, -@OffSetDays, GETDATE())) SET @EndDate…


Top Active Repeating Alerts (Report Dataset)

/*Top active repeating alerts Jonathan Almquist (http://blogs.technet.com/b/jonathanalmquist/) 04-08-2011 */   DECLARE @RowCount AS INT, @MGID AS INT, @TimeZoneOffset AS INT, @OffSetDays AS INT, @StartDate AS DATE, @EndDate AS DATE SET @RowCount = 20 SET @MGID = 1 SET @TimeZoneOffset = 5 SET @OffSetDays = 60 SET @StartDate = DATEADD(hour, @TimeZoneOffset, DATEADD(day, -@OffSetDays, GETDATE())) SET @EndDate…


Top events collected (Report Dataset)

/*Top events collected Jonathan Almquist (http://blogs.technet.com/b/jonathanalmquist/) 04-08-2011 */   DECLARE @RowCount AS INT, @MGID AS INT, @TimeZoneOffset AS INT, @OffSetDays AS INT, @StartDate AS DATE, @EndDate AS DATE SET @RowCount = 20 SET @MGID = 1 SET @TimeZoneOffset = 5 SET @OffSetDays = 60 SET @StartDate = DATEADD(hour, @TimeZoneOffset, DATEADD(day, -@OffSetDays, GETDATE())) SET @EndDate =…


Top Monitor Generated Alerts (Report Dataset)

/*Top monitor generated alerts Jonathan Almquist (http://blogs.technet.com/b/jonathanalmquist/) 04-08-2011 */   DECLARE @RowCount AS INT, @MGID AS INT, @TimeZoneOffset AS INT, @OffSetDays AS INT, @StartDate AS DATE, @EndDate AS DATE SET @RowCount = 20 SET @MGID = 1 SET @TimeZoneOffset = 5 SET @OffSetDays = 60 SET @StartDate = DATEADD(hour, @TimeZoneOffset, DATEADD(day, -@OffSetDays, GETDATE())) SET @EndDate…