-#!/usr/bin/perl
+#!/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;
+use DBI qw( :sql_types );
+use FS::DBI;
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',
+ #'NASIP' => 'NASIPAddress',
+ 'NASID' => 'NASIPAddress',
'AcctSessionId' => 'AcctSessionId',
'Port' => 'NasPortId',
#'Status-Type' => 'Acct-Status-Type',
- 'UserID' => 'UserName',
- 'Authentic' => 'AccdtAuthentic',
+ #'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' => '',
+ #'Input-Gigawords' => '', #XXX handle lots of data
'Input-Octets' => 'AcctInputOctets',
- #'Output-Gigawords' => '',
+ #'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:dP:v:');
+getopts('m:abr:d:P:v:');
my %options = ();
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 $dbh = FS::DBI->connect( $datasrc, $db_user, $db_pass)
+ or die "can't connect: $FS::DBI::errstr\n";
my $csv = Text::CSV_XS->new;
$ls_sftp->setcwd($opt_r) or die "can't chdir to $opt_r\n"
if $opt_r;
- $ls = $ls_sftp->ls('.', wanted => qr/^$.*\.$/i,
+ $ls = $ls_sftp->ls('.', no_wanted => qr/^\.+$/,
names_only => 1 );
}
else {
foreach my $filename ( @$ls ) {
+ next if $opt_d && $filename eq $opt_d;
+
warn "Downloading $filename\n" if $opt_v;
#get the file
warn "Processing $filename\n" if $opt_v;
- my $file_timestamp = $filename.'-'.time2str('%Y-%m-%d', time);
-
open my $fh, "$cachedir/$filename" or die "$cachedir/$filename: $!";
my $header = $csv->getline($fh);
keys %hash;
my @keys = keys %dbhash;
- my @values = map $dbhash{$_}, @keys;
- if ( $hash{'Acct-Status-Type'} eq 'Start' ) {
+ #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'} };
- $dbhash{'AcctStartTime'} = $hash{'Date'};
+ my $sql;
+ my @extra_values = ();
+ if ( $hash{'Status-Type'} eq 'Start' ) {
- my $sql = 'INSERT INTO radacct ( ', join(',', @keys).
- ' ) VALUES ( '. map( ' ? ', @values ). ' )';
- my $sth = $dbh->prepare($sql) or die $dbh->errstr;
- $sth->execute(@values) or die $sth->errstr;
+ push @keys, 'AcctStartTime';
+ $dbhash{'AcctStartTime'} = $date;
- } elsif ( $hash{'Acct-Status-Type'} eq 'Stop' ) {
+ $sql = 'INSERT INTO radacct ( '. join(',', @keys).
+ ' ) VALUES ( '. join(',', map ' ? ', @keys ). ' )';
+
+ } elsif ( $hash{'Status-Type'} eq 'Stop' ) {
my $AcctSessionId = delete($dbhash{AcctSessionId});
- $dbhash{'AcctStopTime'} = $hash{'Date'};
- my $sql = 'UPDATE radacct '. join(' , ', map "SET $_ = ?", @keys ).
- ' WHERE AcctSessionId = ? ';
- my $sth = $dbh->prepare($sql) or die $dbh->errstr;
- $sth->execute(@values, $AcctSessionId) or die $sth->errstr;
+ push @keys, 'AcctStopTime';
+ $dbhash{'AcctStopTime'} = $date;
+
+ push @extra_values, $AcctSessionId;
- } elsif ( $hash{'Acct-Status-Type'} eq 'Interim' ) {
+ $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 {
- die 'Unknown Acct-Status-Type '. $hash{'Acct-Status-Type'}. "\n";
+ 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")
projects / freeside.git / blobdiff