3

I'm trying to find a way to convert timestamps in log files to a Unix timestamp. The command I have come up with so far is the following:

awk -F'[' '{ print $2}' | awk -F']' '{cmd ="date \"+%s\" -d \""$1"\""; cmd | getline var; print var $2; close(cmd)}'

The command works when the original timestamp includes UTC as the timezone or if no timezone is specified. However, it fails when the timezone is something else. For example, this works:

$ entry="[08-May-2020 15:40:32 UTC] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290"
$ echo "$entry" | awk -F'[' '{ print $2}' | awk -F']' '{cmd="date \"+%s\" -d \""$1"\""; cmd | getline var; print var $2; close(cmd)}'
1588952432 PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290

And the command also works when no timezone is specified:

$ entry="[08-May-2020 15:40:32] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290"
$ echo "$entry" | awk -F'[' '{ print $2}' | awk -F']' '{cmd="date \"+%s\" -d \""$1"\""; cmd | getline var; print var $2; close(cmd)}'
1588948832 PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290

However, when the timezone is Europe/London it fails:

$ entry="[08-May-2020 15:40:32 Europe/London] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290"
$ echo "$entry" | awk -F'[' '{ print $2}' | awk -F']' '{cmd="date \"+%s\" -d \""$1"\""; cmd | getline var; print var $2; close(cmd)}'
date: invalid date ‘08-May-2020 15:40:32 Europe/London’
 PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290

I'm really not sure how to debug awk command. I suspect it may not like the slash in the timezone, but that's just a guess.

Improve this question
6
  • 3
    The date command is not able to parse the string 08-May-2020 15:40:32 Europe/London as a date.
    – Freddy
    May 8, 2020 at 17:27
  • @Freddy Thanks, is there a particular reason why it can't parse that string but can parse a string like 08-May-2020 15:40:32 UTC? I'm trying to learn more about awk and converting dates.
    – rkhff
    May 8, 2020 at 17:36
  • 2
    It's not supported, at least not in GNU date and I don't know whether implementations exist that do. But you could call date +"%s" -d"TZ=\"Europe/London\" 08-May-2020 15:40:32" which is accepted, see Specifying time zone rules.
    – Freddy
    May 8, 2020 at 18:08
  • No, @Freddy, the Europe/London is a perfectly valid Olson database value that GNU date is perfectly able to parse (and understand). Please read my answer.
    – user232326
    May 8, 2020 at 20:55
  • 1
    @Isaac, please read my last comment again :)
    – Freddy
    May 8, 2020 at 21:02

2 Answers 2

Reset to default
3

The way in which date could receive a TZ variable (and understand it) is quite tricky. This command works:

 $ date -d 'TZ="UTC" 08-May-2020 15:40:32' +"%s"
1588952432

As well as a TZ from the Olson database:

$ date -d 'TZ="Europe/London" 08-May-2020 15:40:32' +"%s" 
1588948832

And note that the epoch times are different 1588952432 and 1588948832. One (1) hour difference due to London being -1 and UTC being 0.

Understand that the format is quite strict, TZ first, all inside single quotes and the TZ value also inside double quotes. And, being so strict, also quite brittle.

So, setting the values in an array (assuming bash, ksh or zsh):

entry=( 
        "[08-May-2020 15:40:32 UTC] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290"
        "[08-May-2020 15:40:32] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290"
        "[08-May-2020 15:40:32 Europe/London] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290"
      )

We can then get all values from date with awk (note the different FS for just one call to awk (technically speaking: GNU awk, or nawk) and the use of split due to varying number of elements of the time string):

printf '%s\n' "${entry[@]}" | awk -F '[][]' '{
    n=split($2, val, / /, sep);
    cmd=sprintf("date +\"%%s\" -d '\''TZ=\"%s\" %s %s'\''",val[3],val[1],val[2]);
    cmd | getline var; close(cmd);
    print "["var"]"$3;
}'
[1588952432] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290
[1588952432] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290
[1588948832] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290

Note that technically a line like:

if ( (rc=(cmd | getline var)) != 1){ 
     print "error on calling the command date ",rc; exit 
};

Would catch some errors of getline (basically getline unable to get an output from the command) but awk is unable to report nor act on an error number from the command. It is the responsibility of the command to break execution if that is what is required. The only thing that awk does (error or not) is to pass the stderr output of the cmd directly to its stderr. So, you will see any comment (or error) raised by the command on the stderr of awk. Make sure to check and process those if needed. If not, the output file will get silently corrupted. You have been warned!. Which, seems to be what you are asking for.

No, awk datetime() isn't able to understand TZ times and, even less, values from the Olson database.

Improve this answer
3
  • This is very cool. The only issue I had with your example is that it outputs the timestamp without the error message. I got the latter included using echo "$entry" | awk -F'[][ ]' '{ cmd=sprintf("date +\"%%s\" -d '\''TZ=\"%s\" %s %s'\''",$4,$2,$3); cmd | getline var; {FS=" "}; printf var FS ; {for(i=6;i<=NF;i++) printf $i FS}; printf "\n"; close(cmd)}'. Not sure if that's the best solution but it's neat that it's all in a single awk command.
    – rkhff
    May 8, 2020 at 22:33
  • 1
    @rkhff I updated the answer to give the modified output you ask for.
    – user232326
    May 9, 2020 at 3:22
  • Let us continue this discussion in chat.
    – user232326
    May 10, 2020 at 17:16
2

@Isaac is on the right track and please do leave his as the accepted answer since it'll probably work fine for your input, but I'd do it like this to catch cases where the input doesn't start with the expected [date+time] string, the call to date fails or getline fails otherwise, so it succeeds if there's a ] in the message text that comes after the timestamp, and so it exits with a failure exit status if any part of the execution fails:

$ cat tst.awk
match($0,/\[[^]]+] /) {
    dt  = substr($0,RSTART+1,20)
    tz  = substr($0,RSTART+22,RLENGTH-24)
    msg = substr($0,RSTART+RLENGTH)

    cmd = sprintf("date -d \047TZ=\"%s\" %s\047 +\047%%s\047", tz, dt)
    cmd | getline secs
    close(cmd)
}
secs == "" {
    printf "%s[%d]: failed to convert: \"%s\"\n", FILENAME, NR, $0 | "cat>&2"
    exit 1
}
{ print secs, msg; secs="" }

e.g. given this input file (note the last 2 lines - the first has a ] in the message part and the second has an invalid date):

$ cat file
[08-May-2020 15:40:32 UTC] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290"
[08-May-2020 15:40:32] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290
[08-May-2020 15:40:32 Europe/London] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 290
[08-May-2020 15:40:32 Europe/London] PHP Warning:  array foo[] is bad, the sky is falling
[08-Bob-2020 15:40:32 Europe/London] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 290

$ awk -f tst.awk file
1588952432 PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290"
1588952432 PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 1290
1588948832 PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 290
1588948832 PHP Warning:  array foo[] is bad, the sky is falling
date: invalid date ‘TZ="Europe/London" 08-Bob-2020 15:40:32’
file[5]: failed to convert: "[08-Bob-2020 15:40:32 Europe/London] PHP Warning:  Illegal string offset 'ID' in /home/example/public_html/wp-content/themes/example/functions.php on line 290"

$ echo "$?"
1

That last part of exiting with a failure status matters so you can write a script like awk 'script' file > tmp && mv tmp file without having to worry about the awk output overwriting your input file even when it fails or you can otherwise test the result of awk 'script' file

Improve this answer
2
  • 1
    Thanks, that's a timely contribution. I was trying to use Isaac's command to parse a log file that uses a different format for the timestamps and where error messages include square brackets (2020-05-11 16:35:26.450410 [INFO] [3917633] [3.9.191.231:58338#APVH_example.com] File not found [/home/example/public_html/foo]). That outputs 1589214926 [INFO]. I can work around that though.
    – rkhff
    May 11, 2020 at 16:41
  • 1
    Also, having read your chat with Isaac, I have taken care not to overwrite the input file! I'm using it for a script that retrieves log entries that happened in the last x minutes, and it writes the output of the awk command to a temporary file. Good point though, this could wreck the input file.
    – rkhff
    May 11, 2020 at 16:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .