changeset 533:4cdf146f11d5

Added a set of example PHP scripts to parse the app_acct.fdx data.
author Sebastien Decugis <sdecugis@nict.go.jp>
date Tue, 07 Sep 2010 16:57:48 +0900
parents 4cb8f63a0f67
children 6af365b6f955
files contrib/app_acct_tools/README contrib/app_acct_tools/app_acct.conf contrib/app_acct_tools/database.sql contrib/app_acct_tools/display_results.php contrib/app_acct_tools/display_self.php contrib/app_acct_tools/display_stats.php contrib/app_acct_tools/process_records.php contrib/app_acct_tools/purge_to_file.php doc/app_acct.conf.sample
diffstat 9 files changed, 1255 insertions(+), 128 deletions(-) [+]
line wrap: on
line diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/contrib/app_acct_tools/README	Tue Sep 07 16:57:48 2010 +0900
@@ -0,0 +1,47 @@
+This folder contains several tools to use and parse the data from the app_acct.fdx extension.
+
+- database.sql :
+    An example database format for use with the scripts in this folder.
+    
+- app_acct.conf :
+    The part of app_acct.conf that is relevant to this database schema.
+
+- purge_to_file.php : 
+    This PHP script is used to take the records from the incoming table (stored by app_acct.fdx 
+  extension) and save these records in a file in SQL format. This is similar to pg_dump 
+  command, except that all the records that have been saved in the file are removed from 
+  the table. This can be used in cron jobs for example to maintain a reasonable size of 
+  the incoming table and move the data to another host for off-line processing. It can 
+  also be useful to aggregate the data from different hosts, if you are load-balancing your
+  accounting servers for example (granted that all app_acct.fdx use identical table format
+  on all the servers). See the top of the file for configuration parameters.
+    
+- process_records.php :
+    This PHP script processes the records pertaining to users sessions, as follow:
+  * when a session is complete (STOP record received), it stores a session summary
+  into the processed records table (see process_database.sql file for format). 
+  * It optionaly archives the processed records into a different table, before deleting them.
+  * It can also move records of unterminated sessions that are older than a configurable time 
+  to an orphan_records table, so that they are not re-processed every time. 
+  This orphans table must have the same structure as the "incoming" table.
+  
+- display_results.php, display_self.php, display_stats.php :
+   These scripts give a few examples of how to display the processed data.
+  
+USAGE:
+ *) Initial: create your database using database.sql file
+ *) Configure the app_acct.fdx extension using tips from app_acct.conf
+
+  The following processing can be run for example as cron jobs.
+ 1) On each accounting server for the realm, configure the app_acct.fdx extension to
+   dump the records in a local database (all servers must use the same database format).
+   The table would typically be "incoming".
+ 2) Run the purge_to_file.php script on each server regularly, then move the generated
+   files onto a single server for processing. This server only needs the other tables.
+ 3) Add the data from the files into the database in this server by running:
+   psql < file.sql
+    Each file that has been added should then be archived and removed so that it is not 
+   re-added later.
+ 4) Run the process_records.php script on this processing server. Now, the database
+   contains the aggregated data that can be visualized with display_*.php scripts.
+   
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/contrib/app_acct_tools/app_acct.conf	Tue Sep 07 16:57:48 2010 +0900
@@ -0,0 +1,68 @@
+
+# This is the configuration for use with the database created by 'database.sql' file.
+# One should take care of configuring the ConnInfo properly.
+ConnInfo = "";
+
+# The table and special fields names:
+Table = "incoming";
+Timestamp_field = "recorded_on";
+Server_name_field = "recorded_serv";
+
+# The AVPs that are saved in the table:
+"Origin-Host";
+"Origin-Realm";
+"Destination-Realm";
+"Destination-Host";
+"Session-Id";
+"Origin-State-Id"; 
+"Accounting-Record-Type";
+"Accounting-Record-Number";
+"User-Name";
+"Event-Timestamp"; 
+"Acct-Application-Id";
+"Accounting-Sub-Session-Id";
+"Acct-Session-Id";
+"Acct-Multi-Session-Id";
+"Origin-AAA-Protocol";
+"Acct-Delay-Time"; 
+"NAS-Identifier";
+"NAS-IP-Address";
+"NAS-IPv6-Address";
+"NAS-Port";
+"NAS-Port-Id";
+"NAS-Port-Type";
+"Service-Type";
+"Termination-Cause"; 
+"Accounting-Input-Octets";
+"Accounting-Input-Packets";
+"Accounting-Output-Octets";
+"Accounting-Output-Packets";
+"Acct-Authentic";
+"Acct-Link-Count";
+"Acct-Session-Time"; 
+"Acct-Tunnel-Connection";
+"Acct-Tunnel-Packets-Lost"; 
+"Callback-Id";
+"Callback-Number";
+"Called-Station-Id";
+"Calling-Station-Id"; 
+"Connect-Info";
+"Originating-Line-Info"; 
+"Authorization-Lifetime";
+"Session-Timeout";
+"Idle-Timeout";
+"Port-Limit";
+"Accounting-Realtime-Required"; 
+"Acct-Interim-Interval";
+"Filter-Id";
+"NAS-Filter-Rule";
+"QoS-Filter-Rule"; 
+"Login-IP-Host";
+"Login-IPv6-Host";
+"Login-LAT-Group";
+"Login-LAT-Node";
+"Login-LAT-Port"; 
+"Login-LAT-Service";
+"Login-Service";
+"Login-TCP-Port"; 
+"Route-Record" = { multi=5; };
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/contrib/app_acct_tools/database.sql	Tue Sep 07 16:57:48 2010 +0900
@@ -0,0 +1,227 @@
+-- database.sql
+-- Script to create the tables for process_records.php script to perform.
+-- IMPORTANT NOTE: No constraint and almost no index are created by this script. 
+-- One should consider adding the appropriate indexes according to their utilization of the data.
+
+-- Incoming table table.
+--  This table is used by app_acct.fdx to store the Accounting records.
+CREATE TABLE incoming (
+    "Origin-Host" bytea NOT NULL,
+    "Origin-Realm" bytea NOT NULL,
+    "Destination-Realm" bytea,
+    "Destination-Host" bytea,
+    "Session-Id" bytea NOT NULL,
+    "Origin-State-Id" integer,
+    "Accounting-Record-Type" integer NOT NULL,
+    "Accounting-Record-Number" integer NOT NULL,
+    "User-Name" bytea,
+    "Event-Timestamp" bytea,
+    "Acct-Application-Id" integer,
+    "Accounting-Sub-Session-Id" bigint,
+    "Acct-Session-Id" bytea,
+    "Acct-Multi-Session-Id" bytea,
+    "Origin-AAA-Protocol" integer,
+    "Acct-Delay-Time" integer,
+    "NAS-Identifier" bytea,
+    "NAS-IP-Address" bytea,
+    "NAS-IPv6-Address" bytea,
+    "NAS-Port" integer,
+    "NAS-Port-Id" bytea,
+    "NAS-Port-Type" integer,
+    "Service-Type" integer,
+    "Termination-Cause" integer,
+    "Accounting-Input-Octets" bigint,
+    "Accounting-Input-Packets" bigint,
+    "Accounting-Output-Octets" bigint,
+    "Accounting-Output-Packets" bigint,
+    "Acct-Authentic" integer,
+    "Acct-Link-Count" integer,
+    "Acct-Session-Time" integer,
+    "Acct-Tunnel-Connection" bytea,
+    "Acct-Tunnel-Packets-Lost" integer,
+    "Callback-Id" bytea,
+    "Callback-Number" bytea,
+    "Called-Station-Id" bytea,
+    "Calling-Station-Id" bytea,
+    "Connect-Info" bytea,
+    "Originating-Line-Info" bytea,
+    "Authorization-Lifetime" integer,
+    "Session-Timeout" integer,
+    "Idle-Timeout" integer,
+    "Port-Limit" integer,
+    "Accounting-Realtime-Required" integer,
+    "Acct-Interim-Interval" integer,
+    "Filter-Id" bytea,
+    "NAS-Filter-Rule" bytea,
+    "QoS-Filter-Rule" bytea,
+    "Login-IP-Host" bytea,
+    "Login-IPv6-Host" bytea,
+    "Login-LAT-Group" bytea,
+    "Login-LAT-Node" bytea,
+    "Login-LAT-Port" bytea,
+    "Login-LAT-Service" bytea,
+    "Login-Service" integer,
+    "Login-TCP-Port" integer,
+    "Route-Record1" bytea,
+    "Route-Record2" bytea,
+    "Route-Record3" bytea,
+    "Route-Record4" bytea,
+    "Route-Record5" bytea,
+    "recorded_on" timestamp with time zone NOT NULL,
+    "recorded_serv" bytea
+ );
+
+
+-- Accounting Data.
+--  This is is the processed data that is used also by display_results.php
+CREATE TABLE processed (
+    user_name bytea,
+    user_device bytea,
+    nas_info bytea,
+    sess_start timestamp with time zone,
+    sess_duration interval,
+    downl_bytes bigint,
+    upl_bytes bigint,
+    downl_packets bigint,
+    upl_packets bigint
+ );
+CREATE INDEX un_index ON processed (user_name);
+
+
+
+-- Orphans table.
+--  This is optional, and it must match the structure of your incoming table.
+CREATE TABLE orphans (
+    "Origin-Host" bytea NOT NULL,
+    "Origin-Realm" bytea NOT NULL,
+    "Destination-Realm" bytea,
+    "Destination-Host" bytea,
+    "Session-Id" bytea NOT NULL,
+    "Origin-State-Id" integer,
+    "Accounting-Record-Type" integer NOT NULL,
+    "Accounting-Record-Number" integer NOT NULL,
+    "User-Name" bytea,
+    "Event-Timestamp" bytea,
+    "Acct-Application-Id" integer,
+    "Accounting-Sub-Session-Id" bigint,
+    "Acct-Session-Id" bytea,
+    "Acct-Multi-Session-Id" bytea,
+    "Origin-AAA-Protocol" integer,
+    "Acct-Delay-Time" integer,
+    "NAS-Identifier" bytea,
+    "NAS-IP-Address" bytea,
+    "NAS-IPv6-Address" bytea,
+    "NAS-Port" integer,
+    "NAS-Port-Id" bytea,
+    "NAS-Port-Type" integer,
+    "Service-Type" integer,
+    "Termination-Cause" integer,
+    "Accounting-Input-Octets" bigint,
+    "Accounting-Input-Packets" bigint,
+    "Accounting-Output-Octets" bigint,
+    "Accounting-Output-Packets" bigint,
+    "Acct-Authentic" integer,
+    "Acct-Link-Count" integer,
+    "Acct-Session-Time" integer,
+    "Acct-Tunnel-Connection" bytea,
+    "Acct-Tunnel-Packets-Lost" integer,
+    "Callback-Id" bytea,
+    "Callback-Number" bytea,
+    "Called-Station-Id" bytea,
+    "Calling-Station-Id" bytea,
+    "Connect-Info" bytea,
+    "Originating-Line-Info" bytea,
+    "Authorization-Lifetime" integer,
+    "Session-Timeout" integer,
+    "Idle-Timeout" integer,
+    "Port-Limit" integer,
+    "Accounting-Realtime-Required" integer,
+    "Acct-Interim-Interval" integer,
+    "Filter-Id" bytea,
+    "NAS-Filter-Rule" bytea,
+    "QoS-Filter-Rule" bytea,
+    "Login-IP-Host" bytea,
+    "Login-IPv6-Host" bytea,
+    "Login-LAT-Group" bytea,
+    "Login-LAT-Node" bytea,
+    "Login-LAT-Port" bytea,
+    "Login-LAT-Service" bytea,
+    "Login-Service" integer,
+    "Login-TCP-Port" integer,
+    "Route-Record1" bytea,
+    "Route-Record2" bytea,
+    "Route-Record3" bytea,
+    "Route-Record4" bytea,
+    "Route-Record5" bytea,
+    "recorded_on" timestamp with time zone NOT NULL,
+    "recorded_serv" bytea
+ );
+
+-- Archives table.
+--  This is also optional, and it must match the structure of your incoming table.
+CREATE TABLE archived (
+    "Origin-Host" bytea NOT NULL,
+    "Origin-Realm" bytea NOT NULL,
+    "Destination-Realm" bytea,
+    "Destination-Host" bytea,
+    "Session-Id" bytea NOT NULL,
+    "Origin-State-Id" integer,
+    "Accounting-Record-Type" integer NOT NULL,
+    "Accounting-Record-Number" integer NOT NULL,
+    "User-Name" bytea,
+    "Event-Timestamp" bytea,
+    "Acct-Application-Id" integer,
+    "Accounting-Sub-Session-Id" bigint,
+    "Acct-Session-Id" bytea,
+    "Acct-Multi-Session-Id" bytea,
+    "Origin-AAA-Protocol" integer,
+    "Acct-Delay-Time" integer,
+    "NAS-Identifier" bytea,
+    "NAS-IP-Address" bytea,
+    "NAS-IPv6-Address" bytea,
+    "NAS-Port" integer,
+    "NAS-Port-Id" bytea,
+    "NAS-Port-Type" integer,
+    "Service-Type" integer,
+    "Termination-Cause" integer,
+    "Accounting-Input-Octets" bigint,
+    "Accounting-Input-Packets" bigint,
+    "Accounting-Output-Octets" bigint,
+    "Accounting-Output-Packets" bigint,
+    "Acct-Authentic" integer,
+    "Acct-Link-Count" integer,
+    "Acct-Session-Time" integer,
+    "Acct-Tunnel-Connection" bytea,
+    "Acct-Tunnel-Packets-Lost" integer,
+    "Callback-Id" bytea,
+    "Callback-Number" bytea,
+    "Called-Station-Id" bytea,
+    "Calling-Station-Id" bytea,
+    "Connect-Info" bytea,
+    "Originating-Line-Info" bytea,
+    "Authorization-Lifetime" integer,
+    "Session-Timeout" integer,
+    "Idle-Timeout" integer,
+    "Port-Limit" integer,
+    "Accounting-Realtime-Required" integer,
+    "Acct-Interim-Interval" integer,
+    "Filter-Id" bytea,
+    "NAS-Filter-Rule" bytea,
+    "QoS-Filter-Rule" bytea,
+    "Login-IP-Host" bytea,
+    "Login-IPv6-Host" bytea,
+    "Login-LAT-Group" bytea,
+    "Login-LAT-Node" bytea,
+    "Login-LAT-Port" bytea,
+    "Login-LAT-Service" bytea,
+    "Login-Service" integer,
+    "Login-TCP-Port" integer,
+    "Route-Record1" bytea,
+    "Route-Record2" bytea,
+    "Route-Record3" bytea,
+    "Route-Record4" bytea,
+    "Route-Record5" bytea,
+    "recorded_on" timestamp with time zone NOT NULL,
+    "recorded_serv" bytea
+ );
+
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/contrib/app_acct_tools/display_results.php	Tue Sep 07 16:57:48 2010 +0900
@@ -0,0 +1,141 @@
+<?php
+
+# The copyright of this file is the same as the freeDiameter project. Licence is BSD.
+
+# This file should no be called directly; 
+# instead it should be included from another script that sets its parameters as described bellow.
+
+## $USERS  
+# An array of the user names to display; the empty array will display all users.
+# This parameter MUST be set.
+if (!isset($USERS))
+	die('Do not call this file directly');
+
+## $START_TIME:
+# If set, this restricts the displayed data to sessions starting after $START
+
+## $END_TIME:
+# If set, this restricts the displayed data to sessions starting before $END
+
+## $LIMIT:
+## $LIMIT_OFFSET:
+# If set, these limit the number of accounting records displayed (for pagination purpose)
+
+#------------------------------------------------------------------------------------------
+# DATABASE:
+
+/* The Connection String used to access that database:
+  Example: "host=localhost dbname=app_acct user=freediameter password=foo" */
+$CONNSTR=""; 
+
+/* The name of the table containing the processed data (from process_records.php script) */
+$PROCESSED="processed";
+
+#------------------------------------------------------------------------------------------
+
+
+/* Connect to the database */
+$dbconn = pg_connect($CONNSTR)
+    or die('Could not connect: ' . pg_last_error() . "\n");
+    
+/* Function to format download size (from php.net) */
+function human_readable( $size )
+{
+   $count = 0;
+   $format = array("B","KB","MB","GB","TB","PB","EB","ZB","YB");
+   while(($size/1024)>1 && $count<8)
+   {
+       $size=$size/1024;
+       $count++;
+   }
+   if( $size >= 100 ) $decimals = 0;
+   elseif ($size >= 10 ) $decimals = 1;
+   else  $decimals = 2;
+   $return = number_format($size,$decimals,'.',' ')." ".$format[$count];
+   return $return;
+}
+    
+/* Build the SQL query */
+$sql = 'SELECT *, to_char(sess_start, \'YYYY-MM-DD&nbsp;HH24:MI:SS&nbsp;(TZ)\') as fmt_sess_start FROM "'.$PROCESSED.'"';
+$where=0;
+if ($USERS) {
+	$USERS = array_map(pg_escape_bytea, $USERS);
+	$sql .= " WHERE user_name IN ('". join("', '", array_values($USERS))."') ";
+	$where = 1;
+}
+
+if ($START_TIME) {
+	$START_TIME = pg_escape_string($START_TIME);
+	if ($where++)
+		$sql .= " AND ";
+	else
+		$sql .= " WHERE ";
+	$sql .= "sess_start >= '".$START_TIME."'";
+}
+if ($END_TIME) {
+	$END_TIME = pg_escape_string($END_TIME);
+	if ($where++)
+		$sql .= " AND ";
+	else
+		$sql .= " WHERE ";
+	$sql .= "sess_start <= '".$END_TIME."'";
+}
+
+$sql .= " ORDER BY sess_start, sess_duration";
+
+if ($LIMIT)
+	$sql .= " LIMIT $LIMIT";
+if ($LIMIT_OFFSET)
+	$sql .= " OFFSET $LIMIT_OFFSET";
+
+/* Execute the query */
+$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
+$recs = pg_num_rows($result);
+if ($recs == 0) {
+	echo "<p><em>Sorry, no data is available in this selection.</em></p>\n";
+} else {
+	echo "<p><strong>$recs</strong> records found.</p>\n";
+?>
+  <table>
+    <tr>
+      <th>Device identifier</th>
+      <th>Access Device information</th>
+      <th>Session started on</th>
+      <th>Duration</th>
+      <th>Downloaded</th>
+      <th>Uploaded</th>
+    </tr>
+<?php
+	while ($record = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+		echo "    <tr title='".htmlentities(pg_unescape_bytea($record["user_name"]))."'>\n";
+		echo "      <td>";
+		echo htmlentities(pg_unescape_bytea($record["user_device"]));
+		echo "</td>\n";
+		echo "      <td>";
+		echo htmlentities(pg_unescape_bytea($record["nas_info"]));
+		echo "</td>\n";
+		echo "      <td>";
+		echo $record["fmt_sess_start"];
+		echo "</td>\n";
+		echo "      <td>";
+		echo htmlentities($record["sess_duration"]);
+		echo "</td>\n";
+		echo "      <td>";
+		echo human_readable( $record["downl_bytes"] )." (".$record["downl_packets"]."pckts)";
+		echo "</td>\n";
+		echo "      <td>";
+		echo human_readable( $record["upl_bytes"] )." (".$record["upl_packets"]."pckts)";
+		echo "</td>\n";
+		echo "    </tr>\n";
+	
+	}
+}
+pg_free_result($result);
+
+
+/* Closing connection */
+pg_close($dbconn);
+
+
+
+?>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/contrib/app_acct_tools/display_self.php	Tue Sep 07 16:57:48 2010 +0900
@@ -0,0 +1,139 @@
+<?php
+
+# This file is an example wrapper around display_results.php.
+
+# It relies on clients authentication based on certificate usage 
+# (it must be the same certificate as used during AAA access, so that the User-Name matches).
+# See your web server documentation for details.
+# Example for apache2:
+#  (+ detail in http://httpd.apache.org/docs/2.0/ssl/ssl_howto.html#allclients )
+# - in vhost definition file, refence the CA chain of your users certificates:
+#  SSLCACertificateFile /var/www/conf/ssl.crt/ca.crt
+# - in vhost file or .htaccess file (adjust Depth to your setup):
+#  <IfModule mod_ssl.c>
+#  SSLVerifyClient require
+#  SSLVerifyDepth 2
+#  </IfModule>
+
+/* Check the client is correctly SSL authenticated with his server */
+if (!isset($_SERVER["SSL_CLIENT_VERIFY"]) || $_SERVER["SSL_CLIENT_VERIFY"] != "SUCCESS")
+	die("SSL authentication failed, the webserver is probably not configured correctly.\n");
+	
+/* Force some parameters to integer values */
+if ($_GET["t_limit"])
+	$_GET["t_limit"] = (int) $_GET["t_limit"];
+if ($_GET["t_offset"])
+	$_GET["t_offset"] = (int) $_GET["t_offset"];
+	
+/* Default form values */
+if (!isset($_GET["Submit"])) {
+	$_GET["t_limit"] = 50;
+	$_GET["c_limit"] = 1;
+	$_GET["t_offset"] = 0;
+}
+
+/* Output the form */
+?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
+<html lang="en-US">
+<head>
+    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
+
+    <title>Accounting Data</title>
+    <meta name="description" content="This page gives you access to your personal accounting data.">
+    <meta name="keywords" content="accounting">
+
+    <style type="text/css">
+        body { text-align:center; }
+	table { border-collapse:collapse; margin-left:auto; margin-right:auto; }
+	table, td, th { border:1px solid green; padding-left:.5em; padding-right:.5em;}
+	th { background-color:green; color:white; }
+    </style>
+</head>
+
+<body >
+ <h1>Accounting data</h1>
+  <p>Note well: this page displays only data about <em>terminated</em> sessions.</p>
+  <form method="GET">
+  <table>
+    <tr>
+      <th colspan="3">
+        Filtering parameters
+      </th>
+    </tr>
+    <tr>
+      <td><input type="checkbox" name="c_starttime"<?php if (isset($_GET["c_starttime"])) echo " checked"; ?>></td>
+      <td>Show only sessions starting from (<a href="http://www.postgresql.org/docs/8.4/static/datatype-datetime.html">YYYY-MM-DD HH:MM:SS</a>):</td>
+      <td><input type="text" name="t_starttime"<?php if (isset($_GET["t_starttime"])) echo 'value="'.$_GET["t_starttime"].'"'; ?>></td>
+    </tr>
+    <tr>
+      <td><input type="checkbox" name="c_endtime"<?php if (isset($_GET["c_endtime"])) echo " checked"; ?>></td>
+      <td>Show only sessions starting until (<a href="http://www.postgresql.org/docs/8.4/static/datatype-datetime.html">YYYY-MM-DD HH:MM:SS</a>):</td>
+      <td><input type="text" name="t_endtime"<?php if (isset($_GET["t_endtime"])) echo 'value="'.$_GET["t_endtime"].'"'; ?>></td>
+    </tr>
+    <tr>
+      <td><input type="checkbox" name="c_limit"<?php if (isset($_GET["c_limit"])) echo " checked"; ?>></td>
+      <td>Show only this number of records:</td>
+      <td><input type="text" name="t_limit"<?php if (isset($_GET["t_limit"])) echo 'value="'.$_GET["t_limit"].'"'; ?>></td>
+    </tr>
+    <tr>
+      <td><input type="checkbox" name="c_offset"<?php if (isset($_GET["c_offset"])) echo " checked"; ?>></td>
+      <td>Starting from record:</td>
+      <td><input type="text" name="t_offset"<?php if (isset($_GET["t_offset"])) echo 'value="'.$_GET["t_offset"].'"'; ?>></td>
+    </tr>
+    <tr>
+      <th colspan="3">
+        Apply this filter: <input type="submit" name="Submit">
+      </th>
+    </tr>
+  </table>
+  </form>
+  
+<p>
+   Currently displaying user <em><?php echo htmlentities($_SERVER["SSL_CLIENT_S_DN_CN"]); ?></em><?php
+
+/* Search user by CN or Email since some OS use the later during EAP-TLS authentication */
+$USERS = array($_SERVER["SSL_CLIENT_S_DN_CN"], $_SERVER["SSL_CLIENT_S_DN_Email"]);
+
+/* If the start time boundary was specified... */
+if ($_GET["c_starttime"] && $_GET["t_starttime"]) {
+	$START_TIME=$_GET["t_starttime"];
+}
+if ($_GET["c_endtime"] && $_GET["t_endtime"]) {
+	$END_TIME=$_GET["t_endtime"];
+}
+
+/* idem with end time */
+if ($START_TIME && $END_TIME) {
+	echo ", sessions starting between $START_TIME and $END_TIME";
+} elseif ($START_TIME) {
+	echo ", sessions starting after $START_TIME";
+} elseif ($END_TIME) {
+	echo ", sessions starting before $END_TIME";
+}
+
+/* Pagination */
+if ($_GET["c_limit"] && $_GET["t_limit"]) {
+	$LIMIT=$_GET["t_limit"];
+}
+if ($_GET["c_offset"] && $_GET["t_offset"]) {
+	$LIMIT_OFFSET=$_GET["t_offset"];
+}
+if ($LIMIT) {
+	echo ", limited to ".$LIMIT." records";
+	if ($LIMIT_OFFSET)
+		echo " starting at ".$LIMIT_OFFSET;
+} else if ($LIMIT_OFFSET) {
+	echo " starting at record ".$LIMIT_OFFSET;
+}
+echo ".\n";
+?>
+</p>
+
+<?php
+/* This file will generate the array of data matching the selection */
+require("display_results.php");
+
+?>
+</body>
+</html>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/contrib/app_acct_tools/display_stats.php	Tue Sep 07 16:57:48 2010 +0900
@@ -0,0 +1,269 @@
+<?php
+
+# The copyright of this file is the same as the freeDiameter project. Licence is BSD.
+
+#------------------------------------------------------------------------------------------
+# DATABASE:
+
+/* The Connection String used to access that database:
+  Example: "host=localhost dbname=app_acct user=freediameter password=foo" */
+$CONNSTR=""; 
+
+/* The name of the table containing the processed data (from process_records.php script) */
+$PROCESSED="processed";
+
+#------------------------------------------------------------------------------------------
+
+?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
+<html lang="en-US">
+<head>
+    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
+
+    <title>Accounting Data</title>
+    <meta name="description" content="This page gives you access to your personal accounting data.">
+    <meta name="keywords" content="accounting">
+
+    <style type="text/css">
+        body { text-align:center; }
+	table.colored { border-collapse:collapse; margin-left:auto; margin-right:auto; }
+	table.colored td, table.colored th { border:1px solid green; padding-left:.5em; padding-right:.5em;}
+	.colored th { background-color:green; color:white; }
+	hr { width:15px; margin-top:2em;margin-bottom:2em; }
+    </style>
+</head>
+
+<body >
+ <h1>Statistical accounting data</h1>
+  <form method="GET">
+  <table class="colored">
+    <tr>
+      <th colspan="3">
+        Filtering options
+      </th>
+    </tr>
+    <tr>
+      <td><input type="checkbox" name="c_starttime"<?php if (isset($_GET["c_starttime"])) echo " checked"; ?>></td>
+      <td>Limit to sessions starting from (<a href="http://www.postgresql.org/docs/8.4/static/datatype-datetime.html">hint</a>):</td>
+      <td><input type="text" title="YYYY-MM-DD HH:MM:SS" name="t_starttime"<?php if (isset($_GET["t_starttime"])) echo 'value="'.$_GET["t_starttime"].'"'; ?>></td>
+    </tr>
+    <tr>
+      <td><input type="checkbox" name="c_endtime"<?php if (isset($_GET["c_endtime"])) echo " checked"; ?>></td>
+      <td>Limit to sessions starting until (<a href="http://www.postgresql.org/docs/8.4/static/datatype-datetime.html">hint</a>):</td>
+      <td><input type="text" title="YYYY-MM-DD HH:MM:SS" name="t_endtime"<?php if (isset($_GET["t_endtime"])) echo 'value="'.$_GET["t_endtime"].'"'; ?>></td>
+    </tr>
+    <tr>
+      <th colspan="3">
+        Apply this filter: <input type="submit" name="Submit">
+      </th>
+    </tr>
+  </table>
+  </form>
+  <p>Note well: this page displays only data about <em>terminated</em> sessions.</p>
+<?php
+
+/* Connect to the database */
+$dbconn = pg_connect($CONNSTR)
+    or die('Could not connect: ' . pg_last_error() . "\n");
+    
+if ($_GET["c_starttime"] && $_GET["t_starttime"]) {
+	$START_TIME=$_GET["t_starttime"];
+}
+if ($_GET["c_endtime"] && $_GET["t_endtime"]) {
+	$END_TIME=$_GET["t_endtime"];
+}
+$sql_cond="";
+if ($START_TIME) {
+	$sql_cond = "sess_start >= '".pg_escape_string($START_TIME)."'";
+}
+if ($END_TIME) {
+	if ($sql_cond)
+		$sql_cond .= " AND ";
+	$sql_cond .= "sess_start =< '".pg_escape_string($END_TIME)."'";
+}
+
+
+/* Function to format download size (from php.net) */
+function human_readable( $size )
+{
+   $count = 0;
+   $format = array("B","KB","MB","GB","TB","PB","EB","ZB","YB");
+   while(($size/1024)>1 && $count<8)
+   {
+       $size=$size/1024;
+       $count++;
+   }
+   if( $size >= 100 ) $decimals = 0;
+   elseif ($size >= 10 ) $decimals = 1;
+   else  $decimals = 2;
+   $return = number_format($size,$decimals,'.',' ')." ".$format[$count];
+   return $return;
+}
+
+?>
+  
+  <hr />
+  
+<?php
+    
+/* First query: global data */
+$sql = 'SELECT SUM(downl_bytes) as total_down, SUM(upl_bytes) as total_up,';
+$sql .= ' to_char(min(sess_start), \'YYYY-MM-DD&nbsp;HH24:MI:SS&nbsp;TZ\') as first_sess,';
+$sql .= ' to_char(max(sess_start), \'YYYY-MM-DD&nbsp;HH24:MI:SS&nbsp;TZ\') as last_sess,';
+$sql .= ' count(distinct user_name) as nb_users,';
+$sql .= ' count(distinct nas_info) as nb_ap';
+$sql .= ' FROM "'.$PROCESSED.'"';
+if ($sql_cond)
+	$sql .= ' WHERE '.$sql_cond;
+
+/* Execute the query */
+$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
+if (pg_num_rows($result) == 0) {
+	echo "<p><em>No data is available in the selected period.</em></p>\n";
+	die("</body></html>\n");
+} 
+
+$data = pg_fetch_array($result, null, PGSQL_ASSOC);
+?>
+  <table class="colored">
+    <tr>
+      <th colspan="2">Total accounted data</th>
+    </tr>
+<?php
+echo "    <tr><td>First session started:</td><td>".$data["first_sess"]."</td></tr>\n";
+echo "    <tr><td>Last session started:</td><td>".$data["last_sess"]."</td></tr>\n";
+echo "    <tr><td>Total data downloaded:</td><td><strong>".human_readable($data["total_down"])."</strong></td></tr>\n";
+echo "    <tr><td>Total data uploaded:</td><td><strong>".human_readable($data["total_up"])."</strong></td></tr>\n";
+echo "    <tr><td>Number of users who connected at least once:</td><td>".$data["nb_users"]."</td></tr>\n";
+echo "    <tr><td>Number of access points involved:</td><td>".$data["nb_ap"]."</td></tr>\n";
+echo "  </tr>\n";
+echo " </table>\n";
+
+pg_free_result($result);
+
+?>
+  
+  <hr />
+  
+  <table class="colored">
+    <tr>
+      <th colspan="2">Top-5 <strong>downloading</strong> users</th>
+    </tr>
+<?php
+    
+/* Now, get the top 5 downloaders */
+$sql = 'SELECT SUM(downl_bytes) as total_down, user_name';
+$sql .= ' FROM "'.$PROCESSED.'"';
+if ($sql_cond)
+	$sql .= ' WHERE '.$sql_cond;
+$sql .= ' GROUP BY user_name';
+$sql .= ' ORDER BY total_down desc, user_name';
+$sql .= ' LIMIT 5';
+
+/* Execute the query */
+$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
+while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+	echo "    <tr>\n";
+	echo "      <td>".htmlentities(pg_unescape_bytea($data["user_name"]))."</td>\n";
+	echo "      <td>".human_readable($data["total_down"])."</td>\n";
+	echo "    </tr>\n";
+}
+pg_free_result($result);
+?>
+  </table>
+  
+  <hr />
+  
+  <table class="colored">
+    <tr>
+      <th colspan="2">Top-5 <strong>uploading</strong> users</th>
+    </tr>
+<?php
+    
+/* Now, get the top 5 downloaders */
+$sql = 'SELECT SUM(upl_bytes) as total_up, user_name';
+$sql .= ' FROM "'.$PROCESSED.'"';
+if ($sql_cond)
+	$sql .= ' WHERE '.$sql_cond;
+$sql .= ' GROUP BY user_name';
+$sql .= ' ORDER BY total_up desc, user_name';
+$sql .= ' LIMIT 5';
+
+/* Execute the query */
+$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
+while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+	echo "    <tr>\n";
+	echo "      <td>".htmlentities(pg_unescape_bytea($data["user_name"]))."</td>\n";
+	echo "      <td>".human_readable($data["total_up"])."</td>\n";
+	echo "    </tr>\n";
+}
+pg_free_result($result);
+?>
+  </table>
+
+  <hr />
+  
+  <table class="colored">
+    <tr>
+      <th colspan="2">Top-8 access devices (by number of users)</th>
+    </tr>
+<?php
+    
+/* Now, get the top 5 downloaders */
+$sql = 'SELECT count(distinct user_name) as unc, nas_info';
+$sql .= ' FROM "'.$PROCESSED.'"';
+if ($sql_cond)
+	$sql .= ' WHERE '.$sql_cond;
+$sql .= ' GROUP BY nas_info';
+$sql .= ' ORDER BY unc desc, nas_info';
+$sql .= ' LIMIT 8';
+
+/* Execute the query */
+$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
+while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+	echo "    <tr>\n";
+	echo "      <td>".htmlentities(pg_unescape_bytea($data["nas_info"]))."</td>\n";
+	echo "      <td>".$data["unc"]."</td>\n";
+	echo "    </tr>\n";
+}
+pg_free_result($result);
+?>
+  </table>
+
+  <hr />
+  
+  <table class="colored">
+    <tr>
+      <th colspan="2">Top-8 access devices (by traffic: up+down)</th>
+    </tr>
+<?php
+    
+/* Now, get the top 5 downloaders */
+$sql = 'SELECT SUM(upl_bytes) + SUM(downl_bytes) as traffic, nas_info';
+$sql .= ' FROM "'.$PROCESSED.'"';
+if ($sql_cond)
+	$sql .= ' WHERE '.$sql_cond;
+$sql .= ' GROUP BY nas_info';
+$sql .= ' ORDER BY traffic desc, nas_info';
+$sql .= ' LIMIT 8';
+
+/* Execute the query */
+$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
+while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+	echo "    <tr>\n";
+	echo "      <td>".htmlentities(pg_unescape_bytea($data["nas_info"]))."</td>\n";
+	echo "      <td>".human_readable($data["traffic"])."</td>\n";
+	echo "    </tr>\n";
+}
+pg_free_result($result);
+?>
+  </table>
+
+  <hr />
+<?php
+/* Closing connection */
+pg_close($dbconn);
+?>
+<p><small>Data generated by scripts from the <a href="http://www.freediameter.net">freeDiameter</a> project.</small></p>
+</body>
+</html>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/contrib/app_acct_tools/process_records.php	Tue Sep 07 16:57:48 2010 +0900
@@ -0,0 +1,205 @@
+<?php
+# This file may be called either from CLI or web PHP installation.
+# The copyright is the same as the freeDiameter project. Licence is BSD.
+
+# IMPORTANT: This script is highly experimental, PLEASE KEEP A COPY OF YOUR ACCOUNTING DATA 
+# if this data has any importance.
+
+/*-------------------------------------------------------*/
+
+/* This script supports only one database where all tables are kept. 
+  The Connection String used to access that database:
+  Example: "host=localhost dbname=app_acct user=freediameter password=foo" */
+$CONNSTR=""; 
+
+
+/**** 1 : Incoming records (output of app_acct.fdx) ****/
+/* The name of the table were the raw records are saved (from app_acct.conf) */
+$INCOMING="incoming";
+
+/* Note: For this script, this table MUST contain the following fields (with these names): 
+ Session-Id, User-Name, Accounting-Record-Type, Accounting-Record-Number, Acct-Session-Id, 
+ Accounting-{In,Out}put-{Octets,Packets},
+ NAS-Identifier, Called-Station-Id, Calling-Station-Id, recorded_on.
+ */
+
+
+/**** 2 : Processed records (output of this script, input for display_results.php) ****/
+/* The name of the table */
+$PROCESSED="processed";
+
+/* See process_database.sql for command to create this database */
+
+/**** 3 : Orphan records (optional) ****/
+/* The script can move records belonging to an unterminated session that has not received any new
+  record for more than $ORPHAN_DELAY (based on recorded_on field) into an $ORPHANED_TABLE table, so that
+  these records are not re-processed everytime the script runs. 
+  If $ORPHANED_TABLE is empty, this feature is disabled.  */
+$ORPHANED_TABLE="orphans";
+$ORPHAN_DELAY = "2 days";
+
+
+/**** 4 : Archived records (optional) ****/
+/* When data has been processed successfully, the records can be saved into an archive table before being deleted. */
+/* This table also must have the same structure as $INCOMING */
+$ARCHIVES_TABLE="archived";
+
+/*-------------------------------------------------------*/
+
+/* Connect to the database */
+$dbconn = pg_connect($CONNSTR)
+    or die('Could not connect: ' . pg_last_error() . "\n");
+
+/* Handle orphans first */
+if ($ORPHANED_TABLE) {
+	$orphans_sql = 'SELECT * FROM "'.$INCOMING.'" ';
+	$orphans_sql.= 'WHERE "Acct-Session-Id" IN (';
+	$orphans_sql.= '   SELECT data.asid ';
+	$orphans_sql.= '   FROM (';
+	$orphans_sql.= '       SELECT "Acct-Session-Id" as asid, MAX("recorded_on") as latest, bool_or("Accounting-Record-Type" = 2) as got_start, bool_or("Accounting-Record-Type" = 4) as got_stop  ';
+	$orphans_sql.= '       FROM "'.$INCOMING.'"';
+	$orphans_sql.= '       GROUP BY "Acct-Session-Id") ';
+	$orphans_sql.= '   as data';
+	$orphans_sql.= '   WHERE data.latest < current_timestamp - interval \''. $ORPHAN_DELAY .'\' AND NOT ( got_start AND got_stop )';
+	$orphans_sql.= ');';
+	
+	/* Execute, move the orphaned records to the appropriate table. */
+	$result = pg_query($dbconn, $orphans_sql) or die('Query failed: ' . pg_last_error() . "\n");
+	if (pg_num_rows($result) > 0) {
+		$i = 0;
+		while ($orphan = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+			$i++;
+			
+			// this doesn't work: pg_insert( $dbconn, $ORPHANED_TABLE, $orphan ) or die('Insert failed: ' . pg_last_error() . "\n");
+			$sql  = "INSERT INTO \"$ORPHANED_TABLE\" ";
+			$sql .= '("';
+			$sql .= join('", "', array_keys($orphan));
+			$sql .= '") VALUES (';
+			for($c = 0; $c < count($orphan); $c++)
+			  $sql .= ($c ? ', ' : '').'$'.($c+1);
+			$sql .= ')';
+			pg_query_params($dbconn, $sql, array_values($orphan)) or die('Insert failed: ' . pg_last_error() . "\n");
+						
+			// This also doesn't work: pg_delete( $dbconn, $INCOMING, $orphan ) or die('Removing orphan failed: ' . pg_last_error() . "\n");
+			$sql  = "DELETE FROM \"$INCOMING\" WHERE ";
+			$sql .= '"Acct-Session-Id" = $1 AND "recorded_on" = $2';
+			pg_query_params($dbconn, $sql, array($orphan["Acct-Session-Id"], $orphan["recorded_on"])) or die('Removing orphan failed: ' . pg_last_error() . "\n");
+		}
+		echo $i." orphans have been moved to '".$ORPHANED_TABLE."'\n";
+	}
+	pg_free_result($result);
+}
+
+/* Delete duplicate records (which might have been received by different servers on different time, but are identical otherwise */
+$duplicate_sql  = ' SELECT * FROM (SELECT count(*) as cnt, min("recorded_on") as first, "Session-Id", "Acct-Session-Id", "Accounting-Record-Type", "Accounting-Record-Number"';
+$duplicate_sql .= ' FROM "'.$INCOMING.'" GROUP BY "Session-Id", "Acct-Session-Id", "Accounting-Record-Type", "Accounting-Record-Number") as qry WHERE qry.cnt > 1';
+$result = pg_query($dbconn, $orphans_sql) or die('Query failed: ' . pg_last_error() . "\n");
+if (pg_num_rows($result) > 0) {
+	/* We have some duplicates to delete */
+	$i=0;
+	while ($dup = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+		$i++;
+		$sql  = "DELETE FROM \"$INCOMING\" WHERE ";
+		$sql .= '"Session-Id" = $1 AND "Acct-Session-Id" = $2 AND "Accounting-Record-Type" = $3 AND "Accounting-Record-Number" = $4 AND "recorded_on" <> $5';
+		$params = array($dup["Session-Id"], $dup["Acct-Session-Id"], $dup["Accounting-Record-Type"], $dup["Accounting-Record-Number"], $dup["first"]);
+		pg_query_params($dbconn, $sql, $params) or die('Removing duplicates failed: ' . pg_last_error() . "\n");
+	}
+	echo $i." duplicate records have been deleted from '".$INCOMING."'\n";
+}
+pg_free_result($result);
+
+
+/* Now, find Acct-Session-Id of completed sessions */
+$completed_sql  = ' SELECT "Session-Id", "Acct-Session-Id" FROM (';
+$completed_sql .= '     SELECT "Session-Id", "Acct-Session-Id", bool_or("Accounting-Record-Type" = 2) as got_start, bool_or("Accounting-Record-Type" = 4) as got_stop ';
+$completed_sql .= '     FROM "'.$INCOMING.'" GROUP BY "Session-Id", "Acct-Session-Id"';
+$completed_sql .= '    ) as input';
+$completed_sql .= ' WHERE got_start AND got_stop';
+
+$sids = pg_query($dbconn, $completed_sql) or die('Query failed: ' . pg_last_error() . "\n");
+if (pg_num_rows($sids) > 0) {
+	while ($sids_line = pg_fetch_array($sids, null, PGSQL_ASSOC)) {
+		$sid  = $sids_line["Session-Id"];
+		$asid = $sids_line["Acct-Session-Id"];
+		echo "Processing Acct-Session-Id '".$asid."', Session-Id '".$sid ."'... ";
+		
+		$data=array();
+		
+		/* We simply ignore the interim record(s) in this first version, since they contain only cumulative data. It could be used in later version to draw the session details for example. */
+		
+		$result = pg_query_params($dbconn, 'SELECT *, "recorded_on" - CAST(textcat(text("Acct-Session-Time"), text(\' seconds\')) as INTERVAL) as start_time FROM "'.$INCOMING.
+						   '" WHERE "Session-Id" = $1 AND "Acct-Session-Id" = $2 AND "Accounting-Record-Type" = 4 ORDER BY "recorded_on"', 
+						   array($sid, $asid)) or die('Query failed: ' . pg_last_error() . "\n");
+		$record = pg_fetch_array($result, null, PGSQL_ASSOC) or die('Internal error, got_stop is true but no record was returned');
+		
+		$data[/* "user_name" */] = $record["User-Name"];
+		$data[/* "user_device" */] = $record["Calling-Station-Id"];
+		
+		$nas_id= ($record["NAS-Identifier"] ?: $record["Origin-Host"]) ?: "<unidentified NAS>";
+		$ip = $record["NAS-IP-Address"] ?: $record["NAS-IPv6-Address"];
+		$nas_ip= $ip ? inet_ntop(pg_unescape_bytea($ip)) : "<unknown NAS IP>";
+		$nas_csi = $record["Called-Station-Id"];
+		$data[/* "nas_info" */] = $nas_id . " (".$nas_ip.")" . ($nas_csi ? " - Called Station: ".$nas_csi : "");
+		
+		/* Is it possible to infer the session start time from this record only? */
+		if ($record["Acct-Session-Time"]) {
+			/* Yes, let's go */
+			$data[/* "sess_start" */] = $record["start_time"];
+			$data[/* "sess_duration" */] = $record["Acct-Session-Time"]." seconds";
+		} else {
+			/* No the information is missing, let's compute the approx value with the START record timestamp */
+			$res = pg_query_params($dbconn, 'SELECT t_start."recorded_on" as begining, t_end."recorded_on" - t_start."recorded_on" as duration'.
+							' FROM (SELECT "recorded_on" FROM "'.$INCOMING.'"  WHERE "Session-Id" = $1 AND "Acct-Session-Id" = $2 AND "Accounting-Record-Type" = 4 ORDER BY "recorded_on" LIMIT 1) as t_end, '.
+							'      (SELECT "recorded_on" FROM "'.$INCOMING.'"  WHERE "Session-Id" = $1 AND "Acct-Session-Id" = $2 AND "Accounting-Record-Type" = 2 ORDER BY "Accounting-Record-Number", "recorded_on" LIMIT 1) as t_start',
+							array($sid, $asid)) or die('Query failed: ' . pg_last_error() . "\n");
+			$vals = pg_fetch_array($result, null, PGSQL_ASSOC) or die('Internal error, unable to compute session time');
+			$data[/* "sess_start" */] = $vals["begining"];
+			$data[/* "sess_duration" */] = $vals["duration"];
+			pg_free_result($res);
+		}
+		
+		$data[/* "downl_bytes" */] = $record["Accounting-Output-Octets"];
+		$data[/* "downl_packets" */] = $record["Accounting-Output-Packets"];
+		$data[/* "upl_bytes" */] = $record["Accounting-Input-Octets"];
+		$data[/* "upl_packets" */] = $record["Accounting-Input-Packets"];
+		pg_free_result($result);
+		
+		$result = pg_query_params($dbconn, 
+			'INSERT INTO "'.$PROCESSED.'" (user_name, user_device, nas_info, sess_start, sess_duration, downl_bytes, downl_packets, upl_bytes, upl_packets) '.
+			'VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)', $data) or die('Query failed: ' . pg_last_error() . "\n");
+		pg_free_result($result);
+		echo "Data stored into '$PROCESSED'... ";
+		
+		/* Now that we have processed it, move these records to the $ARCHIVES_TABLE table. */
+		if ($ARCHIVES_TABLE) {
+			$result = pg_query_params($dbconn, 'SELECT * FROM "'.$INCOMING.'" WHERE "Session-Id" = $1 AND "Acct-Session-Id" = $2', array($sid, $asid)) or die('Query failed: ' . pg_last_error() . "\n");
+			$i = 0;
+			while ($rec = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+				$i++;
+
+				$sql  = "INSERT INTO \"$ARCHIVES_TABLE\" ";
+				$sql .= '("';
+				$sql .= join('", "', array_keys($rec));
+				$sql .= '") VALUES (';
+				for($c = 0; $c < count($rec); $c++)
+				  $sql .= ($c ? ', ' : '').'$'.($c+1);
+				$sql .= ')';
+				pg_query_params($dbconn, $sql, array_values($rec)) or die('Insert failed: ' . pg_last_error() . "\n");
+			}
+			echo $i." records archived into '".$ARCHIVES_TABLE."'";
+			pg_free_result($result);
+		}
+		echo "\n";
+		$result = pg_query_params($dbconn, 'DELETE FROM "'.$INCOMING.'" WHERE "Session-Id" = $1 AND "Acct-Session-Id" = $2', array($sid, $asid)) or die('Query failed: ' . pg_last_error() . "\n");
+		pg_free_result($result);
+	}
+}
+pg_free_result($sids);
+
+echo "Operation completed with success!\n";
+
+/* Closing connection */
+pg_close($dbconn);
+
+
+?>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/contrib/app_acct_tools/purge_to_file.php	Tue Sep 07 16:57:48 2010 +0900
@@ -0,0 +1,157 @@
+<?php
+# This file may be called either from CLI or web PHP installation.
+
+# The copyright is the same as the freeDiameter project. Licence is BSD.
+
+/*-------------------------------------------------------*/
+
+/* -- These parameters must match the app_acct.conf file -- */
+
+/* The Connection String used to access the database.
+  Example: "host=localhost dbname=app_acct user=freediameter password=foo" */
+$CONNSTR=""; 
+
+/* The name of the table were the records are saved by app_acct.fdx */
+$TABLE="incoming";
+
+
+/* -- These parameters are specific to this script -- */
+
+/* Name of the dump file to create. It can be relative or absolute. 
+   The script fails if this file already exists.
+   Use for example "data-".date("Ymdhis").".sql" to avoid collisions. */
+$DUMPFILE="incoming_data.sql";
+
+/* Does the script also echoes an HTML TABLE of the data processed? */
+$DISPLAY_HTML=FALSE; // it would be better to detect if we are called through Web or CLI...
+/* In addition, issue a full HTML page (including headers) ? */
+$HTML_HEADERS=TRUE;
+
+/*-------------------------------------------------------*/
+
+/* Connect to the database */
+$dbconn = pg_connect($CONNSTR)
+    or die('Could not connect: ' . pg_last_error() . "\n");
+    
+/* Check if the file exists */
+$file = fopen($DUMPFILE, "xb")
+    or die("The file '$DUMPFILE' already exists or cannot be created, aborting.\n");
+
+/* First, query the table format */
+$format_sql =   "SELECT ".
+		"   a.attname AS field,".
+		"   t.typname AS type,".
+/*		"   a.attlen AS length,". */
+		"   a.atttypmod AS lengthvar,".
+		"   a.attnotnull AS notnull".
+		" FROM".
+		"   pg_class c,".
+		"   pg_attribute a,".
+		"   pg_type t".
+		" WHERE".
+		"   c.relname = '" . $TABLE . "'".
+		"   AND a.attnum > 0".
+		"   AND a.attrelid = c.oid".
+		"   AND a.atttypid = t.oid".
+		"   ORDER BY a.attnum";
+$result = pg_query($dbconn, $format_sql) or die('Query failed: ' . pg_last_error() . "\n");
+
+/* app_acct.fdx only uses a few different fields types: */
+$conv_types = array(
+    "timestamptz" => "timestamp with time zone", /* recorded on */
+    "bytea" => "bytea",   /* octet string */
+    "int4" => "integer",  /* unsigned32, integer32, float32 */
+    "int8" => "bigint"    /* unsigned64, integer64, float64 */
+    );
+    
+$fields_types=array();
+
+fwrite($file, " -- Data purged on ".date(DATE_RFC822)."\n\n");
+fwrite($file, " -- Format of the table it was extracted from:\n");
+fwrite($file, " -- CREATE TABLE \"".$TABLE."\" (\n");
+$i = 0;
+while ($field = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+	if ($i++)
+		fwrite($file, ",\n");
+	fwrite($file, " --    \"".$field["field"]."\" ");
+	if (array_key_exists($field["type"], $conv_types))
+		$fields_types[$field["field"]] = $conv_types[$field["type"]];
+	else
+		$fields_types[$field["field"]] = $field["type"];
+	fwrite($file, $fields_types[$field["field"]]);
+	if ($field["lengthvar"] != "-1")
+		fwrite($file, "(".$field["lengthvar"].")");
+	if ($field["notnull"] == "t")
+		fwrite($file, " NOT NULL");
+}
+fwrite($file, "\n -- );\n\n");
+pg_free_result($result);
+
+/* Now, the data */
+$result = pg_query($dbconn, "SELECT * FROM \"".$TABLE."\"") or die('Query failed: ' . pg_last_error() . "\n");
+if (pg_num_rows($result) > 0) {
+	fwrite($file, "INSERT INTO \"".$TABLE."\"\n (\n");
+	$i = pg_num_fields($result);
+	if ($DISPLAY_HTML && $HTML_HEADERS) echo "<HTML>\n<HEAD><TITLE>Purge</TITLE></HEAD>\n<BODY>\n";
+	if ($DISPLAY_HTML) echo "<TABLE>\n  <TR>\n";
+	for ($j = 0; $j < $i; $j++) {
+	      fwrite($file, ($j ? ", " : "") . "\"" . pg_escape_string(pg_field_name($result, $j)). "\"");
+	      if ($DISPLAY_HTML) echo "    <TD>".htmlentities(pg_field_name($result, $j))."</TD>\n";
+	}
+	fwrite($file, "\n )\n VALUES \n");
+	if ($DISPLAY_HTML) echo "  </TR>\n";
+	$i = 0;
+	while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+		if ($i++)
+			fwrite($file, ",\n");
+		fwrite($file, " ( ");
+		
+		if ($DISPLAY_HTML) echo "  <TR>\n";
+		$j = 0;
+		$sql = "";
+		foreach ($line as $f => $v) {
+			if (!is_null($v))
+				switch ($fields_types[$f]) {
+					case "bytea":
+						$v = "E'".pg_escape_bytea(pg_unescape_bytea($v))."'";
+						break;
+					case "timestamp with time zone":
+						$v = "E'".pg_escape_string($v)."'";
+						break;
+				}
+			else
+				$v = "NULL";
+			
+	      		if ($DISPLAY_HTML) echo "    <TD>".htmlentities(print_r($line[$f], TRUE))."</TD>\n";
+
+			fwrite($file, ($j ? ", " : "") . $v);
+			$sql .= ($j ? " AND " : "") . "\"".pg_escape_string($f)."\" ";
+			if (is_null($line[$f]))
+				$sql .= " IS NULL";
+			else 
+				$sql .= " = " . $v;
+			$j++;
+		}
+		fwrite($file, ")");
+		if ($DISPLAY_HTML) echo "  </TR>\n";
+		$res = pg_query( "DELETE FROM \"".$TABLE."\" WHERE ".$sql) or die('DELETE query failed: ' . pg_last_error() . "\n");
+		pg_free_result($res);
+	}
+	fwrite($file, "\n;\n");
+	if ($DISPLAY_HTML) echo "</TABLE>\n";
+	if ($DISPLAY_HTML && $HTML_HEADERS) echo "</BODY>\n</HTML>\n";
+        else echo $i." records have been successfully written to '".$DUMPFILE."' and removed from database.\n";
+} else {
+	if ($DISPLAY_HTML) echo "<p><em>No new record in the database</em></p>\n";
+        else echo "No new record in the database, the generated file is empty.\n";
+}
+pg_free_result($result);
+
+/* Closing connection */
+pg_close($dbconn);
+
+/* Closing the file */
+fclose($file);
+
+
+?>
--- a/doc/app_acct.conf.sample	Mon Sep 06 14:46:25 2010 +0900
+++ b/doc/app_acct.conf.sample	Tue Sep 07 16:57:48 2010 +0900
@@ -46,134 +46,8 @@
 # Note that at the moment, GROUPED AVP are not supported. Also, only the top-level AVPs are
 # searched. This behavior can be changed quite easily if needed.
 
-# The following list is informative only.
-# You may also consult RFC4005 sections 10.2.1 and 10.2.2 for other examples
-# "Origin-Host";
-# "Origin-Realm";
-# "Destination-Realm";
-# "Destination-Host";
-# "Session-Id";
-# "Origin-State-Id"; 
-# "Accounting-Record-Type";
-# "Accounting-Record-Number";
-# "User-Name";
-# "Event-Timestamp"; 
-# "Acct-Application-Id";
-# "Accounting-Sub-Session-Id";
-# "Acct-Session-Id";
-# "Acct-Multi-Session-Id";
-# "Origin-AAA-Protocol";
-# "Acct-Delay-Time"; 
-# "NAS-Identifier";
-# "NAS-IP-Address";
-# "NAS-IPv6-Address";
-# "NAS-Port";
-# "NAS-Port-Id";
-# "NAS-Port-Type";
-# "Service-Type";
-# "Termination-Cause"; 
-# "Accounting-Input-Octets";
-# "Accounting-Input-Packets";
-# "Accounting-Output-Octets";
-# "Accounting-Output-Packets";
-# "Acct-Authentic";
-# "Acct-Link-Count";
-# "Acct-Session-Time"; 
-# "Acct-Tunnel-Connection";
-# "Acct-Tunnel-Packets-Lost"; 
-# "Callback-Id";
-# "Callback-Number";
-# "Called-Station-Id";
-# "Calling-Station-Id"; 
-# "Connect-Info";
-# "Originating-Line-Info"; 
-# "Authorization-Lifetime";
-# "Session-Timeout";
-# "Idle-Timeout";
-# "Port-Limit";
-# "Accounting-Realtime-Required"; 
-# "Acct-Interim-Interval";
-# "Filter-Id";
-# "NAS-Filter-Rule";
-# "QoS-Filter-Rule"; 
-# "Login-IP-Host";
-# "Login-IPv6-Host";
-# "Login-LAT-Group";
-# "Login-LAT-Node";
-# "Login-LAT-Port"; 
-# "Login-LAT-Service";
-# "Login-Service";
-# "Login-TCP-Port"; 
-# "Route-Record" = { multi=5; }; # Record the last 5 hops of the message
-
-# This is the database table corresponding to this list:
-# CREATE TABLE incoming (
-#     "recorded_on" timestamp with time zone NOT NULL,
-#     "recorded_serv" bytea,
-#     "Origin-Host" bytea NOT NULL,
-#     "Origin-Realm" bytea NOT NULL,
-#     "Destination-Realm" bytea,
-#     "Destination-Host" bytea,
-#     "Session-Id" bytea NOT NULL,
-#     "Origin-State-Id" integer,
-#     "Accounting-Record-Type" integer NOT NULL,
-#     "Accounting-Record-Number" integer NOT NULL,
-#     "User-Name" bytea,
-#     "Event-Timestamp" bytea,
-#     "Acct-Application-Id" integer,
-#     "Accounting-Sub-Session-Id" bigint,
-#     "Acct-Session-Id" bytea,
-#     "Acct-Multi-Session-Id" bytea,
-#     "Origin-AAA-Protocol" integer,
-#     "Acct-Delay-Time" integer,
-#     "NAS-Identifier" bytea,
-#     "NAS-IP-Address" bytea,
-#     "NAS-IPv6-Address" bytea,
-#     "NAS-Port" integer,
-#     "NAS-Port-Id" bytea,
-#     "NAS-Port-Type" integer,
-#     "Service-Type" integer,
-#     "Termination-Cause" integer,
-#     "Accounting-Input-Octets" bigint,
-#     "Accounting-Input-Packets" bigint,
-#     "Accounting-Output-Octets" bigint,
-#     "Accounting-Output-Packets" bigint,
-#     "Acct-Authentic" integer,
-#     "Acct-Link-Count" integer,
-#     "Acct-Session-Time" integer,
-#     "Acct-Tunnel-Connection" bytea,
-#     "Acct-Tunnel-Packets-Lost" integer,
-#     "Callback-Id" bytea,
-#     "Callback-Number" bytea,
-#     "Called-Station-Id" bytea,
-#     "Calling-Station-Id" bytea,
-#     "Connect-Info" bytea,
-#     "Originating-Line-Info" bytea,
-#     "Authorization-Lifetime" integer,
-#     "Session-Timeout" integer,
-#     "Idle-Timeout" integer,
-#     "Port-Limit" integer,
-#     "Accounting-Realtime-Required" integer,
-#     "Acct-Interim-Interval" integer,
-#     "Filter-Id" bytea,
-#     "NAS-Filter-Rule" bytea,
-#     "QoS-Filter-Rule" bytea,
-#     "Login-IP-Host" bytea,
-#     "Login-IPv6-Host" bytea,
-#     "Login-LAT-Group" bytea,
-#     "Login-LAT-Node" bytea,
-#     "Login-LAT-Port" bytea,
-#     "Login-LAT-Service" bytea,
-#     "Login-Service" integer,
-#     "Login-TCP-Port" integer,
-#     "Route-Record1" bytea,
-#     "Route-Record2" bytea,
-#     "Route-Record3" bytea,
-#     "Route-Record4" bytea,
-#     "Route-Record5" bytea
-# );
-
-
+# You may look at contrib/app_acct_tools/app_acct.conf and database.sql files
+# for an example of setup.
 
 
 #######################
"Welcome to our mercurial repository"