Improving the Exchange Availability Report

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]

GO

CREATE INDEX [IX_ComputerName]
ON [dbo].[EXCHANGEAVAILABILITY]([ComputerName])
ON [PRIMARY]

GO

CREATE INDEX [IX_EventID]
ON [dbo].[EXCHANGEAVAILABILITY]([NTEventID])
ON [PRIMARY]

GO

INSERT ExchangeAvailability
SELECT [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]

GO

CREATE PROCEDURE dbo.ExchangeUptime
@StartDate DATETIME
, @EndDate DATETIME
AS

DECLARE @ExpectedCounters INT
DECLARE @ActualCounters INT
DECLARE @SERVERCT INT

SELECT @SERVERCT = Count(Distinct ComputerName) FROM EXCHANGEAVAILABILITY
SET @ExpectedCounters = (CONVERT(INT,@EndDate) - CONVERT(INT,@StartDate)) * 288 * @ServerCt
SELECT @ActualCounters = COUNT(NTEventID) FROM EXCHANGEAVAILABILITY WHERE TimeGenerated BETWEEN @StartDate AND @EndDate

-- Build a temporary table to limit results
CREATE 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 #DRange
SELECT 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 EXCHANGEAVAILABILITY
WHERE TimeGenerated BETWEEN @StartDate AND @EndDate
GROUP BY ComputerName, NTEventID
ORDER BY ComputerName

INSERT #Report
SELECT ComputerName
, SUM(E9980) AS E9980
, SUM(E9981) AS E9981
, SUM(E9982) AS E9982
, SUM(E9983) AS E9983
FROM #DRange
GROUP 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 DependsUptime
FROM #Report
UNION
SELECT '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 DependsUptime
FROM #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 LastEvent
FROM EXCHANGEAVAILABILITY
WHERE TimeGenerated BETWEEN @StartDate AND @EndDate

DROP TABLE #DRange
DROP TABLE #Report

GO