Help with Sort-Object

PowerShell Help
1

Hi I have a script to query the sql agent logs on a number of servers. I want to sort the output by duration but cant. Any help would be greatly appreciated.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null # The sql instances $SQLInstances = @() $SQLInstances += "localhost" $SQLInstances += "localhost" $SQLInstances += "localhost" # Array to hold results $Results = @() # Iterate the sql instances foreach ($instance in $SQLInstances) { # new up a connection to the sql instance $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "$instance" # add all jobs to an array - any filtering could and should be done here e.g. $Results += $srv.jobserver.jobs # | where-object {$_.lastrunoutcome -eq "Failed" -and $_.isenabled -eq $TRUE} } # get only the data we want $psObjects = $Results | Select-Object OriginatingServer, ` @{LABEL='JobName'; ex={$_.name}},` @{LABEL='RunStatus'; ex={$_.CurrentRunStatus}},` @{LABEL='LastRunOutcome'; ex={$_.lastrunoutcome}},` @{LABEL='LastRunDate'; ex={$_.lastrundate}},` @{LABEL='LastRunCompletedDate'; ex={$_.lastrundate.AddSeconds(($_.JobSteps | Measure-Object -Property LastRunDuration -Sum).Sum)}},` @{LABEL='Duration';EXPRESSION={"{0:N0}" -f ($_.JobSteps | Measure-Object -Property LastRunDuration -Sum).Sum}}

we can now filter on the duration and only select the top 10

$psObjects | Sort-Object -property @{Expression={$.LastRunCompletedDate - $.LastRunDate}; Ascending=$false} | Select-Object -First 10 | Format-Table -AutoSize

If i try to sort using the below code it works, but I cant figure out why

$psObjects | Sort-Object -property @{Expression={$_.LastRunCompletedDate - $_.LastRunDate}; Ascending=$false} | Select-Object -First 10 | Format-Table -AutoSize
2

Your Duration property is a string, which is probably screwing with your head a bit. (ie, string “9” is greater than string “100000000”)

If you make Duration a numeric type, it’ll probably work as you expect:

 @{LABEL='Duration';EXPRESSION={($_.JobSteps | Measure-Object -Property LastRunDuration -Sum).Sum}}
3

Dave Wyatt. You are a legend. I don’t know how to mark this thread as answered but it is. In fact it was answered before I had even managed to edit the post to get all my code in one code block.

Thanks

4

I cheat. :slight_smile: (Email notifications of new posts on my cell phone)