May 7, 2010 at 3:22 am
A system records a log record each time an email is sent by a user. We want to raise an alert when the number of messages @m sent by any user rises above a certain threshold in the last @n minutes.
I thought the above was clear....
Each time an email is sent a log record is written for the user ID - If a particular user starts sending an abnormal number of emails then the number of records added to log table in the last @m (say 60) minutes for instance will rise above a certain number @n (say 100) so we need a sort of moveing aggregate to anable an alert to be fired - how can this be done
May 7, 2010 at 3:28 am
So, what is your question again?
-- Gianluca Sartori
May 7, 2010 at 11:49 am
How does the table structure looks like (including sample data)?
What do you consider as a "sent message"?
Example: I send a mail to 10 people using a single mail and miltiple recipients. 30 min later I forward the original mail to another 10 persons. This time I use 10 separate mails. What would be your expected count (1, 2, 11, or 20?)
Also, what have you tried so far?
May 12, 2010 at 3:15 am
No sample data, no attempts to solve so far. This is still at the theoretical stage - essentially how do you calculate a moving (sliding) period aggregate !
May 12, 2010 at 10:08 am
samtrenchard (5/12/2010)
No sample data, no attempts to solve so far. This is still at the theoretical stage - essentially how do you calculate a moving (sliding) period aggregate !
Answering using a theoretical approach:
Step1: get the requirements together including when you actually want to raise the alert: event based (as soon as each mail is sent), scheduled (as a job), on request a.s.o.
Step 2: Check if your requirements are as complete as you need it to suggest a solution (e.g. by asking questions like I did before).
Step 3: add up all your requirements and see which tool would fit best: trigger, scheduled job, view, ServiceBroker, ...
Step 4: if you get stuck coding your solution get back here.
Right now there is just not enough information available to suggest anything more detailed.
May 13, 2010 at 7:16 am
I don't think you need a 'moving aggregate'. You simply need to count emails per user for a period:
select userid, count(*)
from emaillog
where sendtime less than 60 minutes ago
group by userid
having count(*) > your limit
Note that you will need some method to know that you have sent an alert for this user already or you might start spamming yourself with alerts! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply