So, maybe it's not a total improvement, but I thought it would be worth sharing. The following SQL commands are designed to create a new, indexed table, and populate that database with Exchange Availability reporting data from the MOM System Center Reporting database in SQL Server (see previous post on the current availability report).
Unlike the SQL Reporting Services, this is a Query Analyzer solution. You'll need to wrap the appropriate presentation layer around it. The final step in the code is to write a stored procedure that accepts two parameters, start date and end date. It returns total uptime for all Exchange servers in the environment, as well as some statistics for the report in general (as a separate recordset).
As is typical, this code is for illustrative purposes only. What you might find interesting, however, is the complexity of the data in the SystemCenterReporting database, and the requirements to massage that data into a usable format. If you try this an run into any issues, let me know about them.
CREATE TABLE EXCHANGEAVAILABILITY(ComputerName VARCHAR(255) NOT NULL, Message VARCHAR(1000) NOT NULL, NTEventID INT NOT NULL, ServerWhereLogged VARCHAR(255) NOT NULL, Source VARCHAR(255) NOT NULL, TimeGenerated DATETIME NOT NULL, TimeStored DATETIME NOT NULL, UserName VARCHAR(255) NOT NULL, ComputerDomain VARCHAR(255) NOT NULL, DomainWhereLogged VARCHAR(255) NOT NULL)
GO
CREATE INDEX [IX_TimeGenerated] ON [dbo].[EXCHANGEAVAILABILITY]([TimeGenerated]) ON [PRIMARY]
CREATE INDEX [IX_ComputerName] ON [dbo].[EXCHANGEAVAILABILITY]([ComputerName]) ON [PRIMARY]
CREATE INDEX [IX_EventID] ON [dbo].[EXCHANGEAVAILABILITY]([NTEventID]) ON [PRIMARY]
INSERT ExchangeAvailabilitySELECT [ComputerName] , [Message] , [NTEventID] , [ServerWhereLogged] , [Source] , [TimeGenerated] , [TimeStored] , [UserName] , [ComputerDomain] , [DomainWhereLogged]FROM [SystemCenterReporting].[dbo].[SDKEventView]WHERE NTEventID IN (9980,9981,9982,9983)
Go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExchangeUptime]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[ExchangeUptime]
CREATE PROCEDURE dbo.ExchangeUptime @StartDate DATETIME , @EndDate DATETIMEAS
DECLARE @ExpectedCounters INT DECLARE @ActualCounters INT DECLARE @SERVERCT INT
SELECT @SERVERCT = Count(Distinct ComputerName) FROM EXCHANGEAVAILABILITYSET @ExpectedCounters = (CONVERT(INT,@EndDate) - CONVERT(INT,@StartDate)) * 288 * @ServerCtSELECT @ActualCounters = COUNT(NTEventID) FROM EXCHANGEAVAILABILITY WHERE TimeGenerated BETWEEN @StartDate AND @EndDate
-- Build a temporary table to limit resultsCREATE TABLE #DRange ( ComputerName VARCHAR(50) , E9980 INT , E9981 INT , E9982 INT , E9983 INT )
CREATE TABLE #Report ( ComputerName VARCHAR(50) , E9980 INT , E9981 INT , E9982 INT , E9983 INT )
INSERT #DRangeSELECT ComputerName , CASE NTEventID WHEN 9980 THEN -- SUCCESS COUNT(NTEventID) ELSE 0 END AS TotalUptime , CASE NTEventID WHEN 9981 THEN -- DC Unavailable COUNT(NTEventID) ELSE 0 END AS DCDown , CASE NTEventID WHEN 9982 THEN -- Exchange Down COUNT(NTEventID) ELSE 0 END AS ExchangeDown , CASE NTEventID WHEN 9983 THEN -- Other Issues COUNT(NTEventID) ELSE 0 END AS OtherDown FROM EXCHANGEAVAILABILITYWHERE TimeGenerated BETWEEN @StartDate AND @EndDateGROUP BY ComputerName, NTEventIDORDER BY ComputerName
INSERT #ReportSELECT ComputerName , SUM(E9980) AS E9980 , SUM(E9981) AS E9981 , SUM(E9982) AS E9982 , SUM(E9983) AS E9983FROM #DRangeGROUP BY ComputerName
SELECT ComputerName , CONVERT(DECIMAL(18,6),E9980)/(CONVERT(DECIMAL(18,6),@ActualCounters)/CONVERT(DECIMAL(18,6),@ServerCt)) AS SystemUptime , 1 - (CONVERT(DECIMAL(18,6),E9981)/(CONVERT(DECIMAL(18,6),@ActualCounters)/CONVERT(DECIMAL(18,6),@ServerCt))) AS DCUptime , 1 - (CONVERT(DECIMAL(18,6),E9982)/(CONVERT(DECIMAL(18,6),@ActualCounters)/CONVERT(DECIMAL(18,6),@ServerCt))) AS EXUptime , 1 - (CONVERT(DECIMAL(18,6),E9983)/(CONVERT(DECIMAL(18,6),@ActualCounters)/CONVERT(DECIMAL(18,6),@ServerCt))) AS DependsUptimeFROM #ReportUNIONSELECT 'All Systems' AS ComputerName , AVG(CONVERT(DECIMAL(18,6),E9980)/(CONVERT(DECIMAL(18,6),@ActualCounters))) AS SystemUptime , AVG(1 - (CONVERT(DECIMAL(18,6),E9981)/(CONVERT(DECIMAL(18,6),@ActualCounters)))) AS DCUptime , AVG(1 - (CONVERT(DECIMAL(18,6),E9982)/(CONVERT(DECIMAL(18,6),@ActualCounters)))) AS EXUptime , AVG(1 - (CONVERT(DECIMAL(18,6),E9983)/(CONVERT(DECIMAL(18,6),@ActualCounters)))) AS DependsUptimeFROM #Report
SELECT @ExpectedCounters AS EXPECTED , @ActualCounters AS ACTUAL , CONVERT(DECIMAL(18,6),@ActualCounters)/CONVERT(DECIMAL(18,6),@ExpectedCounters) AS Confidence , @ServerCt AS Servers , MIN(TimeGenerated) AS FirstEvent , MAX(TimeGenerated) AS LastEventFROM EXCHANGEAVAILABILITYWHERE TimeGenerated BETWEEN @StartDate AND @EndDate
DROP TABLE #DRangeDROP TABLE #Report