Mercurial > hg > freeDiameter
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 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); + + + +?>
--- /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 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>
--- /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. #######################