Author Posts

November 15, 2013 at 8:29 am

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 += $ # | where-object {$_.lastrunoutcome -eq "Failed" -and $_.isenabled -eq $TRUE}
# get only the data we want
$psObjects = $Results | Select-Object OriginatingServer, `
@{LABEL='JobName'; ex={$}},`
@{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

November 15, 2013 at 8:35 am

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

November 15, 2013 at 8:42 am

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.


November 15, 2013 at 8:46 am

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