The Power of Regex in PowerShell

mitchellreport-2010-05-21.jpgPowerShell is probably banned in MLB as a performance enhancing substance. You won’t find red-blooded American baseball player admitting to knowingly using PowerShell. It doesn’t matter what his coach and former teammates say. “I’m clean.” he’ll say to the public. “I didn’t come to talk about the past.” he’ll say in a prepared statement to Congress.

Ok, enough with the stretched analogy.

Power is as PowerShell Does

As it’s name implies, Powershell is extremely powerful. You can do a lot with it right out of the box. It’s built on the .NET framework and is inherently aware of the Windows operating system. This makes accessing system resources almost trivial assuming you have the appropriate permissions.

When combined with Regular Expression (regex), PowersShell’s capabilities are taken to a whole new level. Folders can be readily searched for files that contain a string of a certain format, such as an email address or an IP address.

Let’s consider a simple example to illustrate how PowerShell may be used by SQL Server DBAs.

Searching the SQL Server ErrorLog

SQL Server records information about its performance and status in its error log files. Database Administrators can examine these file for any anomalies that may have occurred. The problem is that there is a lot of chaff with wheat. DBAs must sift through a lot of normal information to find the relatively few exceptions. This is where PowerShell combined with regex can help out.

Let’s say we want to look through the ErrorLog for any errors with a severity level of greater than 9. The file can be multiple megabytes in size so even loading it into a text editor and doing a Find may be cumbersome. Plus that’s a very manual approach that is not easily automated.

So, we create a short PowerShell script to do this for us.

$log = get-content “E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG”
$search = “Severity: \d{2}”
$log | select-string -pattern $search

What does this script do?

The first line uses the get-content cmdlet to read the log file into a variable named $log.

The second line defines the search pattern in a variable named $search. We are looking for “Severity: ” followed by two numbers.

The last line passes the contents of the log file into the select-string cmdlet and parses it using the search filter that we defined in line two.

The output can be seen below.


That’s a lot easier than searching through the file by hand.

From here, we could view the information when we run the script, schedule the script to run automatically and save the information into an Excel spreadsheet, or email us the results in html format. I’ll cover some of those topics in another post.

Note: This script was written using PowerShell version 1.0 since that’s what I had readily available at the time of this post. The same script should run under version 2.0 of PowerShell with little, if any, modifications. Before the next post, I’ll upgrade to the newer version.

What have you done with regex in PowerShell. Share a link to your scripts in the comments below.

9 Responses to The Power of Regex in PowerShell

  1. Pingback: The Rambling DBA: Jonathan Kehayias : Parsing SQLIO Output to Excel Charts using Regex in PowerShell

  2. Pingback: Dew Drop – May 27, 2010 | Alvin Ashcraft's Morning Dew

  3. Pingback: Weekly Link Post 147 « Rhonda Tipton's WebLog

  4. Pingback: Where Is The SQL Server ErrorLog File? « WebbTech Solutions

  5. Pingback: My Most Popular Posts From 2010 « WebbTech Solutions

  6. Pingback: Writing Better Queries Presentation Materials « WebbTech Solutions

  7. Pingback: Parsing SQLIO Output to Excel Charts using Regex in PowerShell | Jonathan Kehayias

  8. Pingback: refinance an fha loan

  9. Pingback: Parsing SQLIO Output to Excel Charts using Regex in PowerShell - Jonathan Kehayias

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s