SQL Error Log Daily Email Report with PowerShell

In my job, I wind up helping out our DBAs with various tasks for setup, troubleshooting, and reporting. It’s definitely not my main job and I’m still learning a lot from them and from training courses (SQL Skills Accidental DBA was a great one), but when they told me they had some simple PowerShell they were using manually on a daily basis, I jumped at the chance to dig in more and get that automated for them.

One of the scripts they had would check the SQL error log, filter out unwanted errors, and display the results in a table. I took that script, added some logging, and turned it into a daily scheduled task hosted on of our utility servers that runs other scheduled scripts via the Windows Task Scheduler. It wound up taking a task that could take 30+ minutes each morning to a quick 5 minute skim through emails to see if anything needed to be investigated further.

The logic is pretty straight forward, but here are the highlights. Everything important has a parameter so it can be configured to run as frequently as necessary without code changes.

First, we load what’s needed. Mainly, the SQLServer PowerShell module. It’s included in SQL Server Management Studio 2016, and can be installed by itself. We have it installed in a default module location, so we no longer need to manually import it. After that, we log the start of the script and test for connectivity to the server before continuing. One thing that I don’t do within the script is make sure the Event Log source exists. I might add it, but right now I just run it before running the script for the first time on a server. It’s a simple one-line PowerShell command. (Technet: New-EventLog)

The next step is to get the error log, and filter out what we don’t care to be alerted to. Right now I’m using a very big Where-Object to filter, but I’m planning to try some array comparisons to make it easier to manage in the future.

The final step is the email, if errors were detected. There is no reason to get an email that no errors were detected, except to ensure the script is still running. That is what the event logging is for though, and you can set up monitoring of the event log for errors if you wanted. With 30 servers, we really don’t want to get emails for each one unless we need to.

I am using the old table HTML for the email body. It’s old and doesn’t do anything pretty, but we didn’t need it to be pretty. All we wanted was the errors and the timestamps so we could determine if we needed to investigate further. I’d like to add an additional column to the email table that identifies possible causes for the error messages. One error we see on occasion is related to an issue with a backup using a third party tool. The error message isn’t very descriptive so it would be nice to have one or two possible causes listed with it, and even a link to our documentation on how to resolve the issue.

There are a lot of other valid ways to do this, but for us, this was a simple way to get the errors to us each morning to keep an eye on all our servers.