Help with Sort-Object

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

  • Author
    Posts
  • #11505
    Profile photo of Stephen Yeadon
    Stephen Yeadon
    Participant

    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

  • #11506
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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
    Profile photo of Stephen Yeadon
    Stephen Yeadon
    Participant

    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

  • #11508
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

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

You must be logged in to reply to this topic.