troubleshooting Question

Bash one liner, start and end time calculation

Avatar of Rich Rumble
Rich RumbleFlag for United States of America asked on
Shell ScriptingPerlScripting Languages
13 Comments1 Solution263 ViewsLast Modified:
I've got file full of VPN logs. There are 2 to 3 lines that I'd like to combine into one with a perl/awk/sed what have you script.
I'd like to go from this:
id=firewall time="2016-01-01 09:34:57" pri=6 fw=10.0.16.11 vpn=vpn1-ssl user=jdoe@example.com realm="MPKI" roles="Role_PC" proto= src=15.16.3.132 dst= dstname= type=vpn op= arg="" result= sent= rcvd= agent="" duration= msg="NWC23464: VPN Tunneling: Session started for user with IPv4 address 10.26.115.42, hostname pc-lt-sfo-55321"
id=firewall time="2016-01-01 10:32:07" pri=6 fw=10.0.16.11 vpn=vpn1-ssl user=jdoe@example.com realm="MPKI" roles="Role_PC" proto=auth src=15.16.3.132 dst= dstname= type=vpn op= arg="" result= sent= rcvd= agent="" duration= msg="AUT22673: Logout from 15.16.3.132 (session:ff61620b)"
id=firewall time="2016-01-01 10:32:07" pri=6 fw=10.0.16.11 vpn=vpn1-ssl user=jdoe@example.com realm="MPKI" roles="Role_PC" proto= src=15.16.3.132 dst= dstname= type=vpn op= arg="" result= sent=8360966 rcvd=20520833 agent="" duration=3431 msg="NWC23465: VPN Tunneling: Session ended for user with IPv4 address 10.26.115.42"
to this:
   (Hostname, Username, Start-Time, End-Time, Sent, Received)
   pc-lt-sfo-55321, jdoe@example.com, 2016-01-01 09:34:57, 2016-01-01 10:32:07,  sent=8.36Mb, rcvd=20.52Mb

The line with msg="NWC23464" is the only line with the hostname in the log. Otherwise, the line with  msg="NWC23465" is the only one I care about. I can use Cut to parse the 23465 line into most of it's important parts, but I don't know how to do math on the SENT/RECIEVED data.

Again its a log filled with other entries just like this one, I'd like to "cat" the file and pass it through sed/awk/perl (I'm open to any), extract the hostname from the 23464 msg line, write the start time based on the value in 23465's duration field (duration written in seconds) then write the end time, and do math on the sent and received fields. (subtract duration seconds from the time stamp at the beginning of the 23465 line)
This is what I have working so far, it's close, but it does not output to one line or do the math:
cat 2016-20k.txt |grep NWC2346[4-5] |cut -d" " -f2,3,6,7,11,18,19,21,32,34 |sed 's/"//g;s/ /-/;s/^time=//;s/ vpn=/, /;s/ user=/, /;s/ src=/, /;s/sent= rcvd= duration= //;'|LC_ALL='C' sort -t, -k3 -n
2=date, 3=time, 6=vpn-device, 7=user, 11=ip, 18=sent, 19=recieved (bytes), 21=duration, 32=hostname, 34=ip
With all the other entries, sort is not pairing the begin/end times as well as I'd hoped. I've tried every variation on sort's column order (-k3, -k1 etc). Any ideas on how to do this better, and with a one liner if at all possible?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros