Nawk Format

ther2000 · July 8, 2011, 10:42am

Hi! I have a file which I want to search daily for any line that contains the work 'Reason' and I want to take that line and put the data in a certain format using awk or nawk....I do not have gawk on my machine so it would have to be awk or nawk, or sed would work as well. Here are some examples of the input lines:

 
-07/07@04:03:34 - 10.7.23.144 : DIAG 3x3b: Reason: code 0x60, Sent by kernel-
-07/07@04:04:14 - 10.37.42.194 : DIAG 3x3b: Reason: code 0x60, Sent by kernel-
-07/07@06:50:04 - 10.7.14.854 : DIAG 0x72: Reason: FATAL MEMORY ERROR - guard band corrupt (addr:0x70e6190 size:32 owner:75(TblMgr Main)) at _mem_DeletePointer line 1737-
-07/07@08:35:28 - 10.7.62.820 : DIAG 0x72: Reason: OCP initiated reset...Rebooting from Delay Dial Option-
-07/07@10:33:14 - 10.7.23.167 : DIAG 0x72: Reason: PASSTHRU RESET MESSAGE-
-07/07@10:34:28 - 10.7.42.621 : DIAG 0x72: Reason: OCP initiated reset...Rebooting from Delay Dial Option-
-07/07@11:19:33 - 10.5.26.704 : DIAG 3x3b: Reason: code 0x11, Unmapped address, fault address: 0x00000925-
-07/07@11:19:33 - 10.7.26.704 : DIAG 3x3b: Reason: code 0x11, Unmapped address, fault address: 0x00000925-
-07/07@11:19:45 - 10.7.24.277 : DIAG 0x72: Reason: OCP initiated reset...Rebooting from Delay Dial Option-
-07/07@15:43:47 - 10.7.25.474 : DIAG 0x72: Reason: PASSTHRU RESET MESSAGE-
-07/07@15:56:38 - 10.7.65.77 : DIAG 0x72: Reason: Error! line 548 of dacct_ExitLoadClImage() in dacctrail/dacct.c-
-07/07@17:13:40 - 10.7.23.342 : DIAG 0x72: Reason: OCP initiated reset...IMAGE upgrade complete!-
-07/07@17:47:57 - 10.5.44.351 : DIAG 0x72: Reason: ERROR: dk_Reset called.-

The output I would like is to eliminate any lines that contain "OCP" or "PASSTHRU RESET" and put everything in the following format:

Date/Time,Location,Shelf,IP,Reason,Log Filename

07/07@04:03:34,-,-,10.7.23.144,DIAG 3x3b: Reason: code 0x60, Sent by kernel,$DATE/10.7.23.144
07/07@04:04:14,-,-,10.37.42.194,DIAG 3x3b: Reason: code 0x60, Sent by kernel,$DATE/10.37.42.194
07/07@06:50:04,-,-,10.7.14.854,DIAG 0x72: Reason: FATAL MEMORY ERROR - guard band corrupt (addr:0x70e6190 size:32 owner:75(TblMgr Main)) at _mem_DeletePointer line 1737,$DATE/10.7.14.854
07/07@11:19:33,-,-,10.5.26.704,DIAG 3x3b: Reason: code 0x11- Unmapped address- fault address: 0x00000925,$DATE/10.5.26.704
07/07@11:19:33,-,-,10.7.26.704,DIAG 3x3b: Reason: code 0x11, Unmapped address, fault address: 0x00000925,$DATE/10.7.65.77
07/07@15:56:38,-,-,10.7.65.77,DIAG 0x72: Reason: Error! line 548 of dacct_ExitLoadClImage() in dacctrail/dacct.c,$DATE/10.7.65.77
07/07@17:47:57,-,-,10.5.44.351,DIAG 0x72: Reason: ERROR: dk_Reset called,$DATE/10.5.44.351

The problem I am having is I want to be able to put this data into an Excel file and so I need to remove any commas that are in the 'Reason' column or else it puts them into seperate cells. Aslo, at the end where it says $DATE I want it to display a date variable that I have and then the IP address.

ctsgnb · July 8, 2011, 11:08am

sed '/OCP/d;/PASSTHRU RESET/d;/Reason/!d;s/ \- /,-,-,/;s/ : /,/;s;\([\.0-9]*\),DIAG[^-]*;&,$DATE/\1;;s/\.,/,/' input

I don't know why a trailing hyphen appear ... if so just remove it

sed '/OCP/d;/PASSTHRU RESET/d;/Reason/!d;s/ \- /,-,-,/;s/ : /,/;s;\([0-9\.]*\),DIAG[^-]*;&,$DATE/\1;;s/\.,/,/;s/-$//' input

ther2000 · July 8, 2011, 11:17am

This is the output I am getting with that sed command:

07/07@04:03:34 ,-,-, 10.7.2.194 ,DIAG 3x3b: Reason: code 0x60
07/07@04:03:34 ,-,-, 10.7.2.194 ,DIAG 3x3b: Reason: code 0x60
07/07@04:04:14 ,-,-, 10.7.2.194 ,DIAG 3x3b: Reason: code 0x60
07/07@04:04:14 ,-,-, 10.7.2.194 ,DIAG 3x3b: Reason: code 0x60
07/07@06:50:04 ,-,-, 10.7.4.84 
07/07@06:50:04 ,-,-, 10.7.4.84 
07/07@11:19:33 ,-,-, 10.7.2.204 ,DIAG 3x3b: Reason: code 0x11, Unmapped address
07/07@11:19:33 ,-,-, 10.7.2.204 ,DIAG 3x3b: Reason: code 0x11, Unmapped address
07/07@11:20:10 ,-,-, 10.7.2.204 ,DIAG 3x3b: Reason: code 0x1, Unmapped address
07/07@11:20:10 ,-,-, 10.7.2.204 ,DIAG 3x3b: Reason: code 0x1, Unmapped address
07/07@15:56:38 ,-,-, 10.7.2.97 
07/07@15:56:38 ,-,-, 10.7.2.97 
07/07@15:57:09 ,-,-, 10.7.2.97 
07/07@15:57:09 ,-,-, 10.7.2.97 
07/07@17:47:57 ,-,-, 10.7.4.51 
07/07@17:47:57 ,-,-, 10.7.4.51

I'd also like to eliminate duplicates if possible.

ctsgnb · July 8, 2011, 11:50am

I updated my previous post.

# cat tst
-07/07@04:03:34 - 10.7.23.144 : DIAG 3x3b: Reason: code 0x60, Sent by kernel-
-07/07@04:04:14 - 10.37.42.194 : DIAG 3x3b: Reason: code 0x60, Sent by kernel-
-07/07@06:50:04 - 10.7.14.854 : DIAG 0x72: Reason: FATAL MEMORY ERROR - guard band corrupt (addr:0x70e6190 size:32 owner:75(TblMgr Main)) at _mem_DeletePointer line 1737-
-07/07@08:35:28 - 10.7.62.820 : DIAG 0x72: Reason: OCP initiated reset...Rebooting from Delay Dial Option-
-07/07@10:33:14 - 10.7.23.167 : DIAG 0x72: Reason: PASSTHRU RESET MESSAGE-
-07/07@10:34:28 - 10.7.42.621 : DIAG 0x72: Reason: OCP initiated reset...Rebooting from Delay Dial Option-
-07/07@11:19:33 - 10.5.26.704 : DIAG 3x3b: Reason: code 0x11, Unmapped address, fault address: 0x00000925-
-07/07@11:19:33 - 10.7.26.704 : DIAG 3x3b: Reason: code 0x11, Unmapped address, fault address: 0x00000925-
-07/07@11:19:45 - 10.7.24.277 : DIAG 0x72: Reason: OCP initiated reset...Rebooting from Delay Dial Option-
-07/07@15:43:47 - 10.7.25.474 : DIAG 0x72: Reason: PASSTHRU RESET MESSAGE-
-07/07@15:56:38 - 10.7.65.77 : DIAG 0x72: Reason: Error! line 548 of dacct_ExitLoadClImage() in dacctrail/dacct.c-
-07/07@17:13:40 - 10.7.23.342 : DIAG 0x72: Reason: OCP initiated reset...IMAGE upgrade complete!-
-07/07@17:47:57 - 10.5.44.351 : DIAG 0x72: Reason: ERROR: dk_Reset called.-
# sed '/OCP/d;/PASSTHRU RESET/d;/Reason/!d;s/ \- /,-,-,/;s/ : /,/;s/\([0-9\.]*\),DIAG[^-]*/&,$DATE,\1/;s/\.,/,/;s/-$//' tst
-07/07@04:03:34,-,-,10.7.23.144,DIAG 3x3b: Reason: code 0x60, Sent by kernel,$DATE,10.7.23.144
-07/07@04:04:14,-,-,10.37.42.194,DIAG 3x3b: Reason: code 0x60, Sent by kernel,$DATE,10.37.42.194
-07/07@06:50:04,-,-,10.7.14.854,DIAG 0x72: Reason: FATAL MEMORY ERROR ,$DATE,10.7.14.854- guard band corrupt (addr:0x70e6190 size:32 owner:75(TblMgr Main)) at _mem_DeletePointer line 1737
-07/07@11:19:33,-,-,10.5.26.704,DIAG 3x3b: Reason: code 0x11, Unmapped address, fault address: 0x00000925,$DATE,10.5.26.704
-07/07@11:19:33,-,-,10.7.26.704,DIAG 3x3b: Reason: code 0x11, Unmapped address, fault address: 0x00000925,$DATE,10.7.26.704
-07/07@15:56:38,-,-,10.7.65.77,DIAG 0x72: Reason: Error! line 548 of dacct_ExitLoadClImage() in dacctrail/dacct.c,$DATE,10.7.65.77
-07/07@17:47:57,-,-,10.5.44.351,DIAG 0x72: Reason: ERROR: dk_Reset called,$DATE,10.5.44.351
# sed '/OCP/d;/PASSTHRU RESET/d;/Reason/!d;s;^\([^ ]*\) - \([^ ]*\) : \(.*\)-$;\1,-,-,\2,\3,$DATE/\2;;s/\.,/,/' tst
-07/07@04:03:34,-,-,10.7.23.144,DIAG 3x3b: Reason: code 0x60, Sent by kernel,$DATE/10.7.23.144
-07/07@04:04:14,-,-,10.37.42.194,DIAG 3x3b: Reason: code 0x60, Sent by kernel,$DATE/10.37.42.194
-07/07@06:50:04,-,-,10.7.14.854,DIAG 0x72: Reason: FATAL MEMORY ERROR - guard band corrupt (addr:0x70e6190 size:32 owner:75(TblMgr Main)) at _mem_DeletePointer line 1737,$DATE/10.7.14.854
-07/07@11:19:33,-,-,10.5.26.704,DIAG 3x3b: Reason: code 0x11, Unmapped address, fault address: 0x00000925,$DATE/10.5.26.704
-07/07@11:19:33,-,-,10.7.26.704,DIAG 3x3b: Reason: code 0x11, Unmapped address, fault address: 0x00000925,$DATE/10.7.26.704
-07/07@15:56:38,-,-,10.7.65.77,DIAG 0x72: Reason: Error! line 548 of dacct_ExitLoadClImage() in dacctrail/dacct.c,$DATE/10.7.65.77
-07/07@17:47:57,-,-,10.5.44.351,DIAG 0x72: Reason: ERROR: dk_Reset called,$DATE/10.5.44.351

ther2000 · July 8, 2011, 3:06pm

That one is giving me this:

 
07/07@04:03:34 - 10.7.2.194 ,DIAG 3x3b: Reason: code 0x60, Sent by kernel,07-08-2011,
07/07@04:03:34 - 10.7.2.194 ,DIAG 3x3b: Reason: code 0x60, Sent by kernel,07-08-2011,
07/07@04:04:14 - 10.7.2.194 ,DIAG 3x3b: Reason: code 0x60, Sent by kernel,07-08-2011,
07/07@06:50:04 - 10.7.4.84 ,DIAG 0x62: Reason: FATAL MEMORY ERROR,,07-08-2011,-,-,guard band corrupt (addr:0x90e4190 size:32 owner:75(TblMgr Main)) at _mem_DeletePointer line 1737
07/07@06:50:04 - 10.7.4.84 ,DIAG 0x62: Reason: FATAL MEMORY ERROR,,07-08-2011,-,-,guard band corrupt (addr:0x90e4190 size:32 owner:75(TblMgr Main)) at _mem_DeletePointer line 1737
07/07@11:19:33 - 10.7.2.204 ,DIAG 3x3b: Reason: code 0x1, Unmapped address, fault address: 0x00000514,07-08-2011,
07/07@11:19:33 - 10.7.2.204 ,DIAG 3x3b: Reason: code 0x1, Unmapped address, fault address: 0x00000514,07-08-2011,
07/07@11:20:10 - 10.7.2.204 ,DIAG 3x3b: Reason: code 0x1, Unmapped address, fault address: 0x00000514,07-08-2011,
07/07@11:20:10 - 10.7.2.204 ,DIAG 3x3b: Reason: code 0x1, Unmapped address, fault address: 0x00000514,07-08-2011,
07/07@15:56:38 - 10.7.2.97 ,DIAG 0x62: Reason: Error! line 678 of docact_ExitLoadCmImage() in docctrl/docact.c,07-08-2011,
07/07@15:56:38 - 10.7.2.97 ,DIAG 0x62: Reason: Error! line 678 of docact_ExitLoadCmImage() in docctrl/docact.c,07-08-2011,
07/07@15:57:09 - 10.7.2.97 ,DIAG 0x62: Reason: Error! line 678 of docact_ExitLoadCmImage() in docctrl/docact.c,07-08-2011,
07/07@15:57:09 - 10.7.2.97 ,DIAG 0x62: Reason: Error! line 678 of docact_ExitLoadCmImage() in docctrl/docact.c,07-08-2011,
07/07@17:47:57 - 10.7.4.51 ,DIAG 0x62: Reason: ERROR: pk_Reset called.,07-08-2011,
07/07@17:47:57 - 10.7.4.51 ,DIAG 0x62: Reason: ERROR: pk_Reset called.,07-08-2011,

Its very close, but I would like it to be like this:

07/07@04:03:34,-,-,10.7.24.164 ,DIAG 3x3b: Reason: code 0x60, Sent by kernel,07-08-2011,10.7.2.194
07/07@04:03:34,-,-,10.7.24.164 ,DIAG 3x3b: Reason: code 0x60, Sent by kernel,07-08-2011,10.7.2.194
07/07@04:04:14,-,-,10.7.24.164 ,DIAG 3x3b: Reason: code 0x60, Sent by kernel,07-08-2011,10.7.24.164
etc....

and if possible I'd like to remove the commas from the reason so they can stay in the same cell when I drag this into an excel file....Thanks so much for all your help.

---------- Post updated at 03:06 PM ---------- Previous update was at 12:04 PM ----------

Thanks...that is almost perfect, but that last IP address isn't showing up. Also, is there a way to get rid of the commas...like the ones I underlined below?

 
07/07@11:18:39,-,-,10.7.2.113 ,DIAG 0x92: Reason: OCAP initiated reset...Rebooting from tru2way Diag Option,07-08-2011,
07/07@11:18:39,-,-,10.7.2.113 ,DIAG 0x92: Reason: OCAP initiated reset...Rebooting from tru2way Diag Option,07-08-2011,
07/07@11:19:08,-,-,10.7.2.227 ,DIAG 0x92: Reason: OCAP initiated reset...Rebooting from tru2way Diag Option,07-08-2011,
07/07@11:19:08,-,-,10.7.2.227 ,DIAG 0x92: Reason: OCAP initiated reset...Rebooting from tru2way Diag Option,07-08-2011,
07/07@11:19:33,-,-,10.7.2.204 ,DIAG 0x0b: Reason: code 0x1, Unmapped address, fault address: 0x00000514,07-08-2011,
07/07@11:19:33,-,-,10.7.2.204 ,DIAG 0x0b: Reason: code 0x1, Unmapped address, fault address: 0x00000514,07-08-2011,
07/07@11:19:45,-,-,10.7.2.227 ,DIAG 0x92: Reason: OCAP initiated reset...Rebooting from tru2way Diag Option,07-08-2011,
07/07@11:19:45,-,-,10.7.2.227 ,DIAG 0x92: Reason: OCAP initiated reset...Rebooting from tru2way Diag Option,07-08-2011,
07/07@11:20:10,-,-,10.7.2.204 ,DIAG 0x0b: Reason: code 0x1, Unmapped address, fault address: 0x00000514,07-08-2011,
07/07@11:20:10,-,-,10.7.2.204 ,DIAG 0x0b: Reason: code 0x1, Unmapped address, fault address: 0x00000514,07-08-2011,
07/07@11:57:00,-,-,10.7.2.227 ,DIAG 0x92: Reason: OCAP initiated reset...Rebooting from tru2way Diag Option,07-08-2011,
07/07@11:57:00,-,-,10.7.2.227 ,DIAG 0x92: Reason: OCAP initiated reset...Rebooting from tru2way Diag Option,07-08-2011,
07/07@11:57:39,-,-,10.7.2.227 ,DIAG 0x92: Reason: OCAP initiated reset...Rebooting from tru2way Diag Option,07-08-2011,
07/07@11:57:39,-,-,10.7.2.227 ,DIAG 0x92: Reason: OCAP initiated reset...Rebooting from tru2way Diag Option,07-08-2

ctsgnb · July 8, 2011, 3:31pm

Please copy paste also what you try, not only the result.

I initially post
s/ \- /,-,-,/2 which i then fixed removing the "2" s/ \- /,-,-,/

ther2000 · July 8, 2011, 3:49pm

This is the one I tried and I got the above results:

 
sed '/OCP/d;/PASSTHRU RESET/d;/Reason/!d;s/ \- /,-,-,/;s/ : /,/;s/\([0-9\.]*\),DIAG[^-]*/&,$DATE,\1/;s/\.,/,/;s/-$//' tst

ctsgnb · July 8, 2011, 4:42pm

you can get ride off the other comas by adding "s/, / /g" to the sed statement :

example :

$ echo "07/07@11:19:33,-,-,10.7.2.204 ,DIAG 0x0b: Reason: code 0x1, Unmapped address, fault address: 0x00000514,07-08-2011," | sed 's/, / /g'
07/07@11:19:33,-,-,10.7.2.204 ,DIAG 0x0b: Reason: code 0x1 Unmapped address fault address: 0x00000514,07-08-2011,

---------- Post updated at 09:58 PM ---------- Previous update was at 09:51 PM ----------

I am afraid you did not exactly tryied the code as is, maybe you did some misstyping.

Or you forgot some space in the input example you gave earlier :
indeed, the " s/ : /,/ " statement would have removed the space that is before the " ,DIAG " occurrence.

So i think that you should retry the code by retyping it taking care of not forgetting any comas, bracket or space.

---------- Post updated at 10:42 PM ---------- Previous update was at 09:58 PM ----------

Try this code :

sed '/OCP/d;/PASSTHRU RESET/d;/Reason/!d;s/-//g;s/  /,-,-,/;s/  / - /;s/ : /,/;s!\([0-9\.]*\),DIAG.*!&,$DATE/\1!;s/, / /g;s/\.,/,/' yourinputfile

ther2000 · July 8, 2011, 4:43pm

Thank you so much that works great!

ctsgnb · July 8, 2011, 4:53pm

Also consider :

$ sed '/OCP/d;/PASSTHRU RESET/d;/Reason/!d;s/-//g;s/  /,-,-,/;s/  / - /;s/ : /,/;s!\([0-9\.]*\),DIAG.*!&,'$(date +%d-%m-%Y)'/\1!;s/, / /g;s/\.,/,/' yourinputfile
07/07@04:03:3,-,-,10.7.23.144,DIAG 3x3b: Reason: code 0x60 Sent by kernel,08-07-2011/10.7.23.144
07/07@04:04:1,-,-,10.37.42.194,DIAG 3x3b: Reason: code 0x60 Sent by kernel,08-07-2011/10.37.42.194
07/07@06:50:0,-,-,10.7.14.854,DIAG 0x72: Reason: FATAL MEMORY ERROR - guard band corrupt (addr:0x70e6190 size:32 owner:75(TblMgr Main)) at _mem_DeletePointer line 1737,08-07-2011/10.7.14.854
07/07@11:19:3,-,-,10.5.26.704,DIAG 3x3b: Reason: code 0x11 Unmapped address fault address: 0x00000925,08-07-2011/10.5.26.704
07/07@11:19:3,-,-,10.7.26.704,DIAG 3x3b: Reason: code 0x11 Unmapped address fault address: 0x00000925,08-07-2011/10.7.26.704
07/07@15:56:3,-,-,10.7.65.77,DIAG 0x72: Reason: Error! line 548 of dacct_ExitLoadClImage() in dacctrail/dacct.c,08-07-2011/10.7.65.77
07/07@17:47:5,-,-,10.5.44.351,DIAG 0x72: Reason: ERROR: dk_Reset called.,08-07-2011/10.5.44.351