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

Marco explains below how this works:

“Imagine the following simple data:

PS C:\Users\Marco> gc data.csv server,ip server1,ip1 server1,ip2 server2,ip2 server2,ip3

In your case, you have event log entries.  Now, with the above code, you determine you want to only retrieve the first IP listed for each server.  There’s several ways to accomplish this, but with my hash table approach…

ipcsv data.csv|%{if($hash[$_.server] -eq $null){$hash[$_.server]=$_}}

So, I create $hash as a hashtable variable.

I use a foreach-object to look at each object in the collection.  Looking at each object’s server property, I get the current value of the property ($_.server).  I use that as the key for my name/value pair in the hashtable.

Basically, if we take the first line of the input data where $_.server=“server1”, I take that property, and check if the hashtable already has this name if($hash[$_.server] -eq $null).  If that is true, this is the first occurence of the value “server1”, so I create that hash key with that name and pass $_ as the value.

When I hit line #2 in the input data, and check $hash[$_.server] again (or $hash[“server1”]), I see that name already exists, so I skip it.

Then onto line #3, where $_.server is now “server2”.

So from the above, I make sure that for each serverX, I get only the first IP address in the list.

This is similar to your scenario where you only want the first occurence per mailboxstore.”

Nice!

The resulting updated script is below. Note I also updated it somewhat on the original based on a useful technique illustrated in another posting.

$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