0,0 → 1,64 |
#!/usr/bin/perl |
use POSIX; |
use File::Temp qw(tempfile tempdir); |
|
# Log in the mtotacct table aggregated accounting information for |
# each user spaning in one month period. |
# If the current month has not ended it will log information up to |
# the current month day |
# Works only with mysql and postgresql |
# |
|
$conf=shift||'/etc/freeradius-web/admin.conf'; |
|
|
open CONF, "<$conf" |
or die "Could not open configuration file\n"; |
while(<CONF>){ |
chomp; |
($key,$val)=(split /:\s*/,$_); |
$sql_type = $val if ($key eq 'sql_type'); |
$sql_server = $val if ($key eq 'sql_server'); |
$sql_username = $val if ($key eq 'sql_username'); |
$sql_password = $val if ($key eq 'sql_password'); |
$sql_database = $val if ($key eq 'sql_database'); |
$sql_accounting_table = $val if ($key eq 'sql_accounting_table'); |
$sqlcmd = $val if ($key eq 'sql_command'); |
} |
close CONF; |
|
die "sql_command directive is not set in admin.conf\n" if ($sqlcmd eq ''); |
die "sql command '$sqlcmd' not found or does not seem to be executable\n" if (! -x $sqlcmd); |
|
if ($sql_type eq 'mysql'){ |
$sql_password = (!$sql_password) ? '' : "-p$sql_password"; |
} |
$sql_password =~ s/(\W)/\\$1/g; |
|
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime; |
if ($mday == 1){ |
$mon--; |
} |
$date_start = POSIX::strftime("%Y-%m-%d",0,0,0,1,$mon,$year,$wday,$yday,$isdst); |
$date_end = POSIX::strftime("%Y-%m-%d",0,0,0,$mday,$mon,$year,$wday,$yday,$isdst); |
|
$query1 = "DELETE FROM mtotacct WHERE AcctDate = '$date_start';"; |
$query2 = "INSERT INTO mtotacct (UserName,AcctDate,ConnNum,ConnTotDuration, |
ConnMaxDuration,ConnMinDuration,InputOctets,OutputOctets,NASIPAddress) |
SELECT UserName,'$date_start',SUM(ConnNum),SUM(ConnTotDuration), |
MAX(ConnMaxDuration),MIN(ConnMinDuration),SUM(InputOctets), |
SUM(OutputOctets),NASIPAddress FROM totacct |
WHERE AcctDate >= '$date_start' AND |
AcctDate <= '$date_end' GROUP BY UserName,NASIPAddress;"; |
print "$query1\n"; |
print "$query2\n"; |
my ($fh, $tmp_filename) = tempfile() or die "Could not open tmp file\n"; |
print $fh "ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';\n" if ($sql_type eq 'oracle'); |
print $fh $query1; |
print $fh $query2; |
close $fh; |
$command = "$sqlcmd -h $sql_server -u $sql_username $sql_password $sql_database < $tmp_filename" if ($sql_type eq 'mysql'); |
$command = "$sqlcmd -U $sql_username -f $tmp_filename $sql_database" if ($sql_type eq 'pg'); |
$command = "$sqlcmd $sql_username/$pass" . "@" . "$sql_database <$tmpfile.$server" if ($sql_type eq 'oracle'); |
$command = "$sqlcmd '$sql_server' '$sql_port' '' '$sql_username' '$sql_password' < $tmp_filename" if ($sql_type eq 'sqlrelay'); |
`$command`; |
Property changes: |
Added: svn:executable |
+* |
\ No newline at end of property |