6

I am trying to create a CSV file from a log output

Example two lines of log file:

May 24 2013 18:13:24 ROUTER1 %%01IFNET/4/UPDOWN(l): The state of interface GigabitEthernet0/0/22 was changed to DOWN.
May 24 2013 17:59:33 ROUTER1 %%01FIB/3/REFRESH_END(l): FIB refreshing end, the refresh group map is 0!

Expected Output:

May 24 2013 18:13:24,ROUTER1,01IFNET,4,UPDOWN,The state of interface GigabitEthernet0/0/22 was changed to DOWN.
May 24 2013 17:59:33,ROUTER1,01IFNET,3,REFRESH_END,FIB refreshing end, the refresh group map is 0!

I could manage to get few portion right with this awk command:

cat test.log | awk -F'[" "%%/(l)]' '{print $1" "$2" "$3","$4","$5","$8","$9","$10","}'

Output:

May 24 2013 18:13:24,ROUTER1,01IFNET,4,UPDOWN,
May 24 2013 17:59:33,ROUTER1,01IFNET,3,REFRESH_END,

But how can I capture the multiple column description text after "(l):" like "FIB refreshing end, the refresh group map is 0!" or "The state of interface GigabitEthernet0/0/22 was changed to DOWN.". Please advise.

Rousseau
  • 85
  • 1
  • 4
  • 9
  • Possible duplicate of [AWK multiple delimiter](https://stackoverflow.com/q/12204192/608639) – jww Aug 15 '18 at 23:18

3 Answers3

2

Awk can deal with multiple delimiters:

$ awk -F'[(/% ]' '{printf "%s",$1" "$2" "$3" "$4" "$5","$8","$9","$10",";for(i=12;i<=NF;i++)printf "%s ",$i;print ""}' file
May 24 2013 18:13:24 ROUTER1,01IFNET,4,UPDOWN,The state of interface GigabitEthernet0 0 22 was changed to DOWN.
May 24 2013 17:59:33 ROUTER1,01FIB,3,REFRESH_END,FIB refreshing end, the refresh group map is 0!
Chris Seymour
  • 83,387
  • 30
  • 160
  • 202
  • sudo_O, What does -F'[(/% ]' match? – jonschipp May 28 '13 at 13:38
  • `-F` is used to set the data delimiter where the value is a regular expression `[(/% ]` which define a character class containing the characters `(`,`/`,`%` or a single space. Basically any character in the class will not be treated as data but as the a delimiter. – Chris Seymour May 28 '13 at 13:43
1

Since this is a simple subsition on a single line, I'd just use sed, e.g.:

$ cat file
May 24 2013 18:13:24 ROUTER1 %%01IFNET/4/UPDOWN(l): The state of interface GigabitEthernet0/0/22 was changed to DOWN.
May 24 2013 17:59:33 ROUTER1 %%01FIB/3/REFRESH_END(l): FIB refreshing end, the refresh group map is 0!

$ sed -r 's/(([^ ]+ +){3}[^ ]+) +([^ ]+)[ %]+([^/]+)\/([^/]+)\/([^(]+)[^ ]+ +(.*)/\1,\3,\4,\5,\6,\7/' file
May 24 2013 18:13:24,ROUTER1,01IFNET,4,UPDOWN,The state of interface GigabitEthernet0/0/22 was changed to DOWN.
May 24 2013 17:59:33,ROUTER1,01FIB,3,REFRESH_END,FIB refreshing end, the refresh group map is 0!

but here's an awk solution if you prefer:

$ awk -F' %%|[(][^)+][)]: ' -v OFS="," '{$1=substr($1,1,20) OFS substr($1,22); gsub(/\//,OFS,$2)}1' file
May 24 2013 18:13:24,ROUTER1,01IFNET,4,UPDOWN,The state of interface GigabitEthernet0/0/22 was changed to DOWN.
May 24 2013 17:59:33,ROUTER1,01FIB,3,REFRESH_END,FIB refreshing end, the refresh group map is 0!

Not that this does not strip the " Gigabit..." text from your first line of input as you don't say how to identify that - is it that you want to remove text after "interface" or starting with "Gigabit" or after some number of spaces or something else?

Ed Morton
  • 188,023
  • 17
  • 78
  • 185
  • Thanks for your reply.. actually I didn't want to strip the "Gigabit..."..unfortunately that was a typing mistake. :) – Rousseau May 28 '13 at 13:14
0

I hope removing the things after "interface" is not a typo...

dirty and quick: (there should be better way though.. )

awk -F'\\(l\\): ' -v OFS="," '{gsub(" %%|/"," ",$1);gsub(/ /,",",$1);for(i=1;i<=3;i++)sub(/,/," ",$1)}$2~/of interface /{gsub(/interface.*/,"interface",$2)}1' file

gives

May 24 2013 18:13:24,ROUTER1,01IFNET,4,UPDOWN,The state of interface
May 24 2013 17:59:33,ROUTER1,01FIB,3,REFRESH_END,FIB refreshing end, the refresh group map is 0!
Kent
  • 189,393
  • 32
  • 233
  • 301
  • And you are right.. indeed it was a typo :| :( my bad..sorry. Actually I have no intention of removing the text after "interface".. – Rousseau May 28 '13 at 13:09