SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.


No comments.

Leave a Comment

Please register or log in to leave a comment.