From: Jeremy Davis Date: Mon, 6 Apr 2015 11:58:00 +0000 (-0400) Subject: freeside-aradial-sftp_and_import X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=b99d624c7414144cb60eabfc8e462fab39c75c0d freeside-aradial-sftp_and_import --- diff --git a/FS/bin/freeside-aradial-sftp_and_import b/FS/bin/freeside-aradial-sftp_and_import new file mode 100644 index 000000000..668ec49ba --- /dev/null +++ b/FS/bin/freeside-aradial-sftp_and_import @@ -0,0 +1,327 @@ +#!/usr/bin/perl -w + +#i'm kinda like freeside-cdr-sftp_and_import... some parts should be libraried + +use strict; +use Getopt::Std; +use Date::Parse; +use Date::Format; +use Text::CSV_XS; +use DBI qw( :sql_types ); +use Net::SFTP::Foreign; +#use FS::UID qw( adminsuidsetup datasrc ); + +#adjusted these for what we're actually seeing in the real log files +our %aradial2db = ( + #'Date' => '', + #'NASIP' => 'NASIPAddress', + 'NASID' => 'NASIPAddress', + 'AcctSessionId' => 'AcctSessionId', + 'Port' => 'NasPortId', + #'Status-Type' => 'Acct-Status-Type', + #'UserID' => 'UserName', + 'User ID' => 'UserName', + 'Authentic' => 'AcctAuthentic', + 'Service-Type' => 'ServiceType', + 'FramedProtocol' => 'FramedProtocol', + #'FramedCompression' => '', #not handled, needed? unlikely + 'FramedAddress' => 'FramedIPAddress', + 'Acct-Delay-Time' => 'AcctStartDelay', #? + 'Session-Time' => 'AcctSessionTime', + #'Input-Gigawords' => '', #XXX handle lots of data + 'Input-Octets' => 'AcctInputOctets', + #'Output-Gigawords' => '', #XXX handle lots of data + 'Output-Octets' => 'AcctOutputOctets', + 'NAS-Port-Type' => 'NASPortType', + 'Acct-Terminate-Cause' => 'AcctTerminateCause', +); + +our %bind_type = ( + 'AcctInputOctets' => SQL_INTEGER, + 'AcctOutputOctets' => SQL_INTEGER, + 'AcctSessionTime' => SQL_INTEGER, + 'AcctStartDelay' => SQL_INTEGER, + 'AcctStopDelay' => SQL_INTEGER, +); + +#http://www.iana.org/assignments/radius-types/radius-types.xhtml#radius-types-10 +our %status_type = ( + 1 => 'Start', + 2 => 'Stop', + 3 => 'Interim-Update', + #4-6,'Unassigned', + 7 => 'Accounting-On', + 8 => 'Accounting-Off', + 9 => 'Tunnel-Start', + 10 => 'Tunnel-Stop', + 11 => 'Tunnel-Reject', + 12 => 'Tunnel-Link-Start', + 13 => 'Tunnel-Link-Stop', + 14 => 'Tunnel-Link-Reject', + 15 => 'Failed', +); + +### +# parse command line +### + +use vars qw( $opt_m $opt_a $opt_b $opt_r $opt_d $opt_v $opt_P ); +getopts('m:abr:d:P:v:'); + +my %options = (); + +my $user = shift or die &usage; +#adminsuidsetup $user; + +# %%%FREESIDE_CACHE%%% & hardcoded datasrc +#my $cachedir = '%%%FREESIDE_CACHE%%%/cache.'. datasrc. '/cdrs'; +my $cachedir = '/usr/local/etc/freeside/cache.DBI:Pg:dbname=freeside/cdrs'; +mkdir $cachedir unless -d $cachedir; + +my $servername = shift or die &usage; + +my( $datasrc, $db_user, $db_pass ) = ( shift, shift, shift ); +my $dbh = DBI->connect( $datasrc, $db_user, $db_pass) + or die "can't connect: $DBI::errstr\n"; + +my $csv = Text::CSV_XS->new; + +### +# get the file list +### + +warn "Retrieving directory listing\n" if $opt_v; + +$opt_m = 'sftp' if !defined($opt_m); +$opt_m = lc($opt_m); + +my $ls; + +if($opt_m eq 'ftp') { + $options{'Port'} = $opt_P if $opt_P; + $options{'Debug'} = $opt_v if $opt_v; + $options{'Passive'} = $opt_a if $opt_a; + + my $ls_ftp = ftp(); + + $ls = [ grep { /^.*$/i } $ls_ftp->ls ]; +} +elsif($opt_m eq 'sftp') { + $options{'port'} = $opt_P if $opt_P; + $options{'debug'} = $opt_v if $opt_v; + + my $ls_sftp = sftp(); + + $ls_sftp->setcwd($opt_r) or die "can't chdir to $opt_r\n" + if $opt_r; + + $ls = $ls_sftp->ls('.', no_wanted => qr/^\.+$/, + names_only => 1 ); +} +else { + die "Method '$opt_m' not supported; must be ftp or sftp\n"; +} + +### +# import each file +### + +foreach my $filename ( @$ls ) { + + next if $opt_d && $filename eq $opt_d; + + warn "Downloading $filename\n" if $opt_v; + + #get the file + if($opt_m eq 'ftp') { + my $ftp = ftp(); + $ftp->get($filename, "$cachedir/$filename") + or die "Can't get $filename: ". $ftp->message . "\n"; + } + else { + my $sftp = sftp(); + $sftp->get($filename, "$cachedir/$filename") + or die "Can't get $filename: ". $sftp->error . "\n"; + } + + warn "Processing $filename\n" if $opt_v; + + open my $fh, "$cachedir/$filename" or die "$cachedir/$filename: $!"; + my $header = $csv->getline($fh); + + while ( my $row = $csv->getline($fh) ) { + + my $i = 0; + my %hash = map { $_ => $row->[$i++] } @$header; + + my %dbhash = map { $aradial2db{$_} => $hash{$_} } + grep $aradial2db{$_}, + keys %hash; + + my @keys = keys %dbhash; + + #skip blank records + next unless grep defined($_), values %dbhash; + + my $date = time2str( '%Y-%m-%d %X', str2time( $hash{'Date'} ) ); + + $hash{'Status-Type'} = $status_type{ $hash{'Status-Type'} } + if exists $status_type{ $hash{'Status-Type'} }; + + my $sql; + my @extra_values = (); + if ( $hash{'Status-Type'} eq 'Start' ) { + + push @keys, 'AcctStartTime'; + $dbhash{'AcctStartTime'} = $date; + + $sql = 'INSERT INTO radacct ( '. join(',', @keys). + ' ) VALUES ( '. join(',', map ' ? ', @keys ). ' )'; + + } elsif ( $hash{'Status-Type'} eq 'Stop' ) { + + my $AcctSessionId = delete($dbhash{AcctSessionId}); + + push @keys, 'AcctStopTime'; + $dbhash{'AcctStopTime'} = $date; + + push @extra_values, $AcctSessionId; + + $sql = 'UPDATE radacct SET '. join(',', map "$_ = ?", @keys ). + ' WHERE AcctSessionId = ? '; + + } elsif ( $hash{'Status-Type'} eq 'Interim' ) { + #not handled, but stop should capture the usage. unless session are + # normally super-long, extending across month boundaries, or we need + # real-time-ish data usage detail, it isn't a big deal + } else { + warn 'Unknown Status-Type '. $hash{'Status-Type'}. "; skipping\n"; + next; + } + + my $sth = $dbh->prepare($sql) or die $dbh->errstr; + + my $p_num = 1; + foreach my $value ( map $dbhash{$_}, @keys ) { + my $key = shift @keys; + my $type = exists($bind_type{$key}) ? $bind_type{$key} : SQL_VARCHAR; + $value ||= 0 if $type == SQL_INTEGER; + $sth->bind_param($p_num++, $value, $type); + } + foreach my $value ( @extra_values ) { + $sth->bind_param($p_num++, $value); + } + + $sth->execute or die $sth->errstr; + + } + + if ( $opt_d ) { + my $file_timestamp = $filename.'-'.time2str('%Y-%m-%d', time); + if ( $opt_m eq 'ftp') { + my $ftp = ftp(); + $ftp->rename($filename, "$opt_d/$file_timestamp") + or do { + unlink "$cachedir/$filename"; + die "Can't move $filename to $opt_d: ".$ftp->message . "\n"; + }; + } else { + my $sftp = sftp(); + $sftp->rename($filename, "$opt_d/$file_timestamp") + or do { + unlink "$cachedir/$filename"; + die "can't move $filename to $opt_d: ". $sftp->error . "\n"; + }; + } + } + + unlink "$cachedir/$filename"; + +} + +### +# subs +### + +sub usage { + "Usage: + aradial-sftp_and_import [ -m method ] [ -a ] [ -b ] + [ -r remotefolder ] [ -d donefolder ] [ -v level ] [ -P port ] + user [sftpuser@]servername dbi_datasrc dbi_username dbi_pass + "; +} + +use vars qw( $sftp $ftp ); + +sub ftp { + return $ftp if $ftp && $ftp->pwd; + + my ($hostname, $userpass) = reverse split('@', $servername); + my ($ftp_user, $ftp_pass) = split(':', $userpass); + + my $ftp = Net::FTP->new($hostname, %options) + or die "FTP connection to '$hostname' failed."; + $ftp->login($ftp_user, $ftp_pass) or die "FTP login failed: ".$ftp->message; + $ftp->cwd($opt_r) or die "can't chdir to $opt_r\n" if $opt_r; + $ftp->binary or die "can't set BINARY mode: ". $ftp->message if $opt_b; + return $ftp; +} + +sub sftp { + + #reuse connections + return $sftp if $sftp && $sftp->cwd; + + my %sftp = ( host => $servername ); + + $sftp = Net::SFTP::Foreign->new(%sftp); + $sftp->error and die "SFTP connection failed: ". $sftp->error; + + $sftp; +} + +=head1 NAME + +freeside-aradial-sftp_and_import - Download Aradial "CDR" (really RADIUS detail) files from a remote server via SFTP + +=head1 SYNOPSIS + + aradial-sftp_and_import [ -m method ] [ -a ] [ -b ] + [ -r remotefolder ] [ -d donefolder ] [ -v level ] [ -P port ] + user [sftpuser@]servername dbi_datasrc dbi_username dbi_pass + +=head1 DESCRIPTION + +Command line tool to download CDR files from a remote server via SFTP +or FTP and then import them into the database. + +-m: transfer method (sftp or ftp), defaults to sftp + +-a: use ftp passive mode + +-b: use ftp binary mode + +-r: if specified, changes into this remote folder before starting + +-d: if specified, moves files to the specified folder when done + +-P: if specified, sets the port to use + +-v: set verbosity level; this script only has one level, but it will + be passed as the 'debug' argument to the transport method + +user: freeside username + +[sftpuser@]servername: remote server +(or ftpuser:ftppass@servername) + +=head1 BUGS + +=head1 SEE ALSO + +L + +=cut + +1; +