Notify Me When Something Doesn’t Happen

Notify Me When Something Doesn’t Happen

by JBrooks 7. January 2011 14:24

I have 10 clients that each have a process that posts a value to my web service every hour at three minutes after the hour.  I needed to know when a post doesn’t happen for a client. 

The logic of the SQL is that I want to return the last row posted for each Client ID when that last row’s post time is older than 60 minutes (plus 2 minutes for variations).  And I only care if it has happened in the last 4 days.  This is the SQL:

 
declare @intervalMinutes int,
@variationMinutes int,
@activeDays int
 
set @intervalMinutes = 60
set @variationMinutes = 2
set @activeDays = 4
 
 
select 'For Client '+cast(clientId as varchar)+
    ' Last Posting is too old: '+ 
    convert(varchar(5),p.postTime,1)+ ' '+convert(varchar(5),p.postTime,108) 
    as Message,
'Last status was: '+
    status as Details
from Postings p
where p.postTime = 
    (select max(pIn.postTime) 
    from Postings pIn
    where pIn.clientId = p.clientId)
and p.postTime  between 
    dateadd(day,-@activeDays,getdate()) 
    and dateadd(mi,-@intervalMinutes - @variationMinutes,getdate()) 
order by p.postTime
 

So this could return a table that looks like:

image

I now only get notified if there last posting is more than 62 minutes old.  I previously blogged about how to have rows like these show up in an RSS Feed HERE.  The good thing about an RSS Feed is that you can have it on your phone, Outlook and web browser.

Tags: