Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need help with a script to rearrange columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need help with a script to rearrange columns
# 8  
Old 12-08-2007
Hi.
Quote:
Originally Posted by n3al10
I don't think this is working the way I need it, maybe the example I gave was not enough, I thought I could have figured out the script with some help from this forum but here goes some more: ...
A few comments:

1) I tried the script from vgersh99 with:
Code:
awk -v cols="action type" ...

and it looked like it worked, displaying the columns for action and type in that order from your sample data set.

2) You didn't tell us what was wrong. You need to provide details, like the output, error messages, etc.

3) Your sample data did not appear to include a column header that had spaces in it. The awk solution looks like it depends on space as a separator for the choice of columns. Either the awk script or the data file will need to be modified to deal with that. If the script is modified, the ";" as a choice separator seems natural, given your data.

Best wishes ... cheers, drl
# 9  
Old 12-08-2007
# this will produce the 'default' columns: "rule;product;orig;time;date;num"
nawk -f n3.awk myInputFile

# this will produce the 'custom' columns: "num orig time"
nawk -v cols='num;orig;time' -f n3.awk myInputFile
Code:
BEGIN {
  FS=OFS=";"
  DEFcols="rule;product;orig;time;date;num"

  if (cols == "")
    cols=DEFcols

  colsN=split(cols, colsA, FS)
}

FNR==1 {
  for(i=1; i<=NF; i++)
     headA[$i] = i
  print cols
  next
}
{
  for(i=1; i<=colsN; i++)
    printf("%s%s", $headA[colsA[i]], (i==colsN) ? ORS : OFS)
}

Last edited by vgersh99; 12-08-2007 at 01:05 PM.. Reason: fixed the 'split' - thx to drl
# 10  
Old 12-08-2007
Hi, vgersh99.
Quote:
Originally Posted by vgersh99
Code:
 ...
  colsN=split(cols, colsA, " ") ...

I'd guess that you now want to split on ";" -- or perhaps I'm missing something else ... cheers, drl
# 11  
Old 12-08-2007
Quote:
Originally Posted by drl
Hi, vgersh99.

I'd guess that you now want to split on ";" -- or perhaps I'm missing something else ... cheers, drl
ooops - missed that one - fixed the post.
Good catch, drl - thx!
# 12  
Old 12-08-2007
Thanks again for the help. Sorry I should have posted more information, the script on this page can handle the columns and delimiters for the original example but it is not working for what I need, I thought with help from you guys I could learn something and adjust the original script but this is beyond me now.: Here is a sample of data from which I am trying to pull the columns:

Code:
date;time;origin;src;user;VPN internal source IP

Here is what my command looks like

awk -v cols='date;time;origin;src;user;VPN internal source IP' -f n3.awk sample.txt

Here is what my n3.awk looks like:

BEGIN {
  FS=OFS=";"
  DEFcols="num;date;time;orig;type;action;alert;i/f_name;i/f_dir;product;log_sys_message;rule;rule_uid;rule_name;SmartDefense profile;service_id;ICMP;src;dst;proto;ICMP Type;ICMP Code;service;s_port;xlatesrc;xlatedst;NAT_rulenum;NAT_addtnl_rulenum;xlatedport;xlatesport;message_info;scheme:;methods:;peer gateway;encryption failure:;partner;community;fw_subproduct;vpn_feature_name;msg;scan direction;attack;Attack Info;TCP packet out of state;tcp_flags;srckeyid;dstkeyid;IKE:;CookieI;CookieR;msgid;IKE notification:;Certificate DN:;IKE IDs:;user;reason:;reject_category;Session:;L2TP:;PPP:;MAC:;OM:;om_method:;assigned_IP:;machine:;VPN internal source IP;start_time;vpn_user;message;old IP;old port;new IP;new port;spi;encryption fail reason:;rpc_prog;sys_message:;elapsed;reject_reason;System Alert message;Object;Event;Parameter;Condition;Current value;H.323 message;src phone number;dst phone number;H.323 Illegal redirection;dst scheme:;dst methods:;dst peer gateway;route status:;dst partner;dst community;Internal_CA:;serial_num:;dn:;message:"

  if (cols == "")
    cols=DEFcols

  colsN=split(cols, colsA, FS)
}

FNR==1 {
  for(i=1; i<=NF; i++)
     headA[$i] = i
  print cols
  next
}
{
  for(i=1; i<=colsN; i++)
    printf("%s%s", $headA[colsA[i]], (i==colsN) ? ORS : OFS)
}


Here is sample.txt

Code:
num;date;time;orig;type;action;alert;i/f_name;i/f_dir;product;log_sys_message;rule;rule_uid;rule_name;SmartDefense profile;service_id;ICMP;src;dst;proto;ICMP Type;ICMP Code;service;s_port;xlatesrc;xlatedst;NAT_rulenum;NAT_addtnl_rulenum;xlatedport;xlatesport;message_info;scheme:;methods:;peer gateway;encryption failure:;partner;community;fw_subproduct;vpn_feature_name;msg;scan direction;attack;Attack Info;TCP packet out of state;tcp_flags;srckeyid;dstkeyid;IKE:;CookieI;CookieR;msgid;IKE notification:;Certificate DN:;IKE IDs:;user;reason:;reject_category;Session:;L2TP:;PPP:;MAC:;OM:;om_method:;assigned_IP:;machine:;VPN internal source IP;start_time;vpn_user;message;old IP;old port;new IP;new port;spi;encryption fail reason:;rpc_prog;sys_message:;elapsed;reject_reason;System Alert message;Object;Event;Parameter;Condition;Current value;H.323 message;src phone number;dst phone number;H.323 Illegal redirection;dst scheme:;dst methods:;dst peer gateway;route status:;dst partner;dst community;Internal_CA:;serial_num:;dn:;message:
737989;16Nov2007;19:31:45;2.2.2.2;log;authcrypt;;daemon;inbound;VPN-1 & FireWall-1;;;;;;;;76.108.238.86;;;;;;;;;;;;;;SSL;;;;;;VPN-1;SNX;;;;;;;;;;;;;;;;domain.com:CN=rizzo\, Frank,OU=Citrix,OU=Test.OU,DC=domain,DC=com,DC=ad;SSL Network Extender: Authenticated by username and password;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
737995;16Nov2007;19:31:45;2.2.2.2;log;authcrypt;;daemon;inbound;VPN-1 & FireWall-1;;;;;;;;76.108.238.86;;;;;;;;;;;;;;SSL;;;;;;VPN-1;SNX;;;;;;;;;;;;;;;;domain.com:CN=rizzo\, Frank,OU=Citrix,OU=Test.OU,DC=domain,DC=com,DC=ad;SSL Network Extender: Authenticated by username and password;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
738053;16Nov2007;19:31:51;2.2.2.2;log;authcrypt;;;inbound;VPN-1 & FireWall-1;;;;;;;;172.16.196.12;2.2.2.2;;;;;;;;;;;;;SSL;;;;;;VPN-1;SNX;;;;;;;;;;;;;;;;rizzoj;SSL Network Extender connected to gateway;;;;;;;;;;172.16.196.12;16Nov2007 19:31:51;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
738054;16Nov2007;19:31:51;2.2.2.2;log;authcrypt;;;inbound;VPN-1 & FireWall-1;;;;;;;;172.16.196.12;2.2.2.2;;;;;;;;;;;;;SSL;;;;;;VPN-1;SNX;;;;;;;;;;;;;;;;rizzoj;SSL Network Extender connected to gateway;;;;;;;;;;172.16.196.12;16Nov2007 19:31:51;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
738083;16Nov2007;19:31:58;2.2.2.2;log;decrypt;;eth0;inbound;VPN-1 & FireWall-1;;2;{D7ECCA2E-180F-4632-93B5-91D367FAA637};Remote User Access;No Protection;igmp;;172.16.196.12;224.0.0.22;igmp;;;;;;;;;;;;SSL;SSL;172.16.196.12;;;RemoteAccess;VPN-1;VPN;;;;;;;;;;;;;;;;rizzoj;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
738084;16Nov2007;19:31:58;2.2.2.2;log;decrypt;;eth0;inbound;VPN-1 & FireWall-1;;2;{D7ECCA2E-180F-4632-93B5-91D367FAA637};Remote User Access;No Protection;nbname;;172.16.196.12;172.16.58.5;udp;;;137;137;;;;;;;;SSL;SSL;172.16.196.12;;;RemoteAccess;VPN-1;VPN;;;;;;;;;;;;;;;;rizzoj;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
738159;16Nov2007;19:32:09;2.2.2.2;log;decrypt;;eth0;inbound;VPN-1 & FireWall-1;;2;{D7ECCA2E-180F-4632-93B5-91D367FAA637};Remote User Access;No Protection;domain-udp;;172.16.196.12;172.16.58.5;udp;;;53;1039;;;;;;;;SSL;SSL;172.16.196.12;;;RemoteAccess;VPN-1;VPN;;;;;;;;;;;;;;;;rizzoj;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
738160;16Nov2007;19:32:09;2.2.2.2;log;decrypt;;eth0;inbound;VPN-1 & FireWall-1;;2;{D7ECCA2E-180F-4632-93B5-91D367FAA637};Remote User Access;No Protection;TCP-3389;;172.16.196.12;172.16.58.22;tcp;;;3389;1231;;;;;;;;SSL;SSL;172.16.196.12;;;RemoteAccess;VPN-1;VPN;;;;;;;;;;;;;;;;rizzoj;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
738276;16Nov2007;19:32:20;2.2.2.2;log;decrypt;;eth0;inbound;VPN-1 & FireWall-1;;2;{D7ECCA2E-180F-4632-93B5-91D367FAA637};Remote User Access;No Protection;http;;172.16.196.12;69.8.203.88;tcp;;;80;1233;2.2.2.2;;13;0;;30413;;SSL;SSL;172.16.196.12;;;RemoteAccess;VPN-1;VPN;;;;;;;;;;;;;;;;rizzoj;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

Last edited by n3al10; 12-08-2007 at 08:03 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

A cleaner way to rearrange column

Hello, I have some tab delimited text data, index name chg_p chg_m 1 name,1 1 0 2 name,2 1 1 3 name,3 1 0 4 name,4 1 0 5 name,5 1 1 I need to duplicate the "index" column, call it "id" and insert it after the... (8 Replies)
Discussion started by: LMHmedchem
8 Replies

2. UNIX for Dummies Questions & Answers

Transpose matrix, and rearrange columns common with another file

This is my first post, I apologize if I have broken rules. Some assistance with the following will be very helpful. I have a couple of files, both should ultimately have common columns only, arranged in the same order. This file needs to be transposed, to bring the rows to columns ... (2 Replies)
Discussion started by: abh.kumar
2 Replies

3. Shell Programming and Scripting

Rearrange Lines with awk

I need to rearrange the lines in the input file in the example below: Input: LG1 R500 A-170 F1:81 F1:22 F2:32 F1:71 LG1 R700 A-203 F2:17 E2:18 LG1 R700 B-224 E1:9 LG2 R500 C-235 E2:9 F2:17 Output: LG1 R500 A-170 F1:81 LG1 R500 A-170 F1:22 LG1 R500 A-170 F2:32 LG1 R500 A-170... (2 Replies)
Discussion started by: aydj
2 Replies

4. Shell Programming and Scripting

Using awk to rearrange fields

Hi, I am required to arrange columns of a file i.e make the 15th column into the 1st column. I am doing awk 'begin {fs=ofs=","} {print $15,$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14}' ad.data>ad.csv the problem is that column 15 gets to column 1 but it is not comma separated with the... (10 Replies)
Discussion started by: seddoubt
10 Replies

5. Shell Programming and Scripting

how to rearrange a matrix with awk

Hi, every one. I have two files ,one is in matrix like this, one is a list with the same data as the matrix. AB AE AC AD AA AF SA 3 4 5 6 4 6 SC 5 7 2 8 4 3 SD 4 6 5 3 8 3 SE 45 ... (5 Replies)
Discussion started by: xshang
5 Replies

6. Shell Programming and Scripting

script to rearrange data.

Hello. I have data in the following format (the spaces at the beginning of lines are included): 1 2 2 0.39621 0.00000 1 2 2 0.00000+-0.0000 * 1 2 ... (5 Replies)
Discussion started by: andersgs
5 Replies

7. UNIX for Dummies Questions & Answers

Rearrange columns and rows with awk

Hello, I have the following problem I have two columns with numbers arranged as follows: x1 y1 x2 y2 .... .... x250 y250 Now I need them arranged as follows: "string a" x1 y1 x1 y2 "string b" "string a" x1 y2 x2 y2 (3 Replies)
Discussion started by: Tom46
3 Replies

8. Shell Programming and Scripting

rearrange a file

hi! in awk, i have a file like this: Trace1: WRIT,Trace2: BLAN,Trace3: BLAN, -47.2120018005371,,,39815.4809027778 -46.3009986877441,,,39815.4809027778 -46.277000427246,,,39815.4809143519 -46.7389984130859,,,39815.4809259259 -46.3460006713867,,,39815.4809259259... (10 Replies)
Discussion started by: riderman
10 Replies

9. UNIX for Dummies Questions & Answers

Help rearrange the content of the file.

Hi all, I need to rearrange the content of the file. I need to move line 1 to 4, 2 to 3, 3 to 2, and 4 to 1. IPlease help. I was thinking about using head and tail command. Here is the original file. aa bb cc dd Here is what I need it to be. dd cc bb aa Thanks. (6 Replies)
Discussion started by: sirrtuan
6 Replies

10. Shell Programming and Scripting

Need help in AWK;Search String and rearrange columns

Hi AWK Experts, file1.txt contains: 29b11b820ddcc:-|OHad.perWrk|spn_id=AH111|spn_ordtyp=MY_REQ|msg_typ=ah.ntf.out|spn_ordid=928176|spn_nid=3|msg_strt=1175615334703|msg_que=oput|diff=371|17:48:55,074|17:48:55,084|10 file2.txt contains:... (2 Replies)
Discussion started by: spring_buck
2 Replies
Login or Register to Ask a Question