Query for SQL uptime?

wullsy

Hi,

I'd like to create an uptimeavailability report.
What I'd like is similar to what the front end captures i.e.
How long has SQL Monitor been able to connect to the instance?
I know I can capture how long the server or instance has been up however, ideally I'd like to know how long it's been accessible over the network.
Does anyone have some sample t-sql I can use to pull this information out of SQL Monitor db?

Dan B

wullsy,

I believe what you require can be implemented using the below script. Please try it out and let me know if you need anything further.

The uptime is based on SQL Monitor's ability to connect to the monitored entity, and also to collect data. The percentage and downtime measures are based on the times that SQL Monitor was attempting to connect, so the percentages and minutes will be accurate, but only cover the times SQL Monitor was instructed to monitor the server.

Eg: You monitor an instance for an hour, and it is online throughout this hour. If you then ask: "What is the uptime for this instance for the last week?", this script will report 100%, with 0 minutes uptime, even if there has been downtime the day before.

DECLARE @startDateUtc BIGINT;
DECLARE @endDateUtc BIGINT;

-- SET THESE THINGS --
SET @startDateUtc = utils.DateTimeToTicks('2014-01-06');
SET @endDateUtc = utils.DateTimeToTicks('2014-01-07');
-- END SET THESE THINGS --

;WITH CteData AS (
	SELECT
		'Cluster' [Level] , 
		ROW_NUMBER() OVER (PARTITION BY Id ORDER BY CollectionDate ASC) [row],
		[Id] ,
		[Cluster_Name] [Cluster Name],
		'' [Node/SQL Server Instance Name],
		[Cluster_MonitoredEntityState] [MonitoredEntityState],
		[Cluster_MonitoringStatusCategory] [MonitoringStatusCategory] ,
		[CollectionDate]
	FROM [data].[Cluster_StableSamples_View]
	UNION ALL
	SELECT  'Node' ,
			ROW_NUMBER() OVER (PARTITION BY Id ORDER BY CollectionDate ASC) [row],
			Id ,
			Cluster_Name ,
	        Cluster_Machine_Name ,
	        Cluster_Machine_MonitoredEntityState ,
	        Cluster_Machine_MonitoringStatusCategory ,
	        CollectionDate 
	FROM [data].[Cluster_Machine_StableSamples_View]
	WHERE Cluster_Machine_Name <> ''
	UNION ALL 
	SELECT  'SqlServer' ,
			ROW_NUMBER() OVER (PARTITION BY Id ORDER BY CollectionDate ASC) [row],
			Id ,
			Cluster_Name ,
	        Cluster_SqlServer_Name ,
	        Cluster_SqlServer_MonitoredEntityState ,
	        Cluster_SqlServer_MonitoringStatusCategory ,
	        CollectionDate 
	FROM data.Cluster_SqlServer_StableSamples_View
), CteTicks AS (
	SELECT 
		aft.[Id] ,
		aft.[Level] ,
		aft.[Cluster Name] ,
		aft.[Node/SQL Server Instance Name] , 
		CASE 
			WHEN aft.CollectionDate < @startDateUtc THEN 0
			WHEN aft.CollectionDate < @endDateUtc AND bef.CollectionDate IS NULL THEN aft.CollectionDate - @startDateUtc
			WHEN aft.CollectionDate < @endDateUtc AND bef.CollectionDate < @startDateUtc THEN aft.CollectionDate - @startDateUtc
			WHEN aft.CollectionDate < @endDateUtc THEN aft.CollectionDate - bef.CollectionDate
			WHEN bef.CollectionDate IS NULL THEN @endDateUtc - @startDateUtc
			WHEN bef.CollectionDate < @startDateUtc THEN @endDateUtc - @startDateUtc
			WHEN bef.CollectionDate < @endDateUtc THEN @endDateUtc - bef.CollectionDate
			ELSE 0
		END [ticks],
		aft.[MonitoredEntityState] ,
		aft.[MonitoringStatusCategory] 
	FROM CteData aft
	LEFT OUTER JOIN CteData bef ON aft.Id = bef.Id AND aft.row = bef.row + 1
)
SELECT [Level], 
       [Cluster Name], 
	   [Node/SQL Server Instance Name] , 
	   100.0 * SUM(CASE WHEN [MonitoringStatusCategory] IS NULL OR [MonitoringStatusCategory] < 300 THEN ticks ELSE 0 END)/SUM(ticks) [% Uptime],
	   1.0/600000000 * SUM(CASE WHEN [MonitoringStatusCategory] IS NOT NULL AND [MonitoringStatusCategory] >= 300 THEN ticks ELSE 0 END) [Monitored Downtime (minutes)]
FROM CteTicks
WHERE [MonitoredEntityState] = 3 
GROUP BY Id, [Level], [Cluster Name], [Node/SQL Server Instance Name]
HAVING SUM(ticks) > 0
ORDER BY [Cluster Name], [Level], [Node/SQL Server Instance Name]

wullsy

Thanks for that! Very useful.

I've got a whole bunch of small outages that don't appear to show.
I would have thought that it might have shown 0.50000000000 rather than 0.00000000000 for downtime minutes for the example below.

SQL Server instance unreachable from: 6 Oct 2015 10:03:22 PM
SQL Server became reachable again at: 6 Oct 2015 10:03:54 PM

It's only 30 seconds so not the end of the world.

Dan B

wullsy,

Did you edit the date range at the top of the script?

-- SET THESE THINGS --
SET @startDateUtc = utils.DateTimeToTicks('2014-01-06');
SET @endDateUtc = utils.DateTimeToTicks('2014-01-07');
-- END SET THESE THINGS --

This should be edited to show the date range you would like to report on. This should be accurate to 5 seconds. Let me know how you get on when you try this.

Regards

Dan Bainbridge

SatyaT

Hello

Is the query mentioned in the earlier comment still valid? I am getting a lot of syntax errors when I try to execute the query.

mdspain

Regarding the query provided, seems there are some HTML artifacts in the code.  Do you have a clean version of this script.  ?