| 1808 |
richard |
1 |
#!/usr/bin/perl
|
|
|
2 |
use POSIX;
|
|
|
3 |
use File::Temp qw(tempfile tempdir);
|
|
|
4 |
|
|
|
5 |
# Log in the totacct table aggregated daily accounting information for
|
|
|
6 |
# each user.
|
|
|
7 |
# We keep a row per user for each day.
|
|
|
8 |
# Works with mysql and postgresql
|
|
|
9 |
#
|
|
|
10 |
|
|
|
11 |
$conf=shift||'/etc/freeradius-web/admin.conf';
|
|
|
12 |
|
|
|
13 |
|
|
|
14 |
open CONF, "<$conf"
|
|
|
15 |
or die "Could not open configuration file\n";
|
|
|
16 |
while(<CONF>){
|
|
|
17 |
chomp;
|
|
|
18 |
($key,$val)=(split /:\s*/,$_);
|
|
|
19 |
$sql_type = $val if ($key eq 'sql_type');
|
|
|
20 |
$sql_server = $val if ($key eq 'sql_server');
|
|
|
21 |
$sql_username = $val if ($key eq 'sql_username');
|
|
|
22 |
$sql_password = $val if ($key eq 'sql_password');
|
|
|
23 |
$sql_database = $val if ($key eq 'sql_database');
|
|
|
24 |
$sql_accounting_table = $val if ($key eq 'sql_accounting_table');
|
|
|
25 |
$sqlcmd = $val if ($key eq 'sql_command');
|
|
|
26 |
}
|
|
|
27 |
close CONF;
|
|
|
28 |
|
|
|
29 |
die "sql_command directive is not set in admin.conf\n" if ($sqlcmd eq '');
|
|
|
30 |
die "sql command '$sqlcmd' not found or does not seem to be executable\n" if (! -x $sqlcmd);
|
|
|
31 |
|
|
|
32 |
if ($sql_type eq 'mysql'){
|
|
|
33 |
$sql_password = (!$sql_password) ? '' : "-p$sql_password";
|
|
|
34 |
}
|
|
|
35 |
$sql_password =~ s/(\W)/\\$1/g;
|
|
|
36 |
|
|
|
37 |
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
|
|
|
38 |
$date_start = POSIX::strftime("%Y-%m-%d %T",0,0,0,($mday - 1),$mon,$year,$wday,$yday,$isdst);
|
|
|
39 |
$date_small_start = POSIX::strftime("%Y-%m-%d",0,0,0,($mday - 1),$mon,$year,$wday,$yday,$isdst);
|
|
|
40 |
$date_end = POSIX::strftime("%Y-%m-%d %T",0,0,0,$mday,$mon,$year,$wday,$yday,$isdst);
|
|
|
41 |
|
|
|
42 |
$query1 = "DELETE FROM totacct WHERE AcctDate = '$date_start';";
|
|
|
43 |
$query2 = "INSERT INTO totacct (UserName,AcctDate,ConnNum,ConnTotDuration,
|
|
|
44 |
ConnMaxDuration,ConnMinDuration,InputOctets,OutputOctets,NASIPAddress)
|
|
|
45 |
SELECT UserName,'$date_small_start',COUNT(*),SUM(AcctSessionTime),
|
|
|
46 |
MAX(AcctSessionTime),MIN(AcctSessionTime),SUM(AcctInputOctets),
|
|
|
47 |
SUM(AcctOutputOctets),NASIPAddress FROM radacct
|
|
|
48 |
WHERE AcctStopTime >= '$date_start' AND
|
|
|
49 |
AcctStopTime < '$date_end' GROUP BY UserName,NASIPAddress;";
|
|
|
50 |
print "$query1\n";
|
|
|
51 |
print "$query2\n";
|
|
|
52 |
my ($fh, $tmp_filename) = tempfile() or die "Could not open tmp file\n";
|
|
|
53 |
print $fh "ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';\n" if ($sql_type eq 'oracle');
|
|
|
54 |
print $fh $query1;
|
|
|
55 |
print $fh $query2;
|
|
|
56 |
close $fh;
|
|
|
57 |
$command = "$sqlcmd -h $sql_server -u $sql_username $sql_password $sql_database < $tmp_filename" if ($sql_type eq 'mysql');
|
|
|
58 |
$command = "$sqlcmd -U $sql_username -f $tmp_filename $sql_database" if ($sql_type eq 'pg');
|
|
|
59 |
$command = "$sqlcmd $sql_username/$pass" . "@" . "$sql_database <$tmpfile.$server" if ($sql_type eq 'oracle');
|
|
|
60 |
$command = "$sqlcmd '$sql_server' '$sql_port' '' '$sql_username' '$sql_password' < $tmp_filename" if ($sql_type eq 'sqlrelay');
|
|
|
61 |
`$command`;
|