#!/usr/bin/perl
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Utility;
`/usr/bin/cvs -d /var/rancid/CVS co firewalls/configs/`;
my @files=<firewalls/configs/*-pix>;

# grab /etc/services into a hash
my %services;
open (SERVICES,"</home/ryan/services") || die "$!\n";
while (<SERVICES>){
  if ($_=~/^(\S+)\s+(\d+\/\w+)\s+/){
    my $number=$2;
    my $servicename=$1;
    # slashes in hash keys are a pain
    $number=~s/\//-/g;
    $services{$number}=$servicename;
  }
}
close SERVICES;

# Create the workbook and formatting globally
my $workbook = Spreadsheet::WriteExcel->new("firewalls.xls");
my $th = $workbook->add_format();
my $td = $workbook->add_format();
my $in = $workbook->add_format();
# header cells
$th->set_bold();
$th->set_color('blue');
$th->set_bg_color('silver');
$th->set_size('13');
$th->set_italic();
$th->set_border();
# normal cells
$td->set_color('black');
$td->set_size('9');
$td->set_bg_color('42');
$td->set_border();
$td->set_text_wrap();
$td->set_align('left');
$td->set_align('top');
# inactive cells
$in->set_color('silver');
$in->set_size('9');
$in->set_bg_color('grey');
$in->set_border();
$in->set_text_wrap();
$in->set_align('left');
$in->set_align('top');
my $worksheet;
foreach my $file (@files){
  open (CONFIG,"<$file") || die "$!\n";
  my (%name,%objects,%acl);
  my $row=1;
  read (CONFIG, my $config, -s CONFIG);
  my @objects=split(/object-group\s/,$config);
  
  # This creates the excel worksheet, and formatting
  my @splitfile = split(/\//,$file); 
  my $sheetname = pop(@splitfile);
  $worksheet = $workbook->add_worksheet("$sheetname");
  # write the header row
  $worksheet->freeze_panes(1, 0);
  $worksheet->write(0,0,"DMZ",$th);
  $worksheet->write(0,1,"Action",$th);
  $worksheet->write(0,2,"Source",$th);
  $worksheet->write(0,3,"Destination",$th);
  $worksheet->write(0,4,"TCP/UDP/IP",$th);
  $worksheet->write(0,5,"Ports",$th);
  $worksheet->write(0,6,"Comments",$th);
  # fix the width
  $worksheet->set_column(0, 1,  12);
  $worksheet->set_column(2, 3,  29); 
  $worksheet->set_column(4, 4,  15);
  $worksheet->set_column(5, 6,  29);
  
  # okay, lets grab the name to ip mapping
  open (CONFIG,"<$file") || die "$!\n";
  while (<CONFIG>){
    if ($_=~/^name\s(\S+)\s(\S+)/){
      $name{$2}=$1;
    }
  }
  close CONFIG;

  # look for unused names
  open (CONFIG,"<$file") || die "$!\n";
  my @config=<CONFIG>;
  open (UNUSED,">$sheetname") || die "$!\n";
  print UNUSED "***UNUSED NAMES***\n\n";
  foreach $n (keys %name){
    my @matches = grep(/\s$n/,@config);
    unless ($#matches > 0){print UNUSED "$n\n";}
  }
  close UNUSED;
  close CONFIG;
  
  foreach (@objects){
    my ($name,$proto);
    if ($_=~/^network\s(\S+)/){
      $name=$1;
    }
    elsif ($_=~/^service\s(\S+)\s(\S+)/){
      $name=$1;
      $proto=$2;
    }
    # initialize an empty anonymous array for hosts and ports
    # I'm not sure what happens if I don't do this
    $objects{$name}{hosts}=[];
    $objects{$name}{ports}=[];
    $objects{$name}{used}=0;
    my @lines=split(/\n/,$_);
    foreach (@lines){
      my $netmask;
      if ($_=~/description\s(.*)$/){
        $objects{$name}{description}=$1;
      }
      elsif ($_=~/network-object\s(\S+)\s255\.255\.255\.255$/){
        push (@{$objects{$name}{hosts}},$1);
      }
      elsif ($_=~/network-object\s(\S+)\s(\d+\.\d+\.\d+\.\d+)$/){
        my $network="$1 $2";
        push (@{$objects{$name}{hosts}},$network);
      }
      elsif ($_=~/port-object\seq\s(\w+)$/){
        push (@{$objects{$name}{ports}},$1);
        $objects{$name}{proto}=$proto;
      }
      elsif ($_=~/port-object\srange\s(\d+)\s(\d+)$/){
        push (@{$objects{$name}{ports}},"$1-$2");
        $objects{$name}{proto}=$proto;
      }
    }
  }
  close CONFIG;
  
  
  # time for the access-lists
  open (CONFIG,"<$file") || die "$!\n";
  while (<CONFIG>){
    # look for remarked lines
    next if $_ =~ /_nat/;
    next if $_ =~ /pnat_out/;
    if ($_=~/^access-list\s(\S+)\sremark\s(.*)$/){
      my $aclname = $1;
      my $remark=$2;
      my $nextline=<CONFIG>;
      if ($nextline=~/^access-list\s(\S+)\sextended\s(.*)$/){
        my $aclname = $1;
        my ($action,$proto,$source,$dest,$svc,$inac,%used) = &aclparse($2);
        &writexls($aclname,$action,$source,$dest,$proto,$svc,$remark,$row,$inac,\%objects,\%name);
        $row++;
        foreach (keys %used){$objects{$_}{used} = $objects{$_}{used} + $used{$_};}
      }
    }
    # look for non-remarked lines
    elsif ($_=~/^access-list\s(\S+)\sextended\s(.*)$/){
      my $aclname = $1;
      # do some stuff to get things out of the ACL line
      my ($action,$proto,$source,$dest,$svc,$inac,%used) = &aclparse($2);
      &writexls($aclname,$action,$source,$dest,$proto,$svc,$remark,$row,$inac,\%objects,\%name);
      $row++;
      # this loops through the used hash returned by aclparse - updating the number of times an object is used
      foreach (keys %used){$objects{$_}{used} = $objects{$_}{used} + $used{$_};}
    }
  }
  close CONFIG;
  open (UNUSED,">>$sheetname") || die "$!\n";
  print UNUSED "\n***UNUSED OBJECTS***\n";
  foreach (keys %objects){ print UNUSED "$_\n" unless $objects{$_}{used};}
  close UNUSED;
}  
  
sub aclparse {
  my $string=shift;
  my ($action,$proto,$source,$dest,$service,$inactive,%used);
  my @splitstring=split(/\s/,$string);
  # action and proto are always in the same place
  $action=shift(@splitstring);
  $proto=shift(@splitstring);
  for (my $x=0;$x<=$#splitstring;$x++){
    my $word=$splitstring[$x];
    if ($word eq "any"){
      if ($source && $dest){$service = $word;}
      elsif ($source){$dest = $word;}
      else {$source = $word;}
    }
    elsif ($word eq "host"){
      if ($source){$dest = $splitstring[$x+1];}
      else {$source = $splitstring[$x+1];}
    }
    elsif ($word eq "object-group"){
      $used{$splitstring[$x+1]}++;
      if ($source && $dest){$service = $splitstring[$x+1];}
      elsif ($source){$dest = $splitstring[$x+1];}
      else {$source = $splitstring[$x+1];}
    }
    elsif ($word =~/\d+\.\d+\.\d+\.\d+/){
      if ($splitstring[$x+1] =~/\d+\.\d+\.\d+\.\d+/){
        if ($source){$dest = "$splitstring[$x] $splitstring[$x+1]";}
        else {$source = "$splitstring[$x] $splitstring[$x+1]";}
      }
    }
    elsif ($word eq "eq"){
      $service = $splitstring[$x+1];
    }
    elsif ($word eq "inactive"){
      $inactive = 1;
    }
  }
  return ($action,$proto,$source,$dest,$service,$inactive,%used);
}

sub writexls {
  # write some stuff
  my($aclname,$action,$source,$dest,$proto,$service,$remark,$row,$inac,$obj,$nam)=@_;
  $aclname =~ s/_access_in//;
  my %objects=%{$obj};
  my %name=%{$nam};
  my $format;
  if ($inac == 1){$format = $in;}
  else {$format = $td;}
  $worksheet->write($row,0,$aclname,$format);
  $worksheet->write($row,1,$action,$format);
  my ($sourcehosts,$desthosts,$ports);
  # this "unless" loop just says to look up the IP if a source is not an object
  unless ($objects{$source}){ if ($name{$source}){$source = "$source($name{$source})";} }
  foreach (@{$objects{$source}{hosts}}){
    $sourcehosts="$sourcehosts\n   $_($name{$_})";
  }
  # this "unless" loop just says to look up the IP if a destination is not an object
  unless ($objects{$dest}){ if ($name{$dest}){$dest = "$dest($name{$dest})";} }
  foreach (@{$objects{$dest}{hosts}}){
    $desthosts="$desthosts\n   $_($name{$_})";
  }
  unless ($service =~ /\w+/){$service = "--";}
  foreach (@{$objects{$service}{ports}}){
    if ($services{"$_-$proto"}){
      my $portproto=$services{"$_-$proto"};
      $ports="$ports\n   $_($portproto)";
    }
    else {
      $ports="$ports\n   $_";
    }
  }
  $worksheet->write($row,2,"$source\n$sourcehosts",$format);
  if ($objects{$source}{description}){ $worksheet->write_comment($row,2,"$objects{$source}{description}"); }
  $worksheet->write($row,3,"$dest\n$desthosts",$format);
  if ($objects{$dest}{description}){ $worksheet->write_comment($row,3,"$objects{$dest}{description}"); }
  $worksheet->write($row,4,$proto,$format);
  $worksheet->write($row,5,"$service\n$ports",$format);
  if ($objects{$service}{description}){ $worksheet->write_comment($row,5,"$objects{$service}{description}"); }
  $worksheet->write($row,6,$remark,$format);
}