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 betperlorawkcan do better though… – dessert Sep 14 '17 at 20:14