URL triggered CSV export site-script specifically for Device to IP

Here is a script designed to send the Device to IP csv to an email address which can be triggered by http/curl command.

What this script will do is either:

• you set a default email address in the script under $mail_to and when you run the script it will send to the csv file to that email address
• or you can set the email address in the http call and it will send to the csv file to that email address.

The http calls will look like the following:

Default email address set under $mail_to: 

http://10.x.xx.2/api-script?password={password};function=web_Email_Device_to_IP_CSV; 

Or with an email address added(this overrides the default email address in the script):

http://10.x.xx.2/api-script?password={password};function=web_Email_Device_to_IP_CSV;email_address=example@emailAddress.com

This script can also be run under the site-scripting page as well for testing.  Just ensure that the $mail_to variable is populated with a valid email address.

**Note** When using the http call please ensure that you go to "Admin"-->"API"-->"Web api settings" and set "Site Script Functions" to on.  Also ensure that you have the user api-rw set up with a password. 

sub web_Email_Device_to_IP_CSV {
use List::Util qw(all min);

# Set this to an email address as default
my $mail_to= ''; # e.g. myEmail@example.com

# this email address is used if you set one in the HTTP call:
my $input_emailmail_address = cgi_param ('email_address') || "";

# Body of the emailed report
my @body = "Device to IP Mapping Export .csv format";

# Subject line of the emailed report
my $subject = "Device to IP Mapping Export";


my %report_cfg = (
       table => [
           { header => "Device", format => "header", class => "block-top" },
           { header => "IP Address", format => "text" },
           ],
     );

my $ip2name_ref;
my %dev2ip;
my @table;
my @table_display;
my $CSV_LOCK = "${HOME_RUN}/csv.lock";

# Checks if email address was added to http call. If added then it
# will print out what was added and set the mail_to variable.

if ($input_emailmail_address eq ""){
print "using default email address\n";
}
else{
$mail_to = $input_emailmail_address;
print "user added email address\n";
}

print "$mail_to\n";

# Checks the mail_to variable to make sure it is set to something.
if ($mail_to ne ""){

$ip2name_ref = config_load_ip2name ();

    for my $ip (keys %{ $ip2name_ref }) {
       my $dev = $ip2name_ref->{$ip};
       push @{ $dev2ip{$dev} }, $ip;
}

    for my $device (nat_sort keys %dev2ip) {
       my $url = html_link (2, $device, sprintf ("/device-editor?mode=display;device_list=%s", $device), "");

          # Seperate the ips with semicolon for csv download
          push @table, [ $url, join (";", nat_sort @{ $dev2ip{$device} }) ];


     #this is for the displayed table
     push @table_display, [ $url, join ("<br/>", nat_sort @{ $dev2ip{$device} }) ];
  }


# if you run the script under site scripting this will Print out the table on the right hand side.
# Uncomment the 2 lines if you want to see this.
# use Akips::GUI::HTMLTable;
# html_table (\%report_cfg, \@table_display);


# Start to create the csv file and send it to an email address
my $filename = "report";
my $csv_file;
my $time_str = strftime ("%Y%m%d.%H%M", localtime);
my $lock_hdl;
my @columns;
my @row;


# Naming the filename with a timestamp
$csv_file = sprintf ("%s/%s.%s.csv", $HOME_CSV, $filename, $time_str);

$lock_hdl = process_lock ($CSV_LOCK, LOCK_EX);

# Assigning the values that were gathered to variables
my ($cfg_ref, $data_ref) = @_;
    $cfg_ref = \%report_cfg;
    $data_ref = \@table;

my $limit = min (($cfg_ref->{limit} || scalar @{ $data_ref}), scalar @{ $data_ref});

# Opening the CSV file
   open (my $CSV, ">", $csv_file ) || EXIT_FATAL ("Can't open $csv_file: $!");

# build the csv column
   for my $col_ref (@{ $cfg_ref->{table} }) {
   $col_ref->{header} =~ s/\<(.*?)\>//mg;
   push (@columns, $col_ref->{header});
}

print $CSV join (",", @columns), "\n";

# build the csv row
   for (my $row_i = 0; $row_i < $limit; $row_i++) {
      my $row_ref = $data_ref->[$row_i];
      @row = ();
      for (my $i = 0; $i < scalar @{ $cfg_ref->{table} }; $i++) {
      my $table_col = $cfg_ref->{table}->[$i];

      # Strip HTML tags from CSV output
      $row_ref->[$i] =~ s/\<(.*?)\>//mg;

      # Quote any data containing comma
      $row_ref->[$i] =~ s/(.*,.*)/"$1"/m;

      push (@row, $row_ref->[$i]);
}

# Excludes empty lines from CSV output
      next if all { not defined } @row;

print $CSV join (",", @row), "\n";

}

close $CSV;

process_unlock ($lock_hdl);

# Email to mail_to address
mail ({ subject => $subject, to => $mail_to, body => \@body, attach => [$csv_file] });
print "Email with Device to IP csv $csv_file sent to $mail_to\n"
}

# End of if statement that makes sure there is an email address 
# either added or set at the beginning of the script.
else{
print "No email address provided. Please either add email address into http or set the mail_to variable in the script.
http://10.x.xx.2/api-script?password=password;function=web_Email_Device_to_IP_CSV;email_address={email address}"
}# End of else for checking for email address

}

• sub web_Email_Device_to_IP_CSV.pl4 KB