Consolidated Email Alerts

Version 1

    The attached is a SQL stored procedure that checks the Events table during the past 2 minutes for specific event types (node up/down events, interface up/down events, and reboots) and emails them.  If no event is detected, no email is sent.  If 1 event is detected, the event message becomes the email subject with the details in the body.  If more than 1 event is detected, the number of events is included in the subject line and the body of the email includes all of the events that occurred in the past 2 minutes.  This significantly reduces the number of emails we receive during the day, especially during WAN and power outages.  Consolidated emails alerts for multiple events are more efficient than sending lots of individual alerts 1 second apart to distribution lists.  And it's a lot less irritating when checking email on your phone... 


    This was the 1st version that shows the basic functionality.  I am not posting v2 as it is a work in progress and has grown a bit more complex as we wanted to pass parameters to it and allow different email addresses to be emailed the events for different regions based on custom properties. 


    To implement this, you should use SQL Server Mgmt Studio:

    -          I highly recommend you involve a DBA buddy of yours if you are not familiar with it.  I never really used it before starting to setup Orion so it was useful to have someone that could answer questions for me

    -          Configure Database Mail from the Management options under the DB server

    -          Expand Databases, NetPerfMon, Programmability.  Right clicking on Stored Procedures should allow you to create a new one.  Mine’s called EmailUpDownEvents.  There is a wizard you can use somewhere for this also.

    -          The attached file shows the content of the store procedure that you can use.  At a minimum, you should be modifying the email address defined as the @recipients.  You probably also want to modify the @bodytext and the @profile_name to be whatever you used when configuring Database Mail.

    -          Execute it so that the stored procedure is created and added to the DB.  Refreshing the list of stored procedures should show it there and you should be able to right-click on it and modify it.

    -          From a query window or a SQL Server Agent Job, you can run “exec EmailUpDownEvents” using the NetPerfMon DB and if it works, you should either get 0 or 1 email regardless of the number of events that have occurred in the past 2 minutes.  We have a job scheduled to run it every 2 minutes.


    We still use Orion Alerts for non-email actions and for alerts that do not result in too many emails being triggered.  But for the node/interface up/down and reboot alert emailing, we like this much better.


    In the future, I hope SW provides the ability to configure alerts based on custom SQL queries or at least on multiple events in the Events table so that it’s possible to consolidate or summarize events without having to do the above....