Real-time alerts possible?

[ms_xxx]

Hello,

we'd like to implement a system which sends out email alerts based on certain changes in the database - for instance, if the revenue for a certain store drops below 50% of the 30-day average, we'd like to automatically generate an alert to the relevant decision makers, so they can check what's going on. (Fictional example, we don't have stores)

Does JasperServer (or any other related tool) provide any functionality which could accomplish this? If not, is there any open-source solution which would be suitable? Ideally we'd like a user-friendly frontend to define the criteria and design the alert mails.

Thanks.

Max

[Gaby38]

 As for me, jasperserver is not the best tool to do what you want, because it has not been designed for that.

But it contents a functionnality that could be interesting for you : the scheduled reports. You can design reports and scheduled them to be run at chosen frequency. The results of the reports are sent by mail to a list of mail addresses.

Moreover, if there are no results (blank page), you can tell jasperserver not to send any mail.

So, in your case, you could have one or several dedicated scheduled reports, running adequate queries against your database to detect alarms. If no alarm, no result so no mail is sent.

As soon as an alarm is detected, the query returns the details of the alarm that can be sent to dedicated people.

You could decide to run "alarm reports" every hour for instance, or once a day, or ...

But in this case, you couldn't have a

"user-friendly frontend to define the criteria and design the alert mails" . You should use the tools to create reports as iReport which is a generic tool for designing jasperreport reports.

This solution is not the best one but it could be set very quickly, let's say a week, by people aware of jasperreport and your database.

[ms_xxx]

That sounds somewhat promising, but also like a bit of a makeshift solution. Isn't there any (free) solution for this problem that is specifically designed for these things?

[Gaby38]

 You can also use the "trigger" mechanism of your rdbms, if any.

I use that in a commercial product managing children inscriptions to activities. I put in place a trigger on insert into the table "inscription" and everytime a new record is inserted I check if the max number of allowed children is reached.

In your case, you could use PL/SQL packages to send mail if there is an alert detected by your trigger.

This should not be very difficult to put in place by people aware of triggers and pl/sql programming (in my case I have an oracle database)

[rajxraj]

Hi Max,

I am having same requirement now. Did you find any best solution for this functionality or you are still using Jasperreports for this.

 

Please let me know your insights.

 

Raj