We help IT Professionals succeed at work.
Get Started

Bash one liner, start and end time calculation

256 Views
Last Modified: 2016-01-06
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"

Open in new window

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

Open in new window

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?
Comment
Watch Question
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 13 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE