Subversion Repositories ALCASAR

Rev

Details | Last modification | View Log

Rev Author Line No. Line
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`;