I have the data exported from Sql query into a file temp.log.

Below is the sample data. The entries after the header line are sorted on the first column.

Also, consider the nodes (36,54,69,76) as fixed list

<Name> <Status> <Node> <Requests>
AString1 Success 36 1
AString1 Success 54 4
AString1 Success 69 3
AString1 Success 76 4
BString2 Success 36 1
BString2 Success 54 2
BString2 Success 69 3
BString2 Success 76 1
CString3 Success 36 8
CString3 Success 54 7
CString3 Success 76 8
DString4 Success 36 8
DString4 Success 54 4
DString4 Success 69 12
DString4 Success 76 7

I need output as below

<Name> <Nodes> <Count of Requests>
Astring1 36 54 69 76 12
BString2 36 54 69 76 7
CString3 36 54 NA 76 23
DString4 36 54 69 76 31

Here the Last Column (<Count of Requests>) indicates the Total Count of requests on each node of the Name.

NA should be printed if a node didn't receive any requests.(Can see for CString3 row in o/p, it has one row missing for node value 69, in that case it should be NA, like CString3 36 54 NA 76 23

In success case DString4 - DString4 36 54 69 76 31

Can this be done with awk command?


You can use two indexed arrays to store the count of requests and the existing nodes for each name, and before printing, you can compare the array of existing nodes with your fixed array of nodes, for example:

awk 'BEGIN {
       totalNodes="36 54 69 76"
     NR==1 {print "<Name> <Nodes> <Count of Requests>"};
     NR>1 {
         count[$1]+=$4; nodesTmp[$1]=nodesTmp[$1]" "$3;
         for (i in count) {
             if(totalNodes!=nodesTmp[i]) {
                 for(j in tmp){
                         nodes[i]=nodes[i]" NA"
                     else {
                         nodes[i]=nodes[i]" "tmp[j]
             print i, nodes[i], count[i] | "sort"
     }' temp.log
  • I removed the space before 36 in the line totalNodes=" 36 54 69 76"... and this solution worked for me :) please edit so that I can accept your answer – Jman91 Nov 17 '20 at 5:22
  • Space removed :) – Dani Garcia Nov 17 '20 at 7:59
$ cat tst.awk
NR==1 {
    print "<Name>", "<Nodes>", "<Count of Requests>"
$1 != prev {
    if ( NR > 2 ) {
    prev = $1
    numReqs += $4
END { prt() }

function prt(   i, n, node, nodes) {
    n = split("36 54 69 76", nodes)

    printf "%s ", prev
    for (i=1; i<=n; i++) {
        node = nodes[i]
        printf "%s ", (node in reqs ? node : "NA")
    print numReqs

    delete reqs
    numReqs = 0

$ awk -f tst.awk file
<Name> <Nodes> <Count of Requests>
AString1 36 54 69 76 12
BString2 36 54 69 76 7
CString3 36 54 NA 76 23
DString4 36 54 69 76 31

Here's another awk solution:

$ awk '{if(NR==1){printf "%s\t%s\t%s\n", "<Name>","<Nodes>","<Count of Requests>"} else{nodes[$3]=1; a[$1][$3]++; sum[$1]+=$4}}END{for(string in a){ printf "%s\t", string; for(i in nodes){ (a[string][i]) ? val=i : val="NA"; printf "%s ",val }; printf "\t%d\n",sum[string];}}' file
<Name>  <Nodes> <Count of Requests>
CString3    36 54 NA 76     23
DString4    36 54 69 76     31
AString1    36 54 69 76     12
BString2    36 54 69 76     7

I am adding a tab character between each entry ("Name", "Nodes" and "Count of Requests") to allow you to easily separate them later.

And here's the same script in a more legible form (you can still copy/paste this directly into a terminal):

awk '{ 
        ## If this is the first line
            ## Set up the nodes array with the desired values
            ## Print the header
            printf "%s\t%s\t%s\n", "<Name>","<Nodes>","<Count of Requests>"
        ## For all except the first line
            ## Save this node in the nodes array
            ## add this node to the values found for this string
            ## add the number of requests
    ## After we have finished reading the file
        ## a holds all the first fields, the various strings
        for(string in a){ 
            ## Print the current string and a tab    
            printf "%s\t", string; 
            ## For each target node
            for(i in nodes){ 
                ## If this node has a value for this string, use the value. 
                ## If it does not, use "NA".
                (a[string][i]) ? val=i : val="NA"; 
                ## Print the value for the node
                printf "%s ",val 
            ## Print the sum
            printf "\t%d\n",sum[string];
    }' file

Assuming GNU awk for PROCINFO and true double-index arrays, the following will work:

gawk 'BEGIN{PROCINFO["sorted_in"]="@ind_str_asc"}
      FNR>1{nodes[$3]=1; total[$1]+=$4; sgl_nodes[$1][$3]=1}
        printf "<Name> <Nodes> <Count of Requests>\n"
        for (f in sgl_nodes) {
          printf "%s%s",f,OFS;
          for (g in nodes) {
            if (sgl_nodes[f][g]) printf "%s%s",g,OFS; else printf "NA%s",OFS
          printf "%d%s",total[f],ORS
      }' temp.log 

This will register the actually encountered node numbers in an array nodes, and those associated with every single name in the two-dimensional array sgl_nodes, stored with the value of <Name> as first index. While processing, it will also sum the total request count in the array total, under array index corresponding to the <Name> value.

At end-of-file, we first print the header line, and then iterate over the sgl_nodes array to print for each <Name> value (first index of sgl_nodes) the name, and then iterate over all nodes registered in nodes to print either the node number (if encountered for that particular <Name>) or NA (if that node was missing), and finally the total request count.

<Name> values and are printed in ascending order thanks to the PROCINFO setting performed in the BEGIN section, which is overriden to numerical sorting for iterating over the nodes array.

This approach does not rely on the temp.log entries being sorted, and it recognized the node numbers automatically.

The advantage of recognizing the node numbers automatically turns into a problem if one of the node numbers happens to be missing from all entries. Since you stated that the node list can be considered fixed, however, we can resort to hard-coding that list to avoid this problem:

gawk 'BEGIN{PROCINFO["sorted_in"]="@ind_str_asc";split("36 54 69 76",nodes)}
      FNR>1{total[$1]+=$4; sgl_nodes[$1][$3]=1}
        printf "<Name> <Nodes> <Count of Requests>\n"
        for (f in sgl_nodes) {
          printf "%s%s",f,OFS;
          for (g=1;g<=4;g++) {
            if (sgl_nodes[f][nodes[g]]) printf "%s%s",nodes[g],OFS; else printf "NA%s",OFS
          printf "%d%s",total[f],ORS
      }' temp.log 
  • 2
    That would fail if any given node number wasn't present in any of the records in the input file as it wouldn't print the column at all instead of printing "NA" for every node value in the column. Try removing every 69 row from the input (like already is the case for the CString3 record) to see what I mean. Assuming the OP always wants the node numbers in incremental order, you'll want to set PROCINFO to numeric ascending before the loop on node numbers btw so it works even when the nodes aren't all 2-digit numbers. – Ed Morton Nov 16 '20 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.