Vista - Selecting one from multiple similar objects

I have a script which pulls entries from the Application log on Exchange
servers to find how much free space is inside the databases. It looks at
entries created in the last day which is fine in environments where Exchange
Online Maintenance runs every night on all databases, but in our environment
it doesn't do this - we run online maintenance for different stores on
different nights. So to make sure I got entries back for all databases I
increased the date range to three days - this means though that you sometimes
get back multiple entries for the same mailbox store.

This is the script:

$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
}
}

$myCol | Sort-Object Servername,MailboxStore

This will result in output similar to the below. What happens is I will get
back similar results for some databases, so same server and mailbox store,
but slightly different free space from the different days. I want to end up
with only one record for each database and preferably the most recent.

I tried using the -unique parameter of Sort-Object which works to a point,
but doesn't guarantee I get the latest record, so I end up with one record
per database (good), but sometimes from two days ago rather than one day
(bad).


ServerName,MailboxStore,"Free Space (MB)","Time Written"
server1,SG1_MBX1,2953,20090423070001.000000+060
server1,SG1_MBX1,2742,20090422070001.000000+060

Can anyone help me so that I get one record and its the latest one?

Thanks

Try something like

sort-object -property timewritten -descending | select -first 1

"jmedd" wrote:

Thanks. $myCol normally returns over 100 results, if I do the below I only
get 1 result.

I need it to look through the 100 results, if there is more than one result
for a mailbox store only keep the most recent result for that mailbox store.

"Rob Campbell" wrote:

Hmm. Does this work?

group-object -property servername |% {group-object -property mailboxstore}
|% {sort-object -property timewritten -descending | select -first 1}



"jmedd" wrote:

Unfortunately not. If I run the below no results are returned at all.
Breaking it down this starts happening at |% {group-object -property
mailboxstore} .

"Rob Campbell" wrote:

Thanks to PowerShell MVP Marco Shaw we have an answer which works well.

I added the below to the end of my script:

$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

"jmedd" wrote:

When I use the hashtable as below I get an error

"Array assignment to [CSQ 1 Mailbox Store] failed: Cannot convert value "CSQ
1 Mailbox Store" to type "System.Int32". Error: "Input string was not in a
correct format.".
At :line:86 char:59
+ Foreach-Object {if($hash[$_.mailboxstore] -eq $null){$hash[ <<<<
$_.mailboxstore]=$_}}"

I have no idea what this means. I am new to powershell and have been
piecing code together to meet my needs.

"jmedd" wrote: