Initial commit
Change-Id: I6a4444e3c193dae437cd7929f4c39aba7b749efa
diff --git a/contrib/app_acct_tools/README b/contrib/app_acct_tools/README
new file mode 100644
index 0000000..4728f15
--- /dev/null
+++ b/contrib/app_acct_tools/README
@@ -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 optionally 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.
+
diff --git a/contrib/app_acct_tools/app_acct.conf b/contrib/app_acct_tools/app_acct.conf
new file mode 100644
index 0000000..0c9cdb8
--- /dev/null
+++ b/contrib/app_acct_tools/app_acct.conf
@@ -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; };
diff --git a/contrib/app_acct_tools/database.sql b/contrib/app_acct_tools/database.sql
new file mode 100644
index 0000000..c3a3072
--- /dev/null
+++ b/contrib/app_acct_tools/database.sql
@@ -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
+ );
+
diff --git a/contrib/app_acct_tools/display_results.php b/contrib/app_acct_tools/display_results.php
new file mode 100644
index 0000000..f84d3d2
--- /dev/null
+++ b/contrib/app_acct_tools/display_results.php
@@ -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 below.
+
+## $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 HH24:MI:SS (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);
+
+
+
+?>
diff --git a/contrib/app_acct_tools/display_self.php b/contrib/app_acct_tools/display_self.php
new file mode 100644
index 0000000..906cb0a
--- /dev/null
+++ b/contrib/app_acct_tools/display_self.php
@@ -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>
diff --git a/contrib/app_acct_tools/display_stats.php b/contrib/app_acct_tools/display_stats.php
new file mode 100644
index 0000000..31beb12
--- /dev/null
+++ b/contrib/app_acct_tools/display_stats.php
@@ -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 HH24:MI:SS TZ\') as first_sess,';
+$sql .= ' to_char(max(sess_start), \'YYYY-MM-DD HH24:MI:SS 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>
diff --git a/contrib/app_acct_tools/process_records.php b/contrib/app_acct_tools/process_records.php
new file mode 100644
index 0000000..c64e1d6
--- /dev/null
+++ b/contrib/app_acct_tools/process_records.php
@@ -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);
+
+
+?>
diff --git a/contrib/app_acct_tools/purge_to_file.php b/contrib/app_acct_tools/purge_to_file.php
new file mode 100644
index 0000000..0fc9f29
--- /dev/null
+++ b/contrib/app_acct_tools/purge_to_file.php
@@ -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);
+
+
+?>