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!