From 5de222c7056149a131fa1e0fba317a49ca014672 Mon Sep 17 00:00:00 2001 From: kkalev Date: Thu, 21 Nov 2002 23:33:32 +0000 Subject: [PATCH] * Remove one sql query from user_admin which was not needed. * Instead of a query like "LIKE 'YYYY-MM-DD%'" use "AcctStopTime >= 'YYYY-MM-DD 00:00:00 AND AcctStopTime <= 'YYYY-MM-DD 23:59:59'" which will allow us to use sql indexes better. * Add a few comments in bin/clean_radacct * Add a new script bin/truncate_radacct which will delete all sessions from the radacct table which are older than a configurable number of days. * Add an entry in tuning_guide about creating a multi column index for (UserName,AcctStopTime). This will really help sql_checksimul and the corresponding queries. --- Changelog | 7 +++++++ bin/clean_radacct | 4 ++++ bin/truncate_radacct | 31 +++++++++++++++++++++++++++++++ htdocs/stats.php3 | 3 ++- htdocs/user_admin.php3 | 35 +++++++++++++++-------------------- htdocs/user_state.php3 | 3 ++- 6 files changed, 61 insertions(+), 22 deletions(-) create mode 100755 bin/truncate_radacct diff --git a/Changelog b/Changelog index 552554b..d9aeb33 100644 --- a/Changelog +++ b/Changelog @@ -1,3 +1,10 @@ +Ver 1.62: +* Remove one sql query from user_admin which was not needed. +* Instead of a query like "LIKE 'YYYY-MM-DD%'" use "AcctStopTime >= 'YYYY-MM-DD 00:00:00 AND AcctStopTime + <= 'YYYY-MM-DD 23:59:59'" which will allow us to use sql indexes better. +* Add a few comments in bin/clean_radacct +* Add a new script bin/truncate_radacct which will delete all sessions from the radacct table which + are older than a configurable number of days Ver 1.61: * Add a string encoder for greek * If general_decode_normal_attributes is set then encode attributes in lib/ldap/change_info. In the near future diff --git a/bin/clean_radacct b/bin/clean_radacct index cc794f9..29fbd1a 100755 --- a/bin/clean_radacct +++ b/bin/clean_radacct @@ -1,4 +1,8 @@ #!/usr/bin/perl +# +# Clean stale open sessions from the radacct table. +# we only clean up sessions which are older than $back_days +# use DBI; use POSIX; diff --git a/bin/truncate_radacct b/bin/truncate_radacct new file mode 100755 index 0000000..b12c9d9 --- /dev/null +++ b/bin/truncate_radacct @@ -0,0 +1,31 @@ +#!/usr/bin/perl +# +# Delete sessions from the radacct table which are older than +# $back_days +# +use DBI; +use POSIX; + +$conf=shift||'/usr/local/dialup_admin/conf/admin.conf'; +$back_days = 90; + +open CONF, "<$conf" + or die "Could not open configuration file\n"; +while(){ + chomp; + ($key,$val)=(split /:\s*/,$_); + $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'); +} +close CONF; + +($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime; +$date = POSIX::strftime("%Y-%m-%d %T",$sec,$min,$hour,($mday - $back_days),$mon,$year,$wday,$yday,$isdst); +print "$date\n"; + +$dbh = DBI->connect("DBI:mysql:$sql_database:$sql_server","$sql_username","$sql_password"); +$dbh->do("DELETE FROM $sql_accounting_table WHERE AcctStopTime < '$date';"); +$dbh->disconnect(); diff --git a/htdocs/stats.php3 b/htdocs/stats.php3 index 3189e60..709d6fa 100644 --- a/htdocs/stats.php3 +++ b/htdocs/stats.php3 @@ -89,7 +89,8 @@ if ($link){ $day = "$days[$i]"; $search = @da_sql_query($link,$config, "SELECT $res[1],$res[2],$res[3] FROM $config[sql_accounting_table] - $sql_val[user] AND AcctStopTime LIKE '$day%' $s;"); + $sql_val[user] AND AcctStopTime >= '$day 00:00:00' + AND AcctStopTime <= '$day 23:59:59' $s;"); if ($search){ $row = @da_sql_fetch_array($search,$config); $data[$day][1] = $row["$res[1]"]; diff --git a/htdocs/user_admin.php3 b/htdocs/user_admin.php3 index 4dffdd5..dfd147d 100644 --- a/htdocs/user_admin.php3 +++ b/htdocs/user_admin.php3 @@ -91,6 +91,7 @@ if ($link){ AND AcctStartTime >= '$week_str' AND AcctStartTime <= '$now_str';"); if ($search){ $row = @da_sql_fetch_array($search,$config); + $weekly_used = $row['sum(AcctSessionTime)']; $tot_time = time2str($row['sum(AcctSessionTime)']); $tot_input = bytes2str($row['sum(AcctInputOctets)']); $tot_output = bytes2str($row['sum(AcctOutputOctets)']); @@ -114,7 +115,8 @@ if ($link){ continue; $search = @da_sql_query($link,$config, "SELECT sum(AcctSessionTime) FROM $config[sql_accounting_table] WHERE - UserName = '$login' AND AcctStopTime LIKE '$days[$i]%';"); + UserName = '$login' AND AcctStopTime >= '$days[$i] 00:00:00' + AND AcctStopTime <= '$days[$i] 23:59:59';"); if ($search){ $row = @da_sql_fetch_array($search,$config); $used[$i] = $row['sum(AcctSessionTime)']; @@ -138,27 +140,20 @@ if ($link){ } } } - $search = @da_sql_query($link,$config, - "SELECT sum(AcctSessionTime) FROM $config[sql_accounting_table] WHERE - UserName = '$login' AND AcctStopTime >= '$week_start' AND - AcctStopTime <= '$now_str';"); - if ($search){ - $row = @da_sql_fetch_array($search,$config); - $weekly_used = $row['sum(AcctSessionTime)']; - if ($weekly_limit != 'none'){ - $tmp = $weekly_limit - $weekly_used; - if ($tmp <=0){ - $tmp = 0; - $extra_msg .= '(Out of weekly quota)'; - } - if ($remaining > $tmp) - $remaining = $tmp; - $log_color = ($remaining) ? 'green' : 'red'; + if ($weekly_limit != 'none'){ + $tmp = $weekly_limit - $weekly_used; + if ($tmp <=0){ + $tmp = 0; + $extra_msg .= '(Out of weekly quota)'; } - $weekly_used = time2str($weekly_used); - if ($weekly_limit != 'none' && !$tmp) - $weekly_used = "$weekly_used"; + if ($remaining > $tmp) + $remaining = $tmp; + $log_color = ($remaining) ? 'green' : 'red'; } + $weekly_used = time2str($weekly_used); + if ($weekly_limit != 'none' && !$tmp) + $weekly_used = "$weekly_used"; + $search = @da_sql_query($link,$config, "SELECT * FROM $config[sql_accounting_table] WHERE UserName = '$login' AND AcctStopTime = '0' diff --git a/htdocs/user_state.php3 b/htdocs/user_state.php3 index a8e4595..7ea4f79 100644 --- a/htdocs/user_state.php3 +++ b/htdocs/user_state.php3 @@ -39,7 +39,8 @@ if ($link){ } $search = @da_sql_query($link,$config, "SELECT COUNT(*),sum(AcctSessionTime) FROM $config[sql_accounting_table] WHERE - UserName = '$login' AND AcctStopTime LIKE '$today%';"); + UserName = '$login' AND AcctStopTime >= '$today 00:00:00' + AND AcctStopTime <= '$today 23:59:59';"); if ($search){ $row = @da_sql_fetch_array($search,$config); $daily_used = time2strclock($row['sum(AcctSessionTime)']); -- 2.39.5