4 # Log in the mtotacct table aggregated accounting information for
5 # each user spaning in one month period.
6 # If the current month has not ended it will log information up to
7 # the current month day
8 # Works only with mysql and postgresql
11 $conf=shift||'/usr/local/dialup_admin/conf/admin.conf';
15 or die "Could not open configuration file\n";
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');
29 die "sql_command directive is not set in admin.conf\n" if ($sqlcmd eq '');
30 die "Could not find sql binary. Please make sure that the \$sqlcmd variable points to the right location\n" if (! -x $sqlcmd);
32 $sql_password = (!$sql_password) ? '' : "-p$sql_password";
34 ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
38 $date_start = POSIX::strftime("%Y-%m-%d",0,0,0,1,$mon,$year,$wday,$yday,$isdst);
39 $date_end = POSIX::strftime("%Y-%m-%d",0,0,0,$mday,$mon,$year,$wday,$yday,$isdst);
41 $query1 = "DELETE FROM mtotacct WHERE AcctDate = '$date_start';";
42 $query2 = "INSERT INTO mtotacct (UserName,AcctDate,ConnNum,ConnTotDuration,
43 ConnMaxDuration,ConnMinDuration,InputOctets,OutputOctets,NASIPAddress)
44 SELECT UserName,'$date_start',SUM(ConnNum),SUM(ConnTotDuration),
45 MAX(ConnMaxDuration),MIN(ConnMinDuration),SUM(InputOctets),
46 SUM(OutputOctets),NASIPAddress FROM totacct
47 WHERE AcctDate >= '$date_start' AND
48 AcctDate <= '$date_end' GROUP BY UserName,NASIPAddress;";
51 open TMP, ">/tmp/tot_stats.query"
52 or die "Could not open tmp file\n";
56 $command = "$sqlcmd -h $sql_server -u $sql_username $sql_password $sql_database </tmp/tot_stats.query" if ($sql_type eq 'mysql');
57 $command = "$sqlcmd -U $sql_username -f /tmp/tot_stats.query $sql_database" if ($sql_type eq 'pg');