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.

 

<?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.

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!

Parents
  • I made a small tweak to the part where it checks to see if maintenance mode was enabled or not. 

    USE SQLdmRepository
    
    DECLARE @InstanceName nvarchar(256) = 'LEE'
    DECLARE @MetricName nvarchar(128) = 'Server Uptime'
    DECLARE @StartDateTime datetime = '2021-09-17 00:00:00'
    DECLARE @EndDateTime datetime = '2021-09-17 23:59:59'
    DECLARE @timeZoneDifference int = -5
    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) OR (@DisabledMMDT IS NULL AND @EnabledMMDT IS NOT NULL))
    			SELECT 'Maintenance mode was enabled prior to the end time which may affect the accuracy of the reported uptime value.' AS [NOTE]
    		DROP TABLE #UptimeRawData
    	END
    
    
    
    

Reply
  • I made a small tweak to the part where it checks to see if maintenance mode was enabled or not. 

    USE SQLdmRepository
    
    DECLARE @InstanceName nvarchar(256) = 'LEE'
    DECLARE @MetricName nvarchar(128) = 'Server Uptime'
    DECLARE @StartDateTime datetime = '2021-09-17 00:00:00'
    DECLARE @EndDateTime datetime = '2021-09-17 23:59:59'
    DECLARE @timeZoneDifference int = -5
    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) OR (@DisabledMMDT IS NULL AND @EnabledMMDT IS NOT NULL))
    			SELECT 'Maintenance mode was enabled prior to the end time which may affect the accuracy of the reported uptime value.' AS [NOTE]
    		DROP TABLE #UptimeRawData
    	END
    
    
    
    

Children
No Data