proposed solution to limiting emails

[carteriii]

I believe I've devised a very simple way to limit the number of email alerts by simply adding an additional clause to the filter query. My solution is far from perfect, but I think it provides a reasonable solution for the extreme simplicity of implementation.

Allow me to describe a bit of my thought process. Knowing that all events and alarm frames have a database record with a column for StartTime, I realized that I could easily write a query to return the number of events within a specified time period. In turn, that makes it pretty easy to limit the events that are returned by a filter query that sends emails.

For starters, it should be somewhat obvious to anyone who knows SQL that the query immediately below will return the count of events that have alarmed and have been emailed within the past 10 minutes (MySQL’s “INTERVAL” keyword makes this quite easy and readable):

Code: Select all

SELECT count(*) FROM Events as E2
  WHERE E2.StartTime >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
  and E2.AlarmFrames >= 1  and  E2.Emailed = 1

To limit the filter query that returns events to be emailed, we just need to compare the count of events to some number. In the query below, I’m appending my “SELECT count(*)” to the default filter query to say that I don’t want more than 5 emails about alarms within the past 10 minutes.

Code: Select all

select E.Id,E.MonitorId, …
from Events as E inner join Monitors as M on M.Id = E.MonitorId
where not isnull(E.EndTime)
and ( E.Archived = 0
and E.StartTime >= '2007-12-07 20:48:53'
and E.AlarmFrames >= 1 ) and ( E.Emailed = 1 )
and 5 >= (SELECT count(*) FROM Events as E2
  WHERE E2.StartTime >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
  and E2.AlarmFrames >= 1  and  E2.Emailed = 1 )
order by E.StartTime asc

For what it’s worth, I do include a limitation on the StartTime (December 7th in the sample above) within my email filter query, mostly because that column is indexed so it makes the query a lot faster. I also wouldn’t want ZoneMinder to suddenly send me a mass of emails for old events anyway. If you use a StartTime condition like I do, you need to consider how it might impact the subquery which we’ve just added. Mostly, just make sure the window of times is larger than that of the subquery.

To add this solution/approach as a core feature of the product, I believe ZoneMinder only needs to ask the user for two additional configuration options. The first is the number of events (5 in my example) and the second is the time interval (10 minutes in my example). We could assume the interval is only specified in minutes, such that both options could be simple numeric values (rather than letting the user specify the interval is seconds or hours).

For now, anyone can hard-code this restriction into zm_filter.pl just before the sort column is appended to the query with a line such as:

Code: Select all

If ($filter_data->{AutoEmail})
{
    sql .= “and 5 <= (SELECT count(*) … )”
}

Having said all this, I should offer a bit of explanation for why I say this is far from perfect. An AutoEmail filter doesn’t run immediately after an alarm event is generated, and the execution of the filter query can pick up multiple events to be emailed which are more than the specified maximum. Think of this example. Assume no alarm events have been sent within the specified interval, and assume that the background filters execute once every minute. Within a one-minute period, it is possible to generate 10 separate alarm events. The next time the filter runs, the “select count(*)” part of the query won’t find any alarm events that have been emailed, so all 10 events will then be emailed. The use of Mocord dramatically affects the whole situation, and the pre and post event settings also impact the number of separate events when running Modetect.

The count(*) query can be expanded to join to the Frames table if people want more precision, but I haven’t thought through how that would exactly work since emails are generated and flagged at the level of the Events table.

So as I said, this isn’t perfect, but it’s easy and seems to handle a large number of the situations that users probably wish to cover.

[zoneminder]

Thanks for the considered and comprehensive post. One thing I'm not sure about though is the big picture about what exactly you are trying to do

Are you trying to prevent more than X emails in a particular time frame, and then get those extra sent after. Or only the first X to be sent at all? You may also want to consider setting the Emailed flag on events you never want to be sent if it is the latter.

[carteriii]

Yes, I am trying to prevent more than X emails in a particular time frame on the assumption that a continuous sequence of events are generated by the same general activity. I would not want/need the secondary events to be sent at all, assuming they were generated from the same basic activity. That's a big assumption, but my use of ZoneMinder is not for a business in which that assumption could cause problems. I use ZoneMinder at my house, and when my wife is outside gardening or with our dog running around, I get an almost continuous stream of emails for the duration of the outdoor activity. Once I know my wife or dog are outside, I don't need to receive repeated emails.

In searching the forums for a solution, I found a number of other people asking for what I believe to be similar functionality to limit the number of emails. I have no idea what their situation may be, nor am I certain this would address their situation, but it felt like it might. Someone suggested using a separate script to track the number of emails sent, but that felt (to me) redundant with the information already stored in the database so I wanted to share what I thought was an easier implementation for those of us who aren't particularly good or comfortable writing scripts.

As a side note, using Mocord does cut down on the emails because I only get the email with the greatest alarm score for the lenth of the event, but Modetect generates separate events which in turn generates more emails. I prefer to keep the Mocord event duration shorter so that I would get multiple emails from truly separate events that just happen to occur close together.

While I'm thinking about it, the implementation of %EIM% to send the frame with the highest alarm score within an event is absolutely brilliant/awesome. Thank you for that. I'm considering a change to try to implement my own %EI2% or %EI2M% (where the 2 is for a 2nd sequence of alarm frames within the same event) to go with %EI1% and %EIM%, then I could keep my Mocord events longer yet still receive emails for secondary alarms.