How to convert Log file to CSV using bash

Question

Is it possible to turn this log entry into a properly formatted csv file using sed/awk? I've tried multiple ways to do this using sed/awk. Alas I couldn't do it. Before I do it in perl. is this possible in AWK/sed or perhaps even bash

Jan 21 11:10:45 hostname.internal.nl THREAT 2015/01/21 11:10:44 critical 5 reset-both outside 10.10.10.10 inside 10.16.61.4 tcp 39898 80 web-browsing any Bash Remote Code Execution Vulnerability(36731)

Like this

Date  | Hostname | Threat | DATE+time | Critical/High | Count | --- | External IP | Internal IP | TCP/UDP | Port | External Port| Category | Vulnerability 

Jan 21 11:10:45 | hostname.internal.nl | THREAT | 2015/01/21 11:10:44 | critical 5 reset-both | outside 10.10.10.10 | inside 10.16.61.4 | tcp  39898 | 80 | web-browsing | 4any Bash Remote Code Execution Vulnerability(36731)

Solution

Yes, you can do it in bash but I have no idea why you would want to. Here's a pure bash solution:

$ while read -r mon day time host threat date time crit count sugg out exip \
                in inip tcp port export cat vuln; do 
     printf "%s | " "$mon $day $time" "$host" "$threat" "$date $time" \ 
                    "$crit $count $sugg" "$out $exip" "$in $inip" "$tcp \
                    $port" "$export" "$cat" "$vuln"
  done < file; printf "\n"
Jan 21 11:10:44 | hostname.internal.nl | THREAT | 2015/01/21 11:10:44 | critical 5 reset-both | outside 10.10.10.10 | inside 10.16.61.4 | tcp 39898 | 80 | web-browsing | any Bash Remote Code Execution Vulnerability(36731) | 

I could give you better ways in perl or awk but they will depend on how exactly you define your fields. If you update your question with more details (for example, will there always be 3 words in the critical field? We need to know what will always be the same in your input and what can change), I can give you a better solution.

Comments

[Admin]

What method did you try?

[Admin]

What do you mean by bash? Yes, it is possible, but it is a very bad idea. Tools like perl, awk or sed are much better for this sort of thing. What do you have against them? Also, how are fields defined? Is critical 5 reset-bot one field or three and, if one, how would bash be able to tell?

[Admin]

You have logged into a different account. Try merging in your old account.