it's me again, the guy with the huge awk
one-line commands... This one is even longer:
awk -F, 'BEGIN{print"\"Source\",\"Duration Time\""}NR>1{gsub(/"/,"",$2);split($2,hms,":");s=hms[1]*3600+hms[2]*60+hms[3];if(!(($1,"MAX")in a)||a[$1,"MAX"]<s)a[$1,"MAX"]=s;if(!(($1,"MIN")in a)||a[$1,"MIN"]>s)a[$1,"MIN"]=s}END{for(idx in a){split(idx,ipm,SUBSEP);if(ipm[2]=="MAX"){d=a[idx]-a[ipm[1],"MIN"];h=int(d/3600);m=int((d-h*3600)/60);s=d%60;printf("%s,\"%02d:%02d:%02d\"\n",ipm[1],h,m,s)}}}' fb.csv
With your given fb.csv
input file from the question, the output looks like this:
"Source","Duration Time"
"157.240.10.23","00:00:12"
"157.240.10.18","00:00:00"
"157.240.10.13","00:00:00"
"10.0.138.163","00:00:37"
"192.168.137.174","00:00:42"
Explanation of the command:
We run awk
like this here, setting the field separator that delimits columns to ,
and using the file fb.csv
as input:
awk -F, '<COMMAND>' fb.csv
The awk
command (placeholder <COMMAND>
above) is this, after proper formatting:
BEGIN {
print "\"Source\",\"Duration Time\""
}
NR>1 {
gsub(/"/, "", $2)
split($2, hms, ":")
s = hms[1]*3600 + hms[2]*60 + hms[3]
if ( !(($1,"MAX") in a) || a[$1,"MAX"] < s )
a[$1,"MAX"] = s
if ( !(($1,"MIN") in a) || a[$1,"MIN"] > s )
a[$1,"MIN"] = s
}
END {
for (idx in a) {
split(idx, ipm, SUBSEP)
if (ipm[2]=="MAX") {
d = a[idx] - a[ipm[1],"MIN"]
h = int(d / 3600)
m = int((d - h * 3600) / 60)
s = d%60
printf("%s,\"%02d:%02d:%02d\"\n", ipm[1] ,h ,m ,s)
}
}
}
The BEGIN
block simply prints the new CSV header.
The NR>1
block runs once per line in the input file, except for the first line which contains the header. Each line gets split into the IP column ($1
) and the time column ($2
).
We process the time column by stripping the quotes with gsub
and splitting it at the colons into an array hms
which contains the hours, minutes and seconds. This is used to convert the time stamp to seconds since midnight, stored in s
in this block.
Next we check the associative array if it does not have an entry with the current line's IP yet or if the entry has smaller MAX or larger MIN time values, in which case it will be updated accordingly.
Finally, in the END
block the created array gets evaluated and for each IP in it, the difference between the MAX and MIN time stamps is calculated and saved as d
. This gets converted back into hours, minutes and seconds and outputted properly formatted.
source='"192.168.137.174"';sed -e /$source/\!d -e 's/.*"\([0-9]*\)"$/\1/' fb.csv | sort -r | sed ':a;N;$!ba;s/\n/-/g;s/-.*-/-/' | bc | sed 's/.*/'$source',"&"/'
– and to get a list of the different sources for the loop to work through:sed '1d;s/,.*//' fb.csv | sort -u
. I betperl
orawk
can do better though… – dessert Sep 14 '17 at 20:14