Need help with a script to rearrange columns

Shell Programming and Scripting
1

I have a file that is semi-colon delimited and the column headers are always the same but the column number is totally random each time this file is generated. I don't have the skills to make a script for this so maybe someone can help.

I would like to be able to take this file which has over 80 columns and grab about 5-10 columns and rearrange them and put that into another file so I can run my script to get the information I need.

Just for a test here are some columns I would like out of this test file below:

Current Order:
num
date
time
org
product
rule

Would like to rearrange to this order:

rule
product
org
time
date
num


num;date;time;orig;type;action;alert;i/f_name;i/f_dir;product;log_sys_message;rule
0;21-Nov-07;23:59:00;2.2.2.2;control; ;;daemon;inbound;VPN-1 & FireWall-1;Log file has been switched to: 2007-11-21_235900.log;
1;22-Nov-07;0:53:28;3.3.3.3;log;drop;;Lan1;inbound;VPN-1 & FireWall-1;;16
2;22-Nov-07;0:53:29;3.3.3.3;log;accept;;Lan1;inbound;VPN-1 & FireWall-1;;3
3;22-Nov-07;0:53:29;3.3.3.3;log;accept;;Lan1;inbound;VPN-1 & FireWall-1;;3
4;22-Nov-07;0:53:30;3.3.3.3;log;accept;;Lan1;inbound;VPN-1 & FireWall-1;;15
5;22-Nov-07;0:53:30;3.3.3.3;log;drop;;Lan1;inbound;VPN-1 & FireWall-1;;16
6;21-Nov-07;23:20:39;4.4.4.4;log;accept;;eth3c0;inbound;VPN-1 & FireWall-1;;8
7;21-Nov-07;23:20:40;4.4.4.4;log;accept;;eth3c0;inbound;VPN-1 & FireWall-1;;8
8;21-Nov-07;23:20:41;4.4.4.4;log;accept;;eth3c0;inbound;VPN-1 & FireWall-1;;8
2

hi

code:

nawk 'BEGIN{
FS=";"
format="%s;%s;%s;%s;%s;%s\n"
}
{
printf(format,$12,$10,$4,$3,$2,$1)
}' filename
3

Sherry thanks for that!! But that is print columns based on their position in the file correct? The problem I have is that the column header stays the same but the number of the column in the file changes for example:

date may be column $2 today and $5 tomorrow and $7 the day after. It is totally random. So how could I print data by specifying which header name I want to print?

4

# 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

n3.awk:

BEGIN {
  FS=OFS=";"
  DEFcols="rule product orig time date num"

  if (cols == "")
    cols=DEFcols

  colsN=split(cols, colsA, " ")
}

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

Thanks for all the help but I have a gentoo system that does not have nawk on it. Is there a way to install that on gentoo, I have not been able to find it yet. Or is there a way to run this script with awk or some other utility like perl that may be on my system?

6

sorry for that post above I got this working with awk which is sym linked to gawk.

Thanks for this script its awesome!! Is there a way for it to print the header at the top also?

7

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:

My header can be from 70-100 columns(separated by semi-colons):

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:


Out of all of these the ones I am looking for are (notice that one name has spaces in it):

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

The column names always stay the same but their order changes. I can't seem to get the code above to work with this many columns. Would be nice to print the header in the outpout file also

8

Hi.

A few comments:

1) I tried the script from vgersh99 with:

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

# 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

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==colsN) ? ORS : OFS)
}
10

Hi, vgersh99.

I'd guess that you now want to split on ";" -- or perhaps I'm missing something else ... cheers, drl

11

ooops - missed that one - fixed the post.
Good catch, drl - thx!

12

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:

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==colsN) ? ORS : OFS)
}

Here is sample.txt

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;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;