2

I have a file with the following content (there are additional spaces to pad the first column)

  1 account1 192.168.0.1  
  1 account1 192.168.0.2   
 19 account2 192.168.0.1   
100 account3 192.168.0.3   
  1 account3 192.168.0.5

And I am trying to get to an output of

  2 account1 192.168.0.1, 192.168.0.2  
 19 account2 192.168.0.1  
101 account3 192.168.0.3, 192.168.0.5   

which I will loop through so that I can create an html table to email to an account. I managed to get the list of IPs per account using :

awk '{a[$2]=a[$2]" " $3}END{for (i in a) print i" " a[i]}' inputfile

but I cannot get to sum the first column as well.

3

Adding the sum to your script shouldn't be too hard, just add another array to hold it.

$ awk '{ if (a[$2]) a[$2] = a[$2] ", ";
         a[$2] = a[$2] $3;
         sum[$2] += $1 }
       END {for (x in a) printf "%3d %s %s\n", sum[x], x, a[x]}' inputfile
  2 account1 192.168.0.1, 192.168.0.2
 19 account2 192.168.0.1
101 account3 192.168.0.3, 192.168.0.5 

(Your sample output had the IP address separated by commas, so I added that too. Though at least the code is neater without it, in my opinion.)

2

Awk solution:

awk 'NR==1{ match($0, /^ +[^ ]+/); s=length(substr($0, RSTART, RLENGTH)) }
     { sum[$2]+=$1; ips[$2]=($2 in ips? ips[$2]", ":"")$3 }
     END{ 
         for (i in sum) 
             printf("%*s %s %s\n", s, sum[i], i, ips[i]) 
     }' file

The output:

  2 account1 192.168.0.1, 192.168.0.2
 19 account2 192.168.0.1
101 account3 192.168.0.3, 192.168.0.5
1

I'm probably overthinking this, (there's likely a nice awk/sed to be used) but this is the best thing I can come up with:

#!/bin/bash
# set file as sys argument
file=$1

# pull unique account names into an array
account_names=($(awk '{print $2}' $file | sort | uniq))

# loop through and store column values
for account in ${account_names[@]}; do
    # get a sum of the first column
    col1=$(grep $account $file | awk '{SUM += $1} END {print SUM}')
    # get last column and convert to one line with comma separation
    IP_list=$(grep $account $file | awk '{print $NF}' | sort | uniq | tr '\n' ',' | sed s'/.$//')
    # print them together
    echo "$col1 $account $IP_list"
done

Then you can run it like so:

┌─[robotjohny@Fedora]─[~]─[04:08 pm]
└─[$]› ./test1.sh file1.txt 
2 account1 192.168.0.1,192.168.0.2
19 account2 192.168.0.1
101 account3 192.168.0.3,192.168.0.5
  • 1
    this makes for a very good reusable function, thank you – C.Mallia Jan 24 '18 at 12:11

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.