From a7f7b3120942c8154d46d39670e000c005dff281 Mon Sep 17 00:00:00 2001
From: kkalev <kkalev>
Date: Tue, 28 Jan 2003 15:59:16 +0000
Subject: [PATCH]   Add two new tables totacct and mtotacct containing per user
 aggregated statistics for each day and month   respectively. Also add two
 corresponding scripts in the bin folder, tot_stats and monthly_tot_stats.
 Lastly,   create a new page, user_stats.php3 which will show the top users in
 connections or connections duration based   on the data in the totacct table.

---
 Changelog                              |   4 +
 bin/monthly_tot_stats                  |  42 +++++
 bin/tot_stats                          |  37 +++++
 htdocs/user_stats.php3                 | 204 +++++++++++++++++++++++++
 html/buttons/default/buttons.html.php3 |   3 +
 sql/mtotacct.sql                       |  17 +++
 sql/totacct.sql                        |  18 +++
 7 files changed, 325 insertions(+)
 create mode 100755 bin/monthly_tot_stats
 create mode 100755 bin/tot_stats
 create mode 100644 htdocs/user_stats.php3
 create mode 100644 sql/mtotacct.sql
 create mode 100644 sql/totacct.sql

diff --git a/Changelog b/Changelog
index bc20eb1..9b211e5 100644
--- a/Changelog
+++ b/Changelog
@@ -35,6 +35,10 @@ Ver 1.62:
 * Do a write lock in radacct before truncating it in truncate_radacct
 * In user_new show a select box with all the available groups. Based on an idea by Karel Stadler (kstadler)
 * Add a column Admin in the badusers table which will contain the administrator username if that is available
+* Add two new tables totacct and mtotacct containing per user aggregated statistics for each day and month
+  respectively. Also add two corresponding scripts in the bin folder, tot_stats and monthly_tot_stats. Lastly,
+  create a new page, user_stats.php3 which will show the top users in connections or connections duration based
+  on the data in the totacct table.
 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/monthly_tot_stats b/bin/monthly_tot_stats
new file mode 100755
index 0000000..42676a8
--- /dev/null
+++ b/bin/monthly_tot_stats
@@ -0,0 +1,42 @@
+#!/usr/bin/perl
+use POSIX;
+
+$conf=shift||'/usr/local/dialupadmin/conf/admin.conf';
+$mysql='/usr/local/mysql/bin/mysql';
+
+open CONF, "<$conf"
+	or die "Could not open configuration file\n";
+while(<CONF>){
+	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;
+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;";
+print "$query1\n";
+print "$query2\n";
+open TMP, ">/tmp/tot_stats.query"
+	or die "Could not open tmp file\n";
+print TMP $query1;
+print TMP $query2;
+close TMP;
+`$mysql -h $sql_server -u $sql_username -p$sql_password $sql_database </tmp/tot_stats.query`;
diff --git a/bin/tot_stats b/bin/tot_stats
new file mode 100755
index 0000000..5fb7dcc
--- /dev/null
+++ b/bin/tot_stats
@@ -0,0 +1,37 @@
+#!/usr/bin/perl
+use POSIX;
+
+$conf=shift||'/usr/local/dialupadmin/conf/admin.conf';
+$mysql='/usr/local/mysql/bin/mysql';
+
+open CONF, "<$conf"
+	or die "Could not open configuration file\n";
+while(<CONF>){
+	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_start = POSIX::strftime("%Y-%m-%d %T",0,0,0,($mday - 1),$mon,$year,$wday,$yday,$isdst);
+$date_small_start = POSIX::strftime("%Y-%m-%d",0,0,0,($mday - 1),$mon,$year,$wday,$yday,$isdst);
+$date_end = POSIX::strftime("%Y-%m-%d %T",0,0,0,$mday,$mon,$year,$wday,$yday,$isdst);
+
+$query = "INSERT INTO totacct (UserName,AcctDate,ConnNum,ConnTotDuration,
+	ConnMaxDuration,ConnMinDuration,InputOctets,OutputOctets,NASIPAddress)
+	SELECT UserName,'$date_small_start',COUNT(*),SUM(AcctSessionTime),
+	MAX(AcctSessionTime),MIN(AcctSessionTime),SUM(AcctInputOctets),
+	SUM(AcctOutputOctets),NASIPAddress FROM radacct
+	WHERE AcctStopTime >= '$date_start' AND
+	AcctStopTime < '$date_end' GROUP BY UserName;";
+print "$query\n";
+open TMP, ">/tmp/tot_stats.query"
+	or die "Could not open tmp file\n";
+print TMP $query;
+close TMP;
+`$mysql -h $sql_server -u $sql_username -p$sql_password $sql_database </tmp/tot_stats.query`;
diff --git a/htdocs/user_stats.php3 b/htdocs/user_stats.php3
new file mode 100644
index 0000000..1956b5e
--- /dev/null
+++ b/htdocs/user_stats.php3
@@ -0,0 +1,204 @@
+<?php
+require('../conf/config.php3');
+require('../lib/functions.php3');
+?>
+<html>
+<?php
+
+if (is_file("../lib/sql/drivers/$config[sql_type]/functions.php3"))
+	include_once("../lib/sql/drivers/$config[sql_type]/functions.php3");
+else{
+	echo <<<EOM
+<title>User Statistics</title>
+<link rel="stylesheet" href="style.css">
+</head>
+<body bgcolor="#80a040" background="images/greenlines1.gif" link="black" alink="black">
+<center>
+<b>Could not include SQL library functions. Aborting</b>
+</body>
+</html>
+EOM;
+	exit();
+}
+
+if ($start == '' && $stop == ''){
+	$now = time();
+	$stop = date($config[sql_date_format],$now);
+	$now -= 604800;
+	$start = date($config[sql_date_format],$now);
+}
+$pagesize = ($pagesize) ? $pagesize : 10;
+$limit = ($pagesize == 'all') ? '' : "LIMIT $pagesize";
+$selected[$pagesize] = 'selected';
+$order = ($order) ? $order : $config[general_accounting_info_order];
+if ($order != 'desc' && $order != 'asc')
+	$order = 'desc';
+if ($sortby != '')
+	$order_attr = ($sortby == 'num') ? 'ConnNum' : 'ConnTotDuration';
+else
+	$order_attr = 'ConnNum';
+if ($server != '' && $server != 'all')
+	$server_str = "AND NASIPAddress = '$server'";
+
+$selected[$order] = 'selected';
+$selected[$sortby] = 'selected';
+
+?>
+
+<head>
+<title>User Statistics</title>
+<link rel="stylesheet" href="style.css">
+</head>
+<body bgcolor="#80a040" background="images/greenlines1.gif" link="black" alink="black">
+<center>
+<table border=0 width=550 cellpadding=0 cellspacing=0>
+<tr valign=top>
+<td align=center><img src="images/title2.gif"></td>
+</tr>
+</table>
+<table border=0 width=400 cellpadding=0 cellspacing=2>
+</table>
+<br>
+<table border=0 width=840 cellpadding=1 cellspacing=1>
+<tr valign=top>
+<td width=65%></td>
+<td bgcolor="black" width=35%>
+	<table border=0 width=100% cellpadding=2 cellspacing=0>
+	<tr bgcolor="#907030" align=right valign=top><th>
+	<font color="white">User Statistics</font>&nbsp;
+	</th></tr>
+	</table>
+</td></tr>
+<tr bgcolor="black" valign=top><td colspan=2>
+	<table border=0 width=100% cellpadding=12 cellspacing=0 bgcolor="#ffffd0" valign=top>
+	<tr><td>
+<?php
+echo <<<EOM
+<b>$start</b> up to <b>$stop</b>
+EOM;
+?>
+
+<p>
+	<table border=1 bordercolordark=#ffffe0 bordercolorlight=#000000 width=100% cellpadding=2 cellspacing=0 bgcolor="#ffffe0" valign=top>
+	<tr bgcolor="#d0ddb0">
+	<th>#</th><th>login</th><th>date</th><th>server</th><th>connections number</th><th>connections duration</th><th>upload</th><th>download</th>
+	</tr>
+
+<?php
+$link = @da_sql_pconnect($config);
+if ($link){
+	$search = @da_sql_query($link,$config,
+	"SELECT * FROM $config[sql_total_accounting_table]
+	WHERE AcctDate >= '$start' AND AcctDate <= '$stop' $server_str
+	ORDER BY $order_attr $order $limit;");
+
+	if ($search){
+		while( $row = @da_sql_fetch_array($search,$config) ){
+			$num++;
+			$acct_login = $row[UserName];
+			if ($acct_login == '')
+				$acct_login = '-';
+			else
+				$acct_login = "<a href=\"user_admin.php3?login=$acct_login\" title=\"Edit user $acct_login\">$acct_login</a>";
+			$acct_time = $row[ConnTotDuration];
+			$acct_time = time2str($acct_time);
+			$acct_conn_num = $row[ConnNum];
+			$acct_date = $row[AcctDate];
+			$acct_upload = $row[InputOctets];
+			$acct_download = $row[OutputOctets];
+			$acct_upload = bytes2str($acct_upload);
+			$acct_download = bytes2str($acct_download);
+			$acct_server = $da_name_cache[$row[NASIPAddress]];
+			if (!isset($acct_server)){
+				$acct_server = gethostbyaddr($row[NASIPAddress]);
+				if (!isset($da_name_cache) && $config[general_use_session] == 'yes'){
+					$da_name_cache[$row[NASIPAddress]] = $acct_server;
+					session_register('da_name_cache');
+				}
+				else
+					$da_name_cache[$row[NASIPAddress]] = $acct_server;
+			}
+			if ($acct_server == '')
+				$acct_server = '-';
+			echo <<<EOM
+			<tr align=center bgcolor="white">
+				<td>$num</td>
+				<td>$acct_login</td>
+				<td>$acct_date</td>
+				<td>$acct_server</td>
+				<td>$acct_conn_num</td>
+				<td>$acct_time</td>
+				<td>$acct_upload</td>
+				<td>$acct_download</td>
+			</tr>
+EOM;
+		}
+	}
+}
+echo <<<EOM
+	</table>
+<tr><td>
+<hr>
+<tr><td align="left">
+	<form action="user_stats.php3" method="post" name="master">
+	<table border=0>
+		<tr valign="bottom">
+			<td><small><b>start time</td><td><small><b>stop time</td><td><small><b>pagesize</td><td><b>sort by</td><td><b>order</td>
+	<tr valign="middle"><td>
+<input type="hidden" name="show" value="0">
+<input type="text" name="start" size="11" value="$start"></td>
+<td><input type="text" name="stop" size="11" value="$stop"></td>
+<td><select name="pagesize">
+<option $selected[5] value="5" >05
+<option $selected[10] value="10">10
+<option $selected[15] value="15">15
+<option $selected[20] value="20">20
+<option $selected[40] value="40">40
+<option $selected[80] value="80">80
+<option $selected[all] value="all">all
+</select>
+</td>
+<td>
+<select name="sortby">
+<option $selected[num] value="num">connections number
+<option $selected[time] value="time">connections duration
+</select>
+</td>
+<td><select name="order">
+<option $selected[asc] value="asc">ascending
+<option $selected[desc] value="desc">descending
+</select>
+</td>
+EOM;
+?>
+
+<td><input type="submit" class=button value="show"></td></tr>
+<tr><td>
+<b>On Access Server:</b>
+</td></tr><tr><td>
+<select name="server">
+<?php
+while(1){
+	$i++;
+	$name = 'nas' . $i . '_name';
+	if ($config[$name] == ''){
+		$i--;
+		break;
+	}
+	$name_ip = 'nas' . $i . '_ip';
+	if ($server == $config[$name_ip])
+		echo "<option selected value=\"$config[$name_ip]\">$config[$name]\n";
+	else
+		echo "<option value=\"$config[$name_ip]\">$config[$name]\n";
+}
+if ($server == '' || $server == 'all')
+	echo "<option selected value=\"all\">all\n";
+?>
+</select>
+</td></tr>
+</table></td></tr></form>
+</table>
+</tr>
+</table>
+</body>
+</html>
diff --git a/html/buttons/default/buttons.html.php3 b/html/buttons/default/buttons.html.php3
index 8c438c0..4ed3dbb 100644
--- a/html/buttons/default/buttons.html.php3
+++ b/html/buttons/default/buttons.html.php3
@@ -42,6 +42,9 @@ if ($HTTP_SERVER_VARS["PHP_AUTH_USER"])
 	<tr align=left><td id="menu2" onmouseover='myin("2");' onmouseout='myout("2");'>
 	<a id="a2" href="stats.php3" target="content" title="Dialup Statistics">Statistics</a>
 	</td></tr>
+	<tr align=left><td id="menu17" onmouseover='myin("17");' onmouseout='myout("17");'>
+	<a id="a17" href="user_stats.php3" target="content" title="Show User Statistics">User Statistics</a>
+	</td></tr>
 	<tr align=left><td id="menu3" onmouseover='myin("3");' onmouseout='myout("3");'>
 	<a id="a3" href="user_finger.php3" target="content" title="Show Online Users">Online Users</a>
 	</td></tr>
diff --git a/sql/mtotacct.sql b/sql/mtotacct.sql
new file mode 100644
index 0000000..b504947
--- /dev/null
+++ b/sql/mtotacct.sql
@@ -0,0 +1,17 @@
+CREATE TABLE mtotacct (
+  MTotAcctId bigint(21) NOT NULL auto_increment,
+  UserName varchar(64) NOT NULL default '',
+  AcctDate date NOT NULL default '0000-00-00',
+  ConnNum bigint(12) default NULL,
+  ConnTotDuration bigint(12) default NULL,
+  ConnMaxDuration bigint(12) default NULL,
+  ConnMinDuration bigint(12) default NULL,
+  InputOctets bigint(12) default NULL,
+  OutputOctets bigint(12) default NULL,
+  NASIPAddress varchar(15) default NULL,
+  PRIMARY KEY  (MTotAcctId),
+  KEY UserName (UserName),
+  KEY AcctDate (AcctDate),
+  KEY UserOnDate (UserName,AcctDate),
+  KEY NASIPAddress (NASIPAddress)
+);
diff --git a/sql/totacct.sql b/sql/totacct.sql
new file mode 100644
index 0000000..e1cb448
--- /dev/null
+++ b/sql/totacct.sql
@@ -0,0 +1,18 @@
+CREATE TABLE totacct (
+  TotAcctId bigint(21) NOT NULL auto_increment,
+  UserName varchar(64) NOT NULL default '',
+  AcctDate date NOT NULL default '0000-00-00',
+  ConnNum bigint(12) default NULL,
+  ConnTotDuration bigint(12) default NULL,
+  ConnMaxDuration bigint(12) default NULL,
+  ConnMinDuration bigint(12) default NULL,
+  InputOctets bigint(12) default NULL,
+  OutputOctets bigint(12) default NULL,
+  NASIPAddress varchar(15) default NULL,
+  PRIMARY KEY  (TotAcctId),
+  KEY UserName (UserName),
+  KEY AcctDate (AcctDate),
+  KEY UserOnDate (UserName,AcctDate),
+  KEY NASIPAddress (NASIPAddress),
+  KEY NASIPAddressOnDate (AcctDate,NASIPAddress)
+);
-- 
2.39.5