5

I am trying to perform a sort by 3 different columns in the correct order on an AIX system.

The below is an extract of a larger file that displays the longest running runtimes between the open brackets e.g. (0 hrs 0 mins 1.030 Secs). Keep in mind that the 1st column will not be in order as the output to the file runs in parallel. So below is the 10 longest running processes out of 2500 lines, which I extracted. Now I would like to sort the top ten longest running process from shortest time to longest time:

2023-01-04 12:32:08:       Table seqhi completed     (0 hrs 0 mins 1.020 Secs)
2023-01-04 12:32:09:       Table iinvd completed     (0 hrs 0 mins 1.028 Secs)
2023-01-04 12:32:10:       Table iaudl completed     (0 hrs 0 mins 1.030 Secs)
2023-01-04 12:32:11:       Table ccdd_save completed    (1 hrs 0 mins 1.021 Secs)
2023-01-04 12:32:13:       Table upi_brordrep_tmp_aj completed       (0 hrs 0 mins 1.028 Secs)
2023-01-04 12:32:07:       Table srdel completed     (0 hrs 2 mins 1.592 Secs)
2023-01-04 12:32:09:       Table iibt completed      (0 hrs 0 mins 1.020 Secs)
2023-01-04 12:32:10:       Table atprdd completed    (0 hrs 0 mins 1.018 Secs)
2023-01-04 12:33:43:       Table atseld completed    (0 hrs 1 mins 33.868 Secs)
2023-01-04 12:32:10:       Table abc_irctd completed         (0 hrs 0 mins 1.029 Secs)

I want to sort the above by hours minutes and seconds like below:

2023-01-04 12:32:10:       Table atprdd completed    (0 hrs 0 mins 1.018 Secs)
2023-01-04 12:32:08:       Table seqhi completed     (0 hrs 0 mins 1.020 Secs)
2023-01-04 12:32:09:       Table iibt completed      (0 hrs 0 mins 1.020 Secs)
2023-01-04 12:32:09:       Table iinvd completed     (0 hrs 0 mins 1.028 Secs)
2023-01-04 12:32:13:       Table upi_brordrep_tmp_aj completed       (0 hrs 0 mins 1.028 Secs)
2023-01-04 12:32:10:       Table abc_irctd completed         (0 hrs 0 mins 1.029 Secs)
2023-01-04 12:32:10:       Table iaudl completed     (0 hrs 0 mins 1.030 Secs)
2023-01-04 12:33:43:       Table atseld completed    (0 hrs 1 mins 33.868 Secs)
2023-01-04 12:32:07:       Table srdel completed     (0 hrs 2 mins 1.592 Secs)
2023-01-04 12:32:11:       Table ccdd_save completed    (1 hrs 0 mins 1.021 Secs)

I tried a few sort commands but struggle to get what I would like. How can this be done?

5
  • Why is 12:33:43: sorted after 12:32:10:? Also, if you are using AIX pelase mention that in the question instead of using the tag so we can be sure. Finally, did you try sort?
    – terdon
    Commented Jan 4, 2023 at 11:12
  • The times on the 1st column is a parallel run to an output file so the 1st column will not be inorder. This is just an extact of many lines simliar to what you see above. I took a subsection of lines out of the file as an example for what I want to achieve. Commented Jan 4, 2023 at 11:16
  • Please edit your question and clarify that, specifically tell us what separator your file uses, do you have tabs? Also tell us what OS you are using and if you have tried anything so far. This will require a script to do properly. Do you have perl or python installed?
    – terdon
    Commented Jan 4, 2023 at 11:26
  • This is an AIX server. Perl is installed. I don't use python on aix Commented Jan 4, 2023 at 11:41
  • Are the 3 main columns separated by tab ?
    – JJoao
    Commented Jan 4, 2023 at 17:40

4 Answers 4

6

Assuming the text between the timestamp and the duration always has 3 blank-separated words like in your sample, you could do:

<your-file LC_ALL=C sort -nb -k6.2,6 -k8,8 -k10,10

The default separator for sort is the transition between a non-blank and a blank, so sort keys without -b would include the leading blanks. By using -b to strip those, we make sure the first key specified as starting at the second character of the sixth field starts just after the (.

All keys are interpreted numerically. With LC_ALL=C, we make sure the decimal radix character is expected to be . regardless of the locale of the user.

Note that it assumes the seconds and minutes parts don't exceed 60. For instance, it would rank (0 hrs 1 mins 10.1 Secs) after (0 hrs 0 mins 120.592 Secs) even though the latter is longer.

Pipe to tail to get the top-ten.


If the sort keys can't be fields or parts of fields with fixed offset, the common approach is to extract the keys using some other tools, copy them at the start of the line, sort and remove them afterwards in a decorate-sort-undecorate fashion:

d='\([[:digit:]]\{1,\}\)'
<your-file sed -n "s/^.*($d hrs $d mins $d\.$d Secs)\$/\1:\2:\3.\4:&/p" |
  LC_ALL=C sort -nt: -k1,1 -k2,2 -k3,3 |
  cut -d: -f4-

Or use perl which is the best tool for extraction and report and has a sort operator builtin. Randal L. Schwartz gave his name to the decorate-sort-undecorate idiom you'd typically use there:

<your-file perl -ne '
   push @records, [$_, $3 + 60 * ($2 + 60 * $1)]
     if /\((\d+) hrs (\d+) mins (\d+\.\d+) Secs\)$/;
   END {print $_->[0] for sort {$a->[1] <=> $b->[1]} @records}'

Or use @terdon's approach which deduplicates lines with identical duration first, saving some comparisons during sorting but at the expense of manipulating hash tables which might end up being counter productive in terms of efficiency and ends up losing the sorting stability.

7
  • The times will reflect correctly not exceeding 60 for minutes and seconds Commented Jan 4, 2023 at 11:42
  • @ChristopherKarsten how did this fail for you? Maybe the sort on AIX is missing one of the required options?
    – terdon
    Commented Jan 4, 2023 at 12:50
  • @ChristopherKarsten I'd be surprised if a program that's already apparently parsing the runtime into hours, minutes, and seconds suddenly decides to output values above 59 for the minutes or seconds fields. Or is your comment meant as a general warning?
    – Jeff Schaller
    Commented Jan 4, 2023 at 19:37
  • @JeffSchaller it is in answer to the warning in Stéphane's answer, see the paragraph starting with "Note that it assumes the seconds and minutes parts don't exceed 60."
    – terdon
    Commented Jan 5, 2023 at 13:20
  • Oh! It's the OP saying "that warning isn't a problem for my situation"; good! It seems to me then, that these comments are all no longer needed?
    – Jeff Schaller
    Commented Jan 5, 2023 at 13:38
4

A Perl approach:

$ perl -lne '/(\d+)\s*hrs\s*(\d+)\s*mins\s*([0-9.]+)\s*Secs/; 
             push @{$k{($1*60*60)+($2*60)+($3)}},$_; 
             }{ 
             for $t (sort {$a <=> $b} keys(%k)){
                print join "\n",@{$k{$t}}
            }; ' file
2023-01-04 12:32:10:       Table atprdd completed    (0 hrs 0 mins 1.018 Secs)
2023-01-04 12:32:08:       Table seqhi completed     (0 hrs 0 mins 1.020 Secs)
2023-01-04 12:32:09:       Table iibt completed      (0 hrs 0 mins 1.020 Secs)
2023-01-04 12:32:09:       Table iinvd completed     (0 hrs 0 mins 1.028 Secs)
2023-01-04 12:32:13:       Table upi_brordrep_tmp_aj completed       (0 hrs 0 mins 1.028 Secs)
2023-01-04 12:32:10:       Table abc_irctd completed         (0 hrs 0 mins 1.029 Secs)
2023-01-04 12:32:10:       Table iaudl completed     (0 hrs 0 mins 1.030 Secs)
2023-01-04 12:33:43:       Table atseld completed    (0 hrs 1 mins 33.868 Secs)
2023-01-04 12:32:07:       Table srdel completed     (0 hrs 2 mins 1.592 Secs)
2023-01-04 12:32:11:       Table ccdd_save completed    (1 hrs 0 mins 1.021 Secs)

This doesn't have the limitation of Stéphane's answer, and will correctly sort (0 hrs 1 mins 10.1 Secs) before (0 hrs 0 mins 120.592 Secs). On the other hand, that seems like a very unlikely problem and Stéphane's approach is both simpler and faster, so unless you know you have cases with >60 seconds, I would use that one instead.

1
  • Unfortunately Stéphane's approach does not work for me, however your perl solution does work. If there was an easier approach I would use something else... Commented Jan 4, 2023 at 11:50
0

The "sort" command that I am familiar with sorts in alphabetical order rather than numerical order (10,11,12 sorted before 6,7,8) so you may need to add "g" after the -k string (e.g. "-k8,8g" instead of "-k8,8") to get things to be sorted in numerical order.

1
  • g is a GNUism. Here you only need n (as a flag to the keys or globally with -n as in my answer) though which is standard. Commented Jan 6, 2023 at 21:01
0

Warning: the current answer's first line is a (GNU)sort command that wouldn't work on a stock AIX system. (sometimes, gnu-sort is also installed under the name of gsort)

sort -k6Vb example

Where:

  • -k6 is used do sort from filed 6 up to the end
  • Vb is version sort (sort that follows human intuition) ignoring leading blanks

(\thanks{Jeff and Stéphane})

6
  • 3
    AIX's sort doesn't have a -V ...
    – Jeff Schaller
    Commented Jan 4, 2023 at 18:14
  • 1
    -k6,100 is to sort from the start of the 6th field (including the leading blanks) to the end of the 100th. To sort from the 6th to the end, it's just -k6 (-bk6 to strip leading blanks from the field) Commented Jan 4, 2023 at 18:18
  • @JeffSchaller, thank you: it always difficult to deal with this kind of things. Should I delete My answer?
    – JJoao
    Commented Jan 4, 2023 at 18:59
  • @StéphaneChazelas, Thank you
    – JJoao
    Commented Jan 4, 2023 at 18:59
  • -V is a GNU extension. GNU sort can be installed on AIX. Might be available as gsort or as /opt/gnu/bin/sort, though likely not out of the box. Commented Jan 4, 2023 at 19:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .