Uptime Report

I've been working on a little project on my own that I thought I would share with the public. The project is essentially creating a report that would generally display how long a server had been up between two different times. In other words, an uptime report. I know that there's a report that's currently built into SQL Diagnostic Manager so I decided to go a different route. For my report, it was going to be based on a custom counter that I would create. The custom counter would simply collect the number of minutes since the SQL Server instance has started. I've attached the custom counter below.

Server Uptime.xml

<?xml version="1.0"?><CustomCounterSerializable xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><MetricDefinition><MetricID>1002</MetricID><IsDeleted>false</IsDeleted><LastChanged>2021-01-07T22:21:29.22</LastChanged><Options>NumericValue OKDisabled AdditionalData CalculateMaxValue</Options><MetricClass>ServerThreshold</MetricClass><ComparisonType>LE</ComparisonType><DefaultMessageID>0</DefaultMessageID><Rank>50</Rank><ProcessNotifications>true</ProcessNotifications><AlertEnabledByDefault>true</AlertEnabledByDefault><Name>Server Uptime</Name><Description /><MinValue>0</MinValue><MaxValue>999999999999999</MaxValue><DefaultInfoThresholdValue>360</DefaultInfoThresholdValue><DefaultWarningThresholdValue>120</DefaultWarningThresholdValue><DefaultCriticalThresholdValue>30</DefaultCriticalThresholdValue><MetricCategory>Custom</MetricCategory><EventCategoryInt>3</EventCategoryInt></MetricDefinition><MetricDescription><Name>Server Uptime</Name><Category>Custom</Category><Description /><Rank>50</Rank></MetricDescription><CounterDefinition><MetricID>1002</MetricID><IsEnabled>true</IsEnabled><MetricType>SQLStatement</MetricType><LastChanged>2021-01-07T22:21:29.22</LastChanged><CalculationType>Value</CalculationType><SqlStatement>SELECT DATEDIFF(mi,sqlserver_start_time,GETDATE()) FROM sys.dm_os_sys_info</SqlStatement><Scale>1</Scale></CounterDefinition></CustomCounterSerializable>

After creating the custom counter, I then link the custom counter to the instances that I'm monitoring with SQL Diagnostic Manager. At that point, I just let SQL Diagnostic Manager do its thing. After some time has passed, I then tried to create my own T-SQL script which would query the SQLdm Repository Database to give me a general uptime report. Before I show you the script, I want to point out that I'm not exactly fluent with T-SQL. I'm pretty sure things could have been written better and I'm hoping some of you will help improve the script. With that out of the way, here's my script.

5734.SQLDM - Uptime Report.sql

USE SQLdmRepository

DECLARE @InstanceName nvarchar(256) = 'KIBA\SQL2017'
DECLARE @MetricName nvarchar(128) = 'Server Uptime'
DECLARE @StartDateTime datetime = '2021-01-28 00:00:00'
DECLARE @EndDateTime datetime = '2021-01-28 17:59:59'
DECLARE @timeZoneDifference int = -6
DECLARE @SQLServerID int = (SELECT SQLServerID FROM MonitoredSQLServers WHERE [email protected])
DECLARE @MetricID int = (SELECT Metric FROM MetricInfo WHERE [email protected])

IF @StartDateTime > @EndDateTime
	PRINT 'StartDateTime must be earlier than EndDateTime'
ELSE 
	BEGIN

		IF @EndDateTime > GETDATE()
			SET @EndDateTime = GETDATE()

		IF OBJECT_ID('tempdb.dbo.#UptimeRawData', 'U') IS NOT NULL
		DROP TABLE #UptimeRawData

		CREATE TABLE #UptimeRawData ([UTCCollectionDateTime] datetime,[LocalCollectionDateTime] datetime, [RawValue] decimal, [Processed] bit default 0)
		INSERT INTO #UptimeRawData SELECT UTCCollectionDateTime,DATEADD(HOUR,@timeZoneDifference,UTCCollectionDateTime) AS [LocalCollectionDateTime], [RawValue], '0' FROM [CustomCounterStatistics] (nolock) WHERE [email protected] and [email protected] AND UTCCollectionDateTime BETWEEN DATEADD(HOUR,[email protected],@StartDateTime) and DATEADD(HOUR,[email protected],@EndDateTime) AND [RawValue] IS NOT NULL

		DECLARE @valid int = 0
		DECLARE @total int = 0
		DECLARE @value int = (SELECT TOP 1 RawValue FROM #UptimeRawData ORDER BY LocalCollectionDateTime ASC)
		UPDATE #UptimeRawData SET Processed=1 WHERE LocalCollectionDateTime = (SELECT TOP 1 LocalCollectionDateTime FROM #UptimeRawData WHERE Processed=0 ORDER BY LocalCollectionDateTime ASC)

		WHILE EXISTS (SELECT Processed FROM #UptimeRawData WHERE Processed=0)
			BEGIN
			IF @value < (SELECT TOP 1 RawValue FROM #UptimeRawData WHERE Processed=0 ORDER BY LocalCollectionDateTime ASC)
				BEGIN
				SET @value = (SELECT TOP 1 RawValue FROM #UptimeRawData WHERE Processed=0 ORDER BY LocalCollectionDateTime ASC)
				UPDATE #UptimeRawData SET Processed=1 WHERE LocalCollectionDateTime = (SELECT TOP 1 LocalCollectionDateTime FROM #UptimeRawData WHERE Processed=0 ORDER BY LocalCollectionDateTime ASC)
				END
			ELSE
				BEGIN
				IF @value > DATEDIFF(mi,@StartDateTime,(SELECT TOP 1 LocalCollectionDateTime FROM #UptimeRawData WHERE Processed=1 ORDER BY LocalCollectionDateTime DESC))
					BEGIN
					SET @value = DATEDIFF(mi,@StartDateTime,(SELECT TOP 1 LocalCollectionDateTime FROM #UptimeRawData WHERE Processed=1 ORDER BY LocalCollectionDateTime DESC)) 
					END

				SET @total = @total + @value
				SET @value = (SELECT TOP 1 RawValue FROM #UptimeRawData WHERE Processed=0 ORDER BY LocalCollectionDateTime ASC)
				UPDATE #UptimeRawData SET Processed=1 WHERE LocalCollectionDateTime = (SELECT TOP 1 LocalCollectionDateTime FROM #UptimeRawData WHERE Processed=0 ORDER BY LocalCollectionDateTime ASC)
				END
			END

		SET @total = @total + @value

		If (@total > (DATEDIFF(mi, @StartDateTime, @EndDateTime)))
			BEGIN
				SET @total = (DATEDIFF(mi, @StartDateTime,(SELECT MAX(LocalCollectionDateTime) FROM #UptimeRawData)))
			END

		--SELECT * FROM #UptimeRawData
		--SELECT @value
		--SELECT @total

		--SELECT DATEDIFF(mi, @StartDateTime, @EndDateTime)

		--SELECT DATEDIFF(mi, '2019-10-24 00:00:00', '2019-10-24 24:00:00')

		SELECT @Total AS [Uptime]
			, (DATEDIFF(mi, @StartDateTime, @EndDateTime))[email protected] AS [Downtime (Total Time Range)]
			, (@Total * 1.0 / DATEDIFF(mi, @StartDateTime, @EndDateTime)) * 100.0 AS [Uptime Percentage (Total Time Range)]
			, @EndDateTime AS [End Time]
			, (DATEDIFF(mi, @StartDateTime, (SELECT MAX(LocalCollectionDateTime) FROM #UptimeRawData)))- @Total AS [Downtime (As of Last Collection)]
			, (@Total * 1.0 / DATEDIFF(mi, @StartDateTime, (SELECT MAX(LocalCollectionDateTime) FROM #UptimeRawData))) * 100.0 AS [Uptime Percentage (As of Last Collection)]
			, (SELECT MAX(LocalCollectionDateTime) FROM #UptimeRawData) AS [Last Collection Time]

		DECLARE @EnabledMMDT DateTime
		DECLARE @DisabledMMDT DateTime
		
		SET @EnabledMMDT = (SELECT MAX(DateTime) FROM [AuditableEvents] (nolock) WHERE [email protected] and ActionID IN (6,18) AND DateTime BETWEEN DATEADD(HOUR,[email protected],@StartDateTime) and DATEADD(HOUR,[email protected],@EndDateTime))
		SET @DisabledMMDT = (SELECT MAX(DateTime) FROM [AuditableEvents] (nolock) WHERE [email protected] and ActionID IN (7,19) AND DateTime BETWEEN DATEADD(HOUR,[email protected],@StartDateTime) and DATEADD(HOUR,[email protected],@EndDateTime))

		If (@EnabledMMDT > @DisabledMMDT)
			SELECT 'Maintenance mode was enabled prior to the end time which will affect the accuracy of the reported uptime value.' AS [NOTE]
		DROP TABLE #UptimeRawData
	END

The script itself does require some input. Right now, it takes one instance at a time and reports the uptime statistics between the dates that you supply.

If this piques your interest, please do give it a try and let me know if you have any suggestions on how it could be improved!

TIA!