4

I'm on CentOS. I have a list of files that I want to read, extract data from them and organize it as a csv file.

The log files text format is:

...
{"name":"test-api","hostname":"ci47","pid":3202,"level":30,"msg":"File: dsiManager, Method: getContract, End { userId: 'AFC5EH5PIHHLO4XS7SG',\n  clientId: '5003700557',\n  intent: 'YesIntent',\n }","time":"2019-01-21T12:23:10.323Z","v":0}
...

Output format must be:

clientId;intent;time;userId
5003700557;YesIntent;2019-01-21T12:23:10.323Z;AFC5EH5PIHHLO4XS7SG

What is the most simple way to get this task done? (awk, grep...)

  • Sorry I missed ''json alike'' in title. I corrected the title. Thx ! – tamerbak Apr 3 at 14:59
  • I just surprised you wanted most simple way but accepted most complex way, lol – αғsнιη May 12 at 9:05
2

To parse JSON-encoded data robustly, you will need a JSON codec. This pretty much means Perl or Python (or Ruby ...). Since I'm a Perl guy, here's a Perl solution.

First off a one-liner:

$ perl -MJSON -ne 'BEGIN { print("clientId;intent;time;userId\n"); } eval { my $obj = from_json($_); my $msg = $obj->{msg}; $msg =~ s/^.*{\s*|\s*,\s*}.*$//g; my %m = map { m/^([^:]*):\s*(.*)/; ($1, $2) } split(/,\s+/, $msg); print("$m{clientId};$m{intent};$obj->{time};$m{userId}\n"); }; warn($@) if ($@);' <x
clientId;intent;time;userId
5003700557;YesIntent;2019-01-21T12:23:10.323Z;AFC5EH5PIHHLO4XS7SG

Since that is a little excessive, even for Perl, here's a readable script as well:

#!/usr/bin/perl

use strict;
use warnings;
use JSON;

print("clientId;intent;time;userId\n");
while (<>) {
    # Don't choke on malformed lines
    eval {
        my $obj = from_json($_);
        my $msg = $obj->{msg};
        $msg =~
            s/^.*{\s*    # Trim up to and including the leading '{'
            |
            \s*,\s*}.*$  # Trim trailing ',}'
            //gx;
        # Split $msg into key-value pairs
        my %m = map {
            m/^([^:]*)   # Stuff that isn't ':'
            :\s*         # Field separator
            (.*)         # Everything after the separator
            /x;
            ($1, $2)
        } split(/,\s+/, $msg);
        print("$m{clientId};$m{intent};$obj->{time};$m{userId}\n");
    };
    warn($@) if ($@);
}
1

Try this,

awk -F "['\"]" 'NF>=26{print $19","$21","$26","$17}' file.csv


5003700557,YesIntent,2019-01-21T12:23:10.323Z,AFC5EH5PIHHLO4XS7SG
  • ['\"] to have both single and double quote as delimiters.
  • NF>=26 just to check the line has more than or equal to 26 fields.
0

I used awk command. my problem was that every line is different from others.. So I did not have an idea about column numbers; I resolved this by adding a test to find the correct line number to show. Here is my code :

awk ' 
BEGIN {
  # Set awk script delimiter
  FS=","; 
  # Set CSV file separator
  OFS=";"; 
  # Set header part in csv file
  print "Method; UserId; ClientId; intent; time"
  } 
  /'clientId'/ 
  { 
    i=1; 
    msg=""; 
    while(i<=NF) { 
      if ($i ~ /clientId/) { 
        # Cleaning column value :
        gsub(/\\n\s{1,}clientId:\s/, "",$i); 
        msg = msg $i ";"
      };  
      if ($i ~ /"time"/) { 
        # Cleaning column value :
        gsub(/"time":/, "",$i); 
        msg = msg $i ";"
      }; 
      if ($i ~ /intent/) { 
        # Cleaning column value :
        gsub(/\\n\s{1,}intent:\s{1,}/, "",$i); 
        msg = msg $i ";"
      }; 
      if ($i ~ /Method/) { 
        # Cleaning column value :
        gsub(/(^(.*?)|\s{1,})Method\s{1,}?:?\s{1,}/, "",$i); 
        gsub (/(\s{1,}\{\s{1,}userId.*)?/, "", $i);  
        msg = msg $i ";"
      }; 
      if ($i ~ /userId/) { 
        # Cleaning column value :
        gsub(/(^(.*?)|\s{1,})userId:\s/, "",$i); 
        msg = msg $i ";"
      }; 
      i++
    } print msg
  } 
END {
  print NR
} ' 

$(grep -l id *.log) >> output.csv
  1. I used gsub() method to clean somme column values, because I had dirty old log files
  2. $(grep -l id *.log) command is used to list all awk input log files

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.