3 #i'm kinda like freeside-cdr-sftp_and_import... some parts should be libraried
10 use DBI qw( :sql_types );
12 use Net::SFTP::Foreign;
13 #use FS::UID qw( adminsuidsetup datasrc );
15 #adjusted these for what we're actually seeing in the real log files
18 #'NASIP' => 'NASIPAddress',
19 'NASID' => 'NASIPAddress',
20 'AcctSessionId' => 'AcctSessionId',
21 'Port' => 'NasPortId',
22 #'Status-Type' => 'Acct-Status-Type',
23 #'UserID' => 'UserName',
24 'User ID' => 'UserName',
25 'Authentic' => 'AcctAuthentic',
26 'Service-Type' => 'ServiceType',
27 'FramedProtocol' => 'FramedProtocol',
28 #'FramedCompression' => '', #not handled, needed? unlikely
29 'FramedAddress' => 'FramedIPAddress',
30 'Acct-Delay-Time' => 'AcctStartDelay', #?
31 'Session-Time' => 'AcctSessionTime',
32 #'Input-Gigawords' => '', #XXX handle lots of data
33 'Input-Octets' => 'AcctInputOctets',
34 #'Output-Gigawords' => '', #XXX handle lots of data
35 'Output-Octets' => 'AcctOutputOctets',
36 'NAS-Port-Type' => 'NASPortType',
37 'Acct-Terminate-Cause' => 'AcctTerminateCause',
41 'AcctInputOctets' => SQL_INTEGER,
42 'AcctOutputOctets' => SQL_INTEGER,
43 'AcctSessionTime' => SQL_INTEGER,
44 'AcctStartDelay' => SQL_INTEGER,
45 'AcctStopDelay' => SQL_INTEGER,
48 #http://www.iana.org/assignments/radius-types/radius-types.xhtml#radius-types-10
52 3 => 'Interim-Update',
55 8 => 'Accounting-Off',
58 11 => 'Tunnel-Reject',
59 12 => 'Tunnel-Link-Start',
60 13 => 'Tunnel-Link-Stop',
61 14 => 'Tunnel-Link-Reject',
69 use vars qw( $opt_m $opt_a $opt_b $opt_r $opt_d $opt_v $opt_P );
70 getopts('m:abr:d:P:v:');
74 my $user = shift or die &usage;
75 #adminsuidsetup $user;
77 # %%%FREESIDE_CACHE%%% & hardcoded datasrc
78 #my $cachedir = '%%%FREESIDE_CACHE%%%/cache.'. datasrc. '/cdrs';
79 my $cachedir = '/usr/local/etc/freeside/cache.DBI:Pg:dbname=freeside/cdrs';
80 mkdir $cachedir unless -d $cachedir;
82 my $servername = shift or die &usage;
84 my( $datasrc, $db_user, $db_pass ) = ( shift, shift, shift );
85 my $dbh = FS::DBI->connect( $datasrc, $db_user, $db_pass)
86 or die "can't connect: $FS::DBI::errstr\n";
88 my $csv = Text::CSV_XS->new;
94 warn "Retrieving directory listing\n" if $opt_v;
96 $opt_m = 'sftp' if !defined($opt_m);
101 if($opt_m eq 'ftp') {
102 $options{'Port'} = $opt_P if $opt_P;
103 $options{'Debug'} = $opt_v if $opt_v;
104 $options{'Passive'} = $opt_a if $opt_a;
108 $ls = [ grep { /^.*$/i } $ls_ftp->ls ];
110 elsif($opt_m eq 'sftp') {
111 $options{'port'} = $opt_P if $opt_P;
112 $options{'debug'} = $opt_v if $opt_v;
114 my $ls_sftp = sftp();
116 $ls_sftp->setcwd($opt_r) or die "can't chdir to $opt_r\n"
119 $ls = $ls_sftp->ls('.', no_wanted => qr/^\.+$/,
123 die "Method '$opt_m' not supported; must be ftp or sftp\n";
130 foreach my $filename ( @$ls ) {
132 next if $opt_d && $filename eq $opt_d;
134 warn "Downloading $filename\n" if $opt_v;
137 if($opt_m eq 'ftp') {
139 $ftp->get($filename, "$cachedir/$filename")
140 or die "Can't get $filename: ". $ftp->message . "\n";
144 $sftp->get($filename, "$cachedir/$filename")
145 or die "Can't get $filename: ". $sftp->error . "\n";
148 warn "Processing $filename\n" if $opt_v;
150 open my $fh, "$cachedir/$filename" or die "$cachedir/$filename: $!";
151 my $header = $csv->getline($fh);
153 while ( my $row = $csv->getline($fh) ) {
156 my %hash = map { $_ => $row->[$i++] } @$header;
158 my %dbhash = map { $aradial2db{$_} => $hash{$_} }
159 grep $aradial2db{$_},
162 my @keys = keys %dbhash;
165 next unless grep defined($_), values %dbhash;
167 my $date = time2str( '%Y-%m-%d %X', str2time( $hash{'Date'} ) );
169 $hash{'Status-Type'} = $status_type{ $hash{'Status-Type'} }
170 if exists $status_type{ $hash{'Status-Type'} };
173 my @extra_values = ();
174 if ( $hash{'Status-Type'} eq 'Start' ) {
176 push @keys, 'AcctStartTime';
177 $dbhash{'AcctStartTime'} = $date;
179 $sql = 'INSERT INTO radacct ( '. join(',', @keys).
180 ' ) VALUES ( '. join(',', map ' ? ', @keys ). ' )';
182 } elsif ( $hash{'Status-Type'} eq 'Stop' ) {
184 my $AcctSessionId = delete($dbhash{AcctSessionId});
186 push @keys, 'AcctStopTime';
187 $dbhash{'AcctStopTime'} = $date;
189 push @extra_values, $AcctSessionId;
191 $sql = 'UPDATE radacct SET '. join(',', map "$_ = ?", @keys ).
192 ' WHERE AcctSessionId = ? ';
194 } elsif ( $hash{'Status-Type'} eq 'Interim' ) {
195 #not handled, but stop should capture the usage. unless session are
196 # normally super-long, extending across month boundaries, or we need
197 # real-time-ish data usage detail, it isn't a big deal
199 warn 'Unknown Status-Type '. $hash{'Status-Type'}. "; skipping\n";
203 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
206 foreach my $value ( map $dbhash{$_}, @keys ) {
207 my $key = shift @keys;
208 my $type = exists($bind_type{$key}) ? $bind_type{$key} : SQL_VARCHAR;
209 $value ||= 0 if $type == SQL_INTEGER;
210 $sth->bind_param($p_num++, $value, $type);
212 foreach my $value ( @extra_values ) {
213 $sth->bind_param($p_num++, $value);
216 $sth->execute or die $sth->errstr;
221 my $file_timestamp = $filename.'-'.time2str('%Y-%m-%d', time);
222 if ( $opt_m eq 'ftp') {
224 $ftp->rename($filename, "$opt_d/$file_timestamp")
226 unlink "$cachedir/$filename";
227 die "Can't move $filename to $opt_d: ".$ftp->message . "\n";
231 $sftp->rename($filename, "$opt_d/$file_timestamp")
233 unlink "$cachedir/$filename";
234 die "can't move $filename to $opt_d: ". $sftp->error . "\n";
239 unlink "$cachedir/$filename";
249 aradial-sftp_and_import [ -m method ] [ -a ] [ -b ]
250 [ -r remotefolder ] [ -d donefolder ] [ -v level ] [ -P port ]
251 user [sftpuser@]servername dbi_datasrc dbi_username dbi_pass
255 use vars qw( $sftp $ftp );
258 return $ftp if $ftp && $ftp->pwd;
260 my ($hostname, $userpass) = reverse split('@', $servername);
261 my ($ftp_user, $ftp_pass) = split(':', $userpass);
263 my $ftp = Net::FTP->new($hostname, %options)
264 or die "FTP connection to '$hostname' failed.";
265 $ftp->login($ftp_user, $ftp_pass) or die "FTP login failed: ".$ftp->message;
266 $ftp->cwd($opt_r) or die "can't chdir to $opt_r\n" if $opt_r;
267 $ftp->binary or die "can't set BINARY mode: ". $ftp->message if $opt_b;
274 return $sftp if $sftp && $sftp->cwd;
276 my %sftp = ( host => $servername );
278 $sftp = Net::SFTP::Foreign->new(%sftp);
279 $sftp->error and die "SFTP connection failed: ". $sftp->error;
286 freeside-aradial-sftp_and_import - Download Aradial "CDR" (really RADIUS detail) files from a remote server via SFTP
290 aradial-sftp_and_import [ -m method ] [ -a ] [ -b ]
291 [ -r remotefolder ] [ -d donefolder ] [ -v level ] [ -P port ]
292 user [sftpuser@]servername dbi_datasrc dbi_username dbi_pass
296 Command line tool to download CDR files from a remote server via SFTP
297 or FTP and then import them into the database.
299 -m: transfer method (sftp or ftp), defaults to sftp
301 -a: use ftp passive mode
303 -b: use ftp binary mode
305 -r: if specified, changes into this remote folder before starting
307 -d: if specified, moves files to the specified folder when done
309 -P: if specified, sets the port to use
311 -v: set verbosity level; this script only has one level, but it will
312 be passed as the 'debug' argument to the transport method
314 user: freeside username
316 [sftpuser@]servername: remote server
317 (or ftpuser:ftppass@servername)