Using PowerShell, LogParser and PowerGadgets to get Exchange 2003 storage information – Part 1

Was asked this week to write up a script that would graphically show how much free space was available in our Exchange Databases. My twitter colleague Alan Renouf made me aware of Jonathan Medds's post about Exchange free space. This gave me a good starting point. I now knew to look for EventID 1221 in the Exchange server(s) application event log. Being a big fan of LogParser, I decided to query the event logs using it instead of Get-WMIObject Win32_NTLogEvent.  My reasoning for this is two-fold, I have a lot of LogParser scripts that I am able to reuse in PowerShell.  Additionally, for many log parsing tasks, LogParser still is faster then using Select-String or other parsing methods. 

I highly suggest that if you are entertaining the idea of using call LogParser within PowerShell, you should copy David Muegge’s LogParser library into your code base.  Check out his posting for an explanation on how his library works (it is slick).

function Get-ExchangeEventLogRecords {

# Include our LogParser library
# Check out David Muegge's Blog
# http://muegge.com/blog/?p=65
# for more info on his library
    .\LogParser_Lib.ps1

# This is the query we will be passing
# to LogParser
# Let's hear it for Here-Strings!
$query = @"
SELECT ComputerName, timewritten, Message
FROM \\Exchange01\application
WHERE eventID = 1221 and
SourceName = 'MSExchangeIS Mailbox Store' and
to_date(timewritten) = System_Date()
"@
$inputformat = Get-LPInputFormat "evt"
$records = Get-LPRecordSet $query $inputformat
return $records
}

The function Get-ExchangeEventLogRecords returns all the records that I care about (in this example I have included only one Exchange Server). In order to get the exact fields I want, I need to parse the Message field from the event log.  The field looks something like this:

The database "MSXC01SG4\MSXC01SG4DB4" has 4626 megabytes of free space after online defragmentation has terminated…

The next function takes care of the parsing and creates a custom object for me.

function Format-ExchangeEventLogObject {
    Begin {
        $regex = [regex] '(?:MSXC\w+\\MSXC\w+)'
        $regex1 = [regex] '(?:\s\d+)' }
 
    Process {
        $Store = $regex.Match($_.message) | select Value
        $Mem = $regex1.Match($_.message) | select Value
        $Mem = $Mem.value.ToString().Trim()
        $obj = New-Object psObject
        $obj | Add-Member NoteProperty TimeWritten $_.TimeWritten
        $obj | Add-Member NoteProperty ExchangeServer $_.ComputerName
        $obj | Add-Member NoteProperty Store $Store.Value
        $obj | Add-Member NoteProperty AvailableMem $Mem
        Write-Output $obj }
}

One thing to note, so far, we have only looked at the available space within the *.EDB file.  In the next post, I will take this information and gather a few more relevant fields (total size of the EDB file, size of STM file, estimated availability in the STM file) and show a graphical representation of the data.

Enjoy!

PowerShell/Log Parser 2.2 and Add-Member

I recently stumbled upon a great post by David Muegge titled “Log Parser and PowerShell - Part II”. David does a great job of wrapping PowerShell around Log Parser. I highly recommend that you take a look at his blog post. I was looking at his functions and decided that I should give them a try against some production data. I decided to look at failed login attempts (we have had an issue with an particular application).

What I was looking for was a count of failed logins (from the Strings property). The following code assumes that you are using David’s Library.

$query = "SELECT Strings FROM '\\XX-P01\Security' WHERE EventID = 529"
$inputformat = Get-LPInputFormat "evt"
$records = Get-LPRecordSet $query $inputformat
$records  `
    Add-Member -name "User" `
    -value {$this.Strings.substring(0,$this.Strings.indexof(""))}`
    -memberType ScriptProperty -force -passThru  `
        Group-Object User -noElement `
        Sort-Object -descending Count `
        Select-Object -first 10

In order to get exactly what I wanted, I added a new property to this object: "User"

In order to do this, I needed to parse the Strings property of my $Records object for everything before the first pipe.

$this.Strings.substring(0,$this.Strings.indexof("")) achieves the desired value.

This gives us:

Count Name
----- ----
   81 cluster
   30 mastersql
   18 Administrator
    9 home50
    8 marg98
    7 bart01
    7 lisa27
    6 Nels07
    5 barn10
    4 skin01

Just what I was looking for!
Enjoy!