Help with Sort-Object

This topic contains 3 replies, has 2 voices, and was last updated by  Dave Wyatt 4 years, 4 months ago.

  • Author
  • #11505

    Stephen Yeadon

    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

  • #11506

    Dave Wyatt

    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}}
  • #11507

    Stephen Yeadon

    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.


  • #11508

    Dave Wyatt

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

You must be logged in to reply to this topic.