WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Saturday, February 26, 2011

Looking for trouble in all the right places with Powershell

Sometime in early 2010 I got into using powershell at work.  It started with MS Operations Manager not being particularly useful in the environment and then Buck Woody starting to post little bits and bites on using powershell for monitoring.  The first function I wrote wrapped the "commandlet" (cmdlet) Get-EventLog into something I could call from various monitoring scripts.  Here is the start of a series of functions I wrote to help find out about issues, hopefully before any user does.

function Get-FilteredEventLog([string] $physicalServer, `
            [string] $logName, `
            [string] $entryTypeFilter, `
            [datetime] $eventLogDate,`
            [string] $eventExclusionList )
{Get-eventlog -ComputerName $physicalServer `
    -logname $logName `
    -After $eventLogDate `
    | Where-Object { $_.entryType -eq  $entryTypeFilter } `
    | Where-Object {$eventExclusionList -notcontains $_.EventID} `
    | Select-Object MachineName,EventID,TimeGenerated,Message

A sample function call with comments for each parameter.

Get-FilteredEventLog -physicalServer $Server ` # Name of server to check.
    -logName "System" `                      # System (could be Application or Security, etc)
    -entryTypeFilter "Error" `                # Errors in log only
    -eventLogDate "2010-09-14" `       # Exclude Events prior to eventLogDate
    -eventExclusionList "1111,1112" `  # Exclude EventID 1111,1112
    | Out-GridView  # Push to a Grid View

1. If you have more than a server or two, you may wish to use Out-File and push the results to a text file.
2. This method is not fast, if want speed, eliminate some of the filters and just look for errors or the latest 100 events using -Newest 100

[edit: removed Format-Table in function - separate function from presentation.

Wednesday, February 23, 2011

SQL Saturday 47

I am just winding down from a nice vacation in Phoenix AZ and a good time at SQL Saturday 47.  Had a full room for my presentation on Powershell Monitoring, here is a link -> sqlsat47 .  The room had a few users and many folks with lesser experience.  I ended up having to move to fast through the content because I had too much and not quite enough time to cover the advanced concepts thoroughly.  Had a nice conversation afterword and some good laughs with several folks.  The organizers did a nice job and things moved smoothly.  This was my second SQL Saturday and I can't wait for the next one to attend.