There are a number of environments or situations where large-scale systems monitoring is either not cost effective or simply not available. Windows 7 and 2008 include a feature for sending notifications when specific events occur in the event log.

Technical Area: Accidental Systems Administrator, Accidental Database Administrator
Level of Difficulty:
Additional Articles:Virtual Lab entry on the LTD Wiki

Using the Built-In Email

Windows Vista, 7, and 2008 have a built-in option that allows us to attach actions to events from the event log. These actions take the form of Tasks, similar to scheduled tasks with the exception that they are trigger by events rather than time. The services infrastructure in windows logs startup and shutdown events for services and many applications use the event log rather than a custom logging format. We can take advantage of these messages to create a notification that emails us when certain events occur.

First we need an example of the event and, being lazy, I’m going to generate one the easy way. Shutting down a SQL Server service generates the first event I want to track (and makes SQL admins everywhere cringe a little, mission accomplished). Later in the post we’ll cover creating tasks without restarting our server, since people tend to get upset when we do things like that in production.

When we open the Event Log from Administrative Tools menu in Windows, there are several logs available. SQL Server and other service log entries are logged to the Application log. To create my new event-driven task, I locate the entry I just created, right click it, and select “Attach Task to This Event…”.

Attach Task to Event – Dialog

The Task creation option presents us with a fairly simple dialog. To be consistent and build good habits, we fill in a descriptive name and description for the task.

Names are Important

The second step doesn’t require (or allow) an input and the third step presents us with the simple decision of sending an email, launching an executable, or displaying a message. We are here to send an email, so the choice should be obvious (but I still pressed the wrong one the second time through).

Information for Email

After filling the email options out we continue to the last tab to review the task information before finishing. At this point we will want to check the “open properties” checkbox and press “Finish”.

After completing the wizard, we now have the full properties dialog for the task. Perhaps the most critical item here is to change the radio selection on the first tab to “Run whether user is logged in or not”. There is also an OS configuration box that I changed to Windows 2008 R2/Windows 7, but I am not sure whether this provides an addition features or simply satisfaction from making such an important decision.

Summary before Finishing

I have enough of the task filled in now that we can now press the OK button and try it out. We open Server Manager from the pinned icon on the taskbar (or via the search mechanism in the Start menu). On expanding “Configuration/Task Scheduler/Task Scheduler Libary” and selecting “Event Viewer Tasks” we should see the task we created. Right clicking the task and pressing “Run” will force the task to run once (again, just like testing a scheduled task). We can select the History tab to see the details of the run or wait patiently for an email to show up in our inbox.

Event Viewer Tasks view

And there we have it, an email each time our service stops.

Alignment of the Stars

If your mail server is like mine then it probably doesn’t allow anonymous relaying. If you are using Exchange and you are sending from the same account that you configured the task for (or the user is allowed to send for other users), then you may have received an email. Checking the alignment of the stars may also be of assistance.

Even if the email did work, it is going to be rather tedious to create individual tasks for each of the potential events (SQL Server start/stop, SQL Agent Start/Stop, etc) that you are interested in.

Rather than build a hard-coded email task for each possible event, there is an option to run an executable. Tie that to Cscript.exe and a VBScript (or maybe a powershell script?) and you have a lot more flexibility to send emails.

If you decide to go this route, you will probably want to edit your tasks to carry some additional information (like which event is firing). This will allow you to create a single script and task for multiple events:

http://blogs.technet.com/b/otto/archive/2007/11/09/find-the-event-that-triggered-your-task.aspx

Not Perfect

This method of reporting from windows does not replace an external monitoring system. There is a wide range of errors that services and executables can generate. Covering them all would be extremely difficult even if we ignored errors that cause the box to lose communications or freeze entirely. However, if you have no budget at all, setting up some of these alerts will only cost you time and it will at least get you one step closer to knowing about problems before the phone rings.

Additional Articles:Virtual Lab entry on the LTD Wiki