Using Powershell to Find Free Space in Exchange 2003 databases: Updated

Back in January I posted about how to find free space in Exchange 2003 databases using Powershell.  Not long after this we changed our online maintenance schedules resulting in each database not having maintenance every day. The original script was based on the assumption of maintenance happening every day and there being events in the application log for every database in the last 24 hours.

Consequently I have had to adjust the script so that it would look back a few days to ensure there are logs for each database. This then created an interesting problem because it meant that sometimes you would get back more than one result per database and so obviously you would only want to keep the most recent result.

For example you would get results like the below:

ServerName,MailboxStore,”Free Space (MB)”,”Time Written”
SERVER1,SG1-MAILBOX1,1592,20090423070001.000000+060
SERVER1,SG1-MAILBOX1,1684,20090422070001.000000+060
SERVER1,SG1-MAILBOX2,52,20090423070000.000000+060
SERVER1,SG1-MAILBOX2,64,20090422070000.000000+060
SERVER1,SG1-MAILBOX3,2607,20090422070001.000000+060
SERVER1,SG1-MAILBOX3,2566,20090423070000.000000+060

If I used:

$myCol | Sort-Object Servername,MailboxStore -unique

it would produce results with only one entry per mailbox store, but it would always choose the most recent result. So I posted my issue to the PowerShell newsgroup and MVP Marco Shaw very kindly helped me work through it. The end result was this:

$hash=@{}
$myCol | Sort-Object @{Expression="Servername";Descending=$false},@{Expression="MailboxStore";Descending=$false},@{Expression="Time Written";Descending=$true} | Foreach-Object {if($hash[$_.mailboxstore] -eq $null){$hash[$_.mailboxstore]=$_}}
$hash.values

$ExchServer = 'Server1','Server2'

#Get the time 3 days ago in the right format for WMI query
$WmidtQueryDT = [System.Management.ManagementDateTimeConverter]::ToDmtfDateTime([DateTime]::Now.AddDays(-3))

$myCol = @()
foreach ($Server in $ExchServer){

    #Perform WMI query of Event 1221 in Application log in the last day
    $eventid = Get-WmiObject -computer $Server -query ("Select * from Win32_NTLogEvent Where Logfile='Application' and Eventcode = '1221' and TimeWritten >='" + $WmidtQueryDT + "'")

    foreach ($event in $eventid){

        #Get the name of the Mailbox Store
        $MBXStoreLocationStart = $event.Message.IndexOf("Storage Group") + 16
        $MBXStoreLocationFinish = $event.Message.IndexOf("has") - 2
        $MBXStoreLocation = $event.Message.SubString($MBXStoreLocationStart, $MBXStoreLocationFinish - $MBXStoreLocationStart)

        #Get the free space figure
        $MBLocationStart = $event.Message.IndexOf("has") + 4
        $MBLocationFinish = $event.Message.IndexOf("megabytes") - 1
        $MBLocation = $event.Message.SubString($MBLocationStart, $MBLocationFinish - $MBLocationStart)

        #Store the data in $myCol
        $MYInfo = “” | select-Object ServerName,MailboxStore,'Free Space (MB)','Time Written'
        $MYInfo.ServerName = $event.ComputerName
        $MYInfo.MailboxStore = $MBXStoreLocation
        $MYInfo.'Free Space (MB)' = $MBLocation
        $MYInfo.'Time Written' = $event.TimeWritten
        $myCol += $MYInfo

    }
}

$hash=@{}
$myCol | Sort-Object @{Expression="Servername";Descending=$false},@{Expression="MailboxStore";Descending=$false},@{Expression="Time Written";Descending=$true} |Foreach-Object {if($hash[$_.mailboxstore] -eq $null){$hash[$_.mailboxstore]=$_}}
$hash.values | Sort-Object Servername,MailboxStore | Export-Csv AllExchangeWhiteSpace.csv -NoTypeInformation