0

We are looking to create an email notification system using our issue tracking software and the SQL Server database. The goal is to send email when a number of conditions are met.

This is to satisfy custom notifications that our customers request. For example, one customer would like an email sent to person Y if a ticket is opened for a software problem, and an email sent to person Z if a ticket is opened for a hardware problem.

SQL Server Notification Services seems to have the functionality, but we are interested in a GUI rather than writing queries by hand each time. We also need some way to manage them, since the number of custom notifications could get into the thousands.

EDIT:

These are good suggestions, but one requirement is that we have a GUI for managing rules. We don't want to be hand-writing queries every time we need to create or change a notification.

We know we could hack something together with scripts, triggers, and scheduled tasks, but it seems like there ought to be some kind of packaged product that does this already. Maybe it requires some database skills to initially set up, but ideally after that it can be updated by managers who aren't DBAs.

Improve this question

5 Answers 5

Reset to default
1

Are you looking for a system already built? There are ticketing systems from many vendors that already do this. Most of them are somewhat crappy, IMHO, and overkill, but they do route things.

Or do you have some system and need to add notifications?

The design here to build something is not incredibly hard, but it would take some development work. Simple tools to manage the administration would be needed.

Improve this answer
3
  • We already have an issue system. The notifier doesn't need to integrate it, the idea we have is just to have the notifier check the tables we tell it and send messages if they return any rows.
    – Stephen Jennings
    Jun 2, 2009 at 22:01
  • Then I'd recommend you just get a developer and build something that uses xp_sendmail based on rows returned. Anything you buy will require integration and development time anyway. This isn't that complicated, should be a few days with admin tools included.
    – Steve Jones
    Jun 3, 2009 at 16:15
  • Search open source sites. You might stumble upon an app you can build upon.
    – MarlonRibunal
    Jun 4, 2009 at 15:33
0

Not sure how integration works with internal products, but you could check out Quest's Foglight monitoring product.

Improve this answer
0

Could you just use a query designer and paste the query into a table (form field) that stores all your monitors?

Then run a job periodically to go through the table and run the queries, using dbmail to send mail to 'operators' you store in a table and config through your GUI.

Instead of a job, you could have a trigger check on each insert and send the mail using the now asynch dbmail.

Improve this answer
1
  • We considered this, but managing that many triggers would be infeasable. We don't want to muck around with the database directly to add or change these notifications.
    – Stephen Jennings
    Jun 3, 2009 at 0:50
0

Stephen, this sounds more like an outline spec for an application rather than a 'how do I do this ...?'! Do the rules change so frequently that Notification Services isnt the best solution?

Reporting services can be scheduled and it is possible to use it to execute INSERT, UPDATE and DELETE commands based on user supplied parameter values as well as SELECT but that is not a polished way of running what seems to amount to a small CRM GUI that you have outlined.

Maybe looking for an ASP.Net developer to build you a GUI around the rules you have detailed would be your way forward if you dont have time/experience to 'roll your own'?

Improve this answer
0

So, you need something scalable. Why not create a table that you can insert rules into, first_name, Last_name, email_addr, table OR table.column such that you add your email rules to this table. Then run a scheduled SQL script that performs a generic query by consulting the rules table, and checks the database for any rule matches. By scripting it, you control how often the job runs, and also control how it emails, i.e. if it was a Perl Script using DBI database calls, you could also use a Perl Sendmail module to issue the emails right from within the same script.

You could even start with just a flat text file, or XML file with the user-based rules...

If this is not inherently supported in the product, that you'd want to write an automation script that can be scheduled at your discretion. The other option is possibly a hybrid of this, and use a database trigger ON INSERT to then fire the script/sql that performs the checks and emails.

Simply wrap this process with a simple web front-end to add your rules. The SQL would largely stay the same and be generic. You would just need a form to INSERT/DELETE/UPDATE the rows of rules.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .