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


Comments (0)

Skip to main content