CODEWITHSUNDEEP
awk
ygoe
ygoe

Reputation: 20384

Modify awk parameter with other command

I have a file like this that contains multiple fields in lines. I want to display some of them, while processing one of them with another command.

TITLE,OpenVPN ...
HEADER,CLIENT_LIST,Common Name,Real Address,Virtual Address,Virtual IPv6 Address,Bytes Received,Bytes Sent,Connected Since,Connected Since (time_t),Username,Client ID,Peer ID
CLIENT_LIST,name1,1.1.1.1:1,10.0.0.1,,2692253,3765861,Wed Jun 23 12:51:08 2021,1624452668,name1,4727,0
CLIENT_LIST,name2,2.2.2.2:2,10.0.0.2,,1571221,2080242,Thu Jul  1 19:24:10 2021,1625167450,name2,5625,0
CLIENT_LIST,name3,3.3.3.3:3,10.0.0.3,,2670410,3736957,Wed Jun 23 16:20:51 2021,1624465251,name3,4747,0
...

The expected output is this:

name1  10.0.0.1  2021-06-23 12:51:08
name2  10.0.0.2  2021-07-01 19:24:10
name3  10.0.0.3  2021-06-23 16:20:51

The command I have now is this:

grep '^CLIENT_LIST,' /var/run/ovpn-server.status |awk -F',' '{print $2 $4 $9}' |sort

It prints the desired fields, but doesn't convert the timestamp to a formatted time. Here's the command for that:

date -d @1624452668 +"%Y-%m-%d %H:%M:%S"

How can I integrate the date command into the awk script? Or what other solution is there to accomplish this?

I also intend to put the output into a columns/table layout with the column command, I've done that before, so that's not part of the question.

Upvotes: 1

Views: 133

Answers (2)

anubhava
anubhava

Reputation: 785216

You may use this awk:

awk -F, -v OFS='\t' '$1 == "CLIENT_LIST" {
   cmd = "date +\047%Y-%m-%d %H:%M:%S\047 -d\047@" $9 "\047"
   print $2, $4, ((cmd | getline dt) > 0 ? dt : $9)
   close(dt)
}' file

name1   10.0.0.1    2021-06-23 08:51:08
name2   10.0.0.2    2021-07-01 15:24:10
name3   10.0.0.3    2021-06-23 12:20:51

Explanation:

  • -F, -v OFS='\t': Sets input field separator as , and output field separator as tab
  • '$1 == "CLIENT_LIST": Do it when first field is CLIENT_LIST
  • cmd = "date +\047%Y-%m-%d %H:%M:%S\047 -d\047@" $9 "\047": Format date command using $9
  • cmd | getline dt invokes external date command
  • (cmd | getline dt) > 0: When date command is a success
  • print: prints 2nd, 4th and output of date field

Upvotes: 5

Ed Morton
Ed Morton

Reputation: 203645

If you actually just want the date+time from $8 reformatted instead of converting the seconds since the epoch from $9 to a date+time then you can just do the following which will be orders of magnitude faster than calling date since that would require awk to spawn a subshell once per input line to call date from that subshell which would be extremely slow.

Using any awk in any shell on every Unix box:

$ cat tst.awk
BEGIN { FS=","; OFS="\t" }
NR > 2 {
    split($8,t," ")
    mthNr = (index("JanFebMarAprMayJunJulAugSepOctNovDec",t[2])+2)/3
    print $2, $4, sprintf("%04d-%02d-%02d %s", t[5], mthNr, t[3], t[4])
}


$ awk -f tst.awk file
name1   10.0.0.1        2021-06-23 12:51:08
name2   10.0.0.2        2021-07-01 19:24:10
name3   10.0.0.3        2021-06-23 16:20:51

or if you really want to use the epoch seconds from $9 then use GNU awk for strftime() so you don't have to spawn subshells to call date (but note that the output now becomes TZ-dependent, just like with date):

$ cat tst.awk
BEGIN { FS=","; OFS="\t" }
NR > 2 {
    print $2, $4, strftime("%F %T",$9)
}


$ awk -f tst.awk file
name1   10.0.0.1        2021-06-23 07:51:08
name2   10.0.0.2        2021-07-01 14:24:10
name3   10.0.0.3        2021-06-23 11:20:51


$ TZ=UTC awk -f tst.awk file
name1   10.0.0.1        2021-06-23 12:51:08
name2   10.0.0.2        2021-07-01 19:24:10
name3   10.0.0.3        2021-06-23 16:20:51

or setting the UTC flag in strftime() if UTC is what you have in your data:

$ cat tst.awk
BEGIN { FS=","; OFS="\t" }
NR > 2 {
    print $2, $4, strftime("%F %T",$9,1)
}


$ awk -f tst.awk file
name1   10.0.0.1        2021-06-23 12:51:08
name2   10.0.0.2        2021-07-01 19:24:10
name3   10.0.0.3        2021-06-23 16:20:51

Upvotes: 1

Related Questions