How many times have you had to read through an extremely long log file to find specific events that may have led to a failure in an automated job or process? Have you ever had to parse multiple large log files to get to the root cause of a problem, or identify errors and warnings that occurred throughout a process? If you have answered yes to any of these questions, then I would like to share a PowerShell script that can make this job much faster and cleaner for you. For those SQL Server DBA's that are not familiar with what PowerShell is, and how it can make your life easier, I encourage you to learn more about it by reading the many articles and forums located here on SQLServerCentral.

Log File Overview

I realize that not every situation will be exactly like this, but I'm hoping that your situation might be close enough, that you may find a way to use this example to solve problems of your own. First, let's begin by looking at a sample log file that represents the output of an FTP session that runs nightly to download and unzip files to a local staging server.

Sample FTP Process Log File

In a situation where your average nightly download might include thousands of files, the process of finding out how many files failed to download would take a very long time if done manually. This is where PowerShell can be a real time saver. Let's begin by taking a look at a PowerShell script that will parse a log file like the one in this example, and summarize how many files were successfully downloaded versus ones that failed.

Summarizing Results

We can start by looking at a few of the PowerShell commands we will use to get this job done. Our first command utilized will be the Get-Content Cmdlet. Below is a basic example of how to use Get-Content.

This simple little command will do just what the name implies, and that's read the contents of the file one line at a time and returns a collection of objects. This is important, because it's these collection of objects that will be needed by our next command. We will begin using these objects with the Select-String Cmdlet. Before we can begin to utilize the Select-String Cmdlet, we must pipe the collection of objects from Get-Content over to Select-String. This is remarkably simple to do in PowerShell by using the pipe ( | ) symbol between the two (2) commands.

At this point, we're now using the strings available in the Get-Content objects to filter out those that have the words "ERROR", "WARNING", "SUCCESS" and "FAILURE" in them. How cool is this, we are already beginning to see the simple effectiveness of using PowerShell for this task. Now that we're beginning to get somewhere, we need to add one more piece to this puzzle to get a true summary of what happened in the log file. To do this, we will use the Group-Object Cmdlet to roll up a count of each line that contains our string filters. Again, this command will be fed the objects filtered from Select-String through the use of the pipe ( | ) symbol.

Notice our progression here, Get-Content feeds data to Select-String, which then feeds data to Group-Object. This is a basic fundamental of how PowerShell works, and understanding this concept will get you over the wall towards mastering the PowerShell scripting language. At the heart of this script is the set of Cmdlets we just discussed, but to make the script more useful we will give it the ability to parse multiple files, write the results to a text file and then email the results to an operator. I will not go into the full details of the more advanced sections of the script in this article, but I do encourage you to learn more about PowerShell by visiting the Microsoft TechNet site for PowerShell.

Complete PowerShell Script

The completed version of this PowerShell script is available with this article. Please feel free to use this in any form you need to. Although the script is designed to parse and summarize a log file with certain keywords, you can easily modify and tweak it to function in another situation.

Enjoy!