TSQL – Monitor to check number of records every hour in a given table


-- This monitor collects 6 hours of data collected in mySchema.myTable table. Verifies latest record count with average record count to identify possible data collection error.
-- This monitor is designed to execute on Azure Database
-- 
-- Monitor Created on 11/04 - Vijred 

DECLARE @debugMode bit  
DECLARE @errorThreshold int 
DECLARE @startTime datetime
DECLARE @endTime datetime
DECLARE @avgCount INT
DECLARE @latestCount INT
DECLARE @Hours4Avg INT

SET @debugMode = 0
SET @errorThreshold = 90
SET @Hours4Avg = 6

SET @Hours4Avg = @Hours4Avg * -1 - 1
SET @startTime  = dateadd(hh,@Hours4Avg, dateadd(hour, datediff(hour, 0, getdate()), 0))
SET @endTime = dateadd(hh,-1, dateadd(hour, datediff(hour, 0, getdate()), 0))

if(@debugMode=1)
BEGIN
	select @startTime as startTime, @endTime as endTime
END 

select datepart(mm,EndDateTime) as [Month], datepart(dd,EndDateTime) as [Date], datepart(hh,EndDateTime) as [Hour], count(*) as [Number_of_Records] -- , count(*)/240 as [Approx_Number_of_Database] -- 240 records an hour 
into #temp1 
FROM mySchema.myTable AS drs WITH (nolock) 
where EndDateTime >= @startTime and EndDateTime < @endTime
group by datepart(yy,EndDateTime), datepart(mm,EndDateTime), datepart(dd,EndDateTime), datepart(hh,EndDateTime)
ORDER BY 1,2,3,4

select @avgCount=avg(Number_of_Records) from #temp1
select TOP 1 @latestCount=Number_of_Records from #temp1 order by [Month] DESC, [Date] DESC, [Hour] DESC 

if(@debugMode=1)
BEGIN
	select * from #temp1 ORDER BY 1, 2, 3
END 

if(@debugMode=1)
BEGIN
	select @avgCount as avgCount, @latestCount as LatestCount 
END 

-- This command is very useful in Azure as typucal OBJECTID does not work
drop table if exists #temp1

if(@latestCount < (@avgCount / 100) * @errorThreshold )
	SELECT 1 as DataCollectionproblemIdentified
	ELSE
		SELECT 0 as DataCollectionproblemIdentified

--
Advertisements
This entry was posted in Azure, SQL, SQL Query, Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s