Using PowerShell To Search The Event Log

The “30-minute DBA” was operating in a highly effective mode. He’d automated most everything that could be automated. The first half hour of each day was spent reviewing the automated reports that landed in his inbox over night, taking actions as necessary. That’s why he’s paid the big bucks and gets to spend the balance of his time playing and learning.

“I’m convinced that he actually works 30 minutes a day and the rest of his time is spent playing around with cool SQL Server things in his test lab.” That’s what another DBA told me about the 30-Minute DBA.

Searching The Event Logs

There are a lot of options for automation. PowerShell has become one of my favorites. For example, PowerShell can be used to peek into the Windows Event Log, searching for anything of interest to you. I’ve already written about one way to sift through the events; now I’ll share a few more options.

To examine the Application Event Log, invoke the following cmdlet in PowerShell

Get-EventLog Application;

You can narrow the results to only those entries that are of type Error using the EntryType parameter.

Get-EventLog Application -EntryType Error;

You can further limit the results by looking at only the past 24 hours.

Get-EventLog Application -EntryType Error -After (Get-Date).AddDays(-1);

Using the Message parameter, you can search for specific words or phrases in the error message.

Get-EventLog Application -Message "*failed*";

If a specific event becomes a sporadic problem, you can search for the event using the Where functionality.

Get-EventLog Application | where {$_.EventId -eq 1309} | Format-Table -autosize;

Using these techniques, you can easily set up your own automated Event Log review process and insert the results into an email, a spreadsheet , or a database table for review.

Question:

  • What techniques are you using to automate your daily tasks?
  • What do you wish you could automate?

Dynamic PowerShell

I learned something new while giving my PowerShell session at the PASS Community Summit a couple of weeks ago.

Big Idea

One astute attendee asked if it was possible to create dynamic PowerShell in a script. Or put another way, can you define a PowerShell statement in a variable and then tell PowerShell to execute the contents of that variable. I’d never tried that so I didn’t know the answer. I said as much and speculated that it was possible and that I’d check into it for him.

Fortunately, there are a lot of really smart and knowledgeable people at conferences; not all of them speak. That can sometimes be intimidating; it can also be very helpful. Another attendee came up to me after the session and showed me this little script.

Executing Dynamic PowerShell

In the script, the $cmd variable is assigned a literal value that is itself a PowerShell statement. You could substitute any valid PowerShell statements in place of Get-Process. The second line is where it gets interesting. Enclosing the variable in parenthesis and prefacing it with an ampersand tells PowerShell to evaluate the contents of the variable and execute those dynamically.

$cmd = ‘Get-Process’;
&($cmd);

When I ran this script, I received a list of processes running on my local computer.

With this simple example, you may ask “What’s the point? Why not execute Get-Process directly?” I can see instances where you may need to dynamically build statements that include aspects that are not known at design-time. For example, passing in a sort or filter criteria, a machine name, or even a SQL Server instance name.

I wish I’d gotten the name of the attendee that showed this to me; I’d give him credit for this. So to the unknown Posher, thank you.

The PowerShell CookBook

“A script club.” That’s what a friend of mine said when he was telling me how he’d learned the basics of PowerShell. I did a double take. “You had a user group meeting at a strip club?!?!” He laughed and clarified, this time enunciating more clearly.

cutting board image

Last week at the PASS Community Summit in Seattle, I delivered a session called The PowerShell CookBook. In it, I demonstrated some scripts that I use when auditing SQL Server instances. I showed how to programmatically collect a list of SQL Server instances and then iterate through each instance to gather information about its configuration parameters, databases, and jobs.

As promised in the session, I’ve made the scripts available for you to use and tweak to suit your own needs.

If you were in the session, thanks for coming! I hope you found it worthwhile! After the session, someone asked if I’d be willing to deliver the session remotely to their user group. The answer is yes; just contact me using twitter, email, or a comment to this blog and we’ll work out a time.

See you next time!

Checking The Event Log With PowerShell

Good administrators work really hard so that they don’t have to work really hard. Well, that’s an oxymoronic statement if I’ve ever written one. So what do I mean?

Smart administrators are willing to spend time automating as much of their routine work as possible. Why go through the hassle of manually checking a long list of servers on a periodic basis when you can configure a scheduled task to do it for you and report only the exceptions. Smart administrators know that the effort to set up these processes on the front-end will pay dividends on the back-end, freeing them up to handle the exceptions or to play with the latest and greatest technology.

Checking Event Logs With PowerShell

Let’s take a simple example using PowerShell.

It’s a good practice to regularly review the Windows Event Log on the your servers to make sure things are going smoothly. You could make a note to remind you to manually connect to each server and sift through its event log. But that would be cumbersome and time intensive.

That’s where PowerShell can help. The following one-line PowerShell script will check the Event Log on a server, looking for only the Errors that have occurred within the past week. It doesn’t report the informational and warning entries, only the errors.

Get-EventLog Application -EntryType Error -After (Get-Date).AddDays(-7);

You can schedule this to run daily, weekly, or some other interval by changing the -7 parameter on the AddDays method.

Emailing The Results

“That’s great, but it’d be even more convenient to have the result automatically delivered to my inbox.” You can do that.

$smtp_server = “mysmtpserver”;
$to = “joew@myemail.com”;
$from = “administrator@mycompany.com”;
$subject = “Event log from my server”;
$body = Get-EventLog Application -EntryType Error  `
-After (Get-Date).AddDays(-7);

send-mailmessage -to $to -from $from -subject `
$subject -body $body -smtpserver $smtp_server;

And there you have it: the beginning of a PowerShell script to automate a portion of your routine system checks.

PowerShell and Effective Meetings at PASS 2010

Last week, PASS announced this year’s program for the 2010 Summit in Seattle, Washington. The program session are available online. This year I had a couple of abstracts that were accepted for the Summit.

Conducting Effective Meetings

The standard for meeting room chairs

Over my umpteen year career in consulting, I’ve patiently sat through literally hundreds of hours of meetings. Some of them were time well spent; most unfortunately were not. Time, money, and even moral are lost during worthless meetings that go on and on seemingly without reason or care.

Over the years I’ve done some research and made my own observations about systematic ways to help ensure that meetings are more productive and not just a big time sink for everyone involved. In this session I’ll share some of the best practices I’ve learned that will help you conduct more effective meetings. I’ll also share some tips that can help you to make the meetings that you don’t run a bit more effective too.

The PowerShell Cookbook for the DBA

When PowerShell was first announced, I was very reluctant. Frankly, I didn’t want to spend the time learning a new technology that 1) may be found wanting when compared to counterparts like PERL and 2) may not be around after a few years due to a low adoption rate. My skepticism was pretty high and my enthusiasm was low.

However since then I’ve become a big fan of PowerShell. It definitely lives up to its name. It’s a powerful object-based scripting language built on the .net framework that’s completely integrated with the Windows environment. It natively knows about the Windows Registry, the devices, and the environment, making many tasks relatively easy.

In this session, I’ll share with you some of the scripts I’ve used to collect and analyze server settings and performance data. Most of my sessions are 50% demonstration; this one may have an even higher percentage of demos.

Question:

  • What tasks do you use PowerShell to accomplish?
  • What are some of your more monotonous activities that could be automated with PowerShell?
  • What kind of scripts would you like to see in my demos?

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.

powershell-2010-05-21.jpg

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.

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

Follow

Get every new post delivered to your Inbox.

Join 31 other followers