Complicated awk loop over CSV

Question

I've got multiple CSV's with a type of rulebase that I need to parse and create commands from, and I'm having multiple issues.

Before I get started, here's an example of how it's laid out, and what it looks like:

$1 = Rule number
$3 = Source
$4 = Destination
$5 = Service
$6 = Action
$7 = Track
$10 = Comments

.

Security Policy: Blahblahblah,,,,,,,,,
12,,host_A,net-B,https,drop,Log,Any,Any,comments
13,,host_A,net-B,smtp,drop,Log,Any,Any,comments
14,,host_A,net-B,http,accept,Log,Any,Any,comments 
,,net-C,,,,,,,
,,net-D,,,,,,,
15,,host_A,net-B,http,accept,Log,Any,Any,comments
,,host_B,net-C,service_X,,,,,
,,host_C,net-D,service_y,,,,,
,,host_D,,,,,,,
,,host_E,,,,,,,

Problem #1: Column 1 (Rule Number) needs to be adjusted within the loop. I need to subtract a variable from it to equal the correct number (needs to shift). For example, the first rule #12, needs to become #1 within the loop.

I use this to create the variable I need to subtract from the original for each successive line (take the first line, subtract by one):

`awk -F, 'NR==2 {print $1 -1 }'

Problem #2: I need to iterate this loop over every instance of Rule#. IE: Each rule "can" have multiple sources/destinations/services, and I need to be able to link the new objects with the correct rule.

Error checking for $1 also needs to be done, as there are some fields/rules which need to be skipped which start with 'disabled' or something similar. This seems to do the trick:

awk -F, '$1 ~ "^[0-9]*$" {print $1}

Overall, I'd like the final output to look something like the following:

(all echo'd/awk print'd etc):

if new rule # is found in $1:
create rule security_rule
create action $rule_number $action
create comment $rule_number $comment
create source $rule_number $source <--- iterate as many times as required
create destination $rule_number $destination <--- iterate as many times as required
create service $rule_number $service <--- iterate as many times as required
create track $rule_number $track

etc...

Any help/suggestions you could make would be appreciated.

Thank you,

Edit: A better example (Rule 1 = rule 12 in CSV - these are still rough print statements, I can fill in the right print values later):

if new rule # is found in $1:
create rule security_rule
create action rule 1 drop
create comment rule 1 "This is a comment"
create source rule 1 host_A
create destination rule 1 net-B
create service rule 1 https
create track rule 1 Log

The ones with multiple source/destination/service would simply add extra 'create source rule x' lines, like so:

if new rule # is found in $1:
create rule security_rule
create action rule 3 accept
create comment rule 3 "This is a comment"
create source rule 3 host_A
create source rule 3 net-C
create source rule 3 net-D
create destination rule 3 net-B
create service rule 3 http
create track rule 3 Log

Answer 1

Awk can do this, but it's a touch unwieldy. You basically collect the information in one big string and then print it out when you're done with each one. (just remember to print the last one too)

I've omitted the if new rule # is found in $1: bit... because I don't entirely understand how that is supposed to work. If you absolutely need the "track" line to show up at the end... just duplicate the lines for $3,$4 and $5 for $7.

BEGIN{
    FS=",";recNum=0;curLine=""
}

$1 ~ /^Security Policy/ {next}

$1!="" {
    print curLine,"\n"
    recNum++;
    $1=recNum;
    curLine=sprintf("create rule security_rule\ncreate action rule %d %s\n",$1,$6);
    curLine=curLine sprintf("create comment rule %d \"%s\"\n",$1,$10);
    curLine=curLine sprintf("create track rule %d %s\n",$1,$7);
}
$1=="" {
    $1=recNum;
}

$3!=""{
    curLine=curLine sprintf("create source rule %d %s\n",$1,$3);
}
$4!=""{
    curLine=curLine sprintf("create destination rule %d %s\n",$1,$4);
}
$5!=""{
    curLine=curLine sprintf("create service rule %d %s\n",$1,$5);
}
END {print curLine}

For your input above, that gives me:

create rule security_rule
create action rule 1 drop
create comment rule 1 "comments"
create track rule 1 Log
create source rule 1 host_A
create destination rule 1 net-B
create service rule 1 https


create rule security_rule
create action rule 2 drop
create comment rule 2 "comments"
create track rule 2 Log
create source rule 2 host_A
create destination rule 2 net-B
create service rule 2 smtp


create rule security_rule
create action rule 3 accept
create comment rule 3 "comments"
create track rule 3 Log
create source rule 3 host_A
create destination rule 3 net-B
create service rule 3 http
create source rule 3 net-C
create source rule 3 net-D


create rule security_rule
create action rule 4 accept
create comment rule 4 "comments"
create track rule 4 Log
create source rule 4 host_A
create destination rule 4 net-B
create service rule 4 http
create source rule 4 host_B
create destination rule 4 net-C
create service rule 4 service_X
create source rule 4 host_C
create destination rule 4 net-D
create service rule 4 service_y
create source rule 4 host_D
create source rule 4 host_E

Answer 2

i'm not totally clear about the question, but as @Charles Duffy mentioned, why not use the native bash, can you give a sample file and it's output, I tried to break down the requirement from your question, but got lost. Anyway, below is a small example, you can try to modify to suite your requirement, same can be done thru awk which will be more elegant (afraid i'm not that much in awk), i'm forcefully setting the column one to array index, retaining the 3rd's value to "hello", and if empty retain the old value.

[bash]$ cat example;echo "##################################################"; ./tmp.sh < example ;echo "##################################################"; cat tmp.sh
12,,host_A,net-B,https,drop,Log,Any,Any,comments
13,,host_A,net-B,smtp,drop,Log,Any,Any,comments
14,,host_A,net-B,http,accept,Log,Any,Any,comments
,,net-C,,,,,,,
,,net-D,,,,,,,
15,,host_A,net-B,http,accept,Log,Any,Any,comments
,,host_B,net-C,service_X,,,,,
,,host_C,net-D,service_y,,,,,
,,host_D,,,,,,,
,,host_E,,,,,,,
##################################################
0 host_A hello https drop Log Any Any comments
1 host_A hello smtp drop Log Any Any comments
2 host_A hello http accept Log Any Any comments
3 net-C hello http accept Log Any Any comments
4 net-D hello http accept Log Any Any comments
5 host_A hello http accept Log Any Any comments
6 host_B hello service_X accept Log Any Any comments
7 host_C hello service_y accept Log Any Any comments
8 host_D hello service_y accept Log Any Any comments
9 host_E hello service_y accept Log Any Any comments
##################################################
#!/bin/bash
oldarr=();
oldarr[3]="hello"
index=0
while IFS=',' read -ra newarray
#do any rule which is iteration over data
do
  for (( i = 0; i < ${#newarray[@]}; i++))
  do
    if [ "${newarray[$i]}" ]
    then
#put any exceptional case
      if [ "$i" != "3" ]
      then
      oldarr[$i]=${newarray[$i]}
      fi
    fi
  done
#put anything which is independent of iteration
  oldarr[0]=$index
  ((index++))
  echo ${oldarr[*]}
done