freeDiameter: contrib/app_acct_tools/process

annotate contrib/app_acct_tools/process_records.php @ 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
children

rev	line source
533 4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	1 <?php
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	2 # This file may be called either from CLI or web PHP installation.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	3 # The copyright is the same as the freeDiameter project. Licence is BSD.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	4
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	5 # IMPORTANT: This script is highly experimental, PLEASE KEEP A COPY OF YOUR ACCOUNTING DATA
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	6 # if this data has any importance.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	7
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	8 /-------------------------------------------------------/
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	9
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	10 /* This script supports only one database where all tables are kept.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	11 The Connection String used to access that database:
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	12 Example: "host=localhost dbname=app_acct user=freediameter password=foo" */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	13 $CONNSTR="";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	14
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	15
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	16 /** 1 : Incoming records (output of app_acct.fdx) **/
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	17 /* The name of the table were the raw records are saved (from app_acct.conf) */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	18 $INCOMING="incoming";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	19
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	20 /* Note: For this script, this table MUST contain the following fields (with these names):
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	21 Session-Id, User-Name, Accounting-Record-Type, Accounting-Record-Number, Acct-Session-Id,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	22 Accounting-{In,Out}put-{Octets,Packets},
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	23 NAS-Identifier, Called-Station-Id, Calling-Station-Id, recorded_on.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	24 */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	25
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	26
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	27 /** 2 : Processed records (output of this script, input for display_results.php) **/
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	28 /* The name of the table */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	29 $PROCESSED="processed";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	30
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	31 /* See process_database.sql for command to create this database */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	32
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	33 /** 3 : Orphan records (optional) **/
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	34 /* The script can move records belonging to an unterminated session that has not received any new
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	35 record for more than $ORPHAN_DELAY (based on recorded_on field) into an $ORPHANED_TABLE table, so that
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	36 these records are not re-processed everytime the script runs.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	37 If $ORPHANED_TABLE is empty, this feature is disabled. */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	38 $ORPHANED_TABLE="orphans";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	39 $ORPHAN_DELAY = "2 days";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	40
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	41
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	42 /** 4 : Archived records (optional) **/
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	43 /* When data has been processed successfully, the records can be saved into an archive table before being deleted. */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	44 /* This table also must have the same structure as $INCOMING */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	45 $ARCHIVES_TABLE="archived";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	46
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	47 /-------------------------------------------------------/
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	48
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	49 /* Connect to the database */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	50 $dbconn = pg_connect($CONNSTR)
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	51 or die('Could not connect: ' . pg_last_error() . "\n");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	52
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	53 /* Handle orphans first */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	54 if ($ORPHANED_TABLE) {
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	55 $orphans_sql = 'SELECT * FROM "'.$INCOMING.'" ';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	56 $orphans_sql.= 'WHERE "Acct-Session-Id" IN (';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	57 $orphans_sql.= ' SELECT data.asid ';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	58 $orphans_sql.= ' FROM (';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	59 $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 ';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	60 $orphans_sql.= ' FROM "'.$INCOMING.'"';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	61 $orphans_sql.= ' GROUP BY "Acct-Session-Id") ';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	62 $orphans_sql.= ' as data';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	63 $orphans_sql.= ' WHERE data.latest < current_timestamp - interval \''. $ORPHAN_DELAY .'\' AND NOT ( got_start AND got_stop )';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	64 $orphans_sql.= ');';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	65
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	66 /* Execute, move the orphaned records to the appropriate table. */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	67 $result = pg_query($dbconn, $orphans_sql) or die('Query failed: ' . pg_last_error() . "\n");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	68 if (pg_num_rows($result) > 0) {
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	69 $i = 0;
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	70 while ($orphan = pg_fetch_array($result, null, PGSQL_ASSOC)) {
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	71 $i++;
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	72
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	73 // this doesn't work: pg_insert( $dbconn, $ORPHANED_TABLE, $orphan ) or die('Insert failed: ' . pg_last_error() . "\n");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	74 $sql = "INSERT INTO \"$ORPHANED_TABLE\" ";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	75 $sql .= '("';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	76 $sql .= join('", "', array_keys($orphan));
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	77 $sql .= '") VALUES (';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	78 for($c = 0; $c < count($orphan); $c++)
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	79 $sql .= ($c ? ', ' : '').'$'.($c+1);
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	80 $sql .= ')';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	81 pg_query_params($dbconn, $sql, array_values($orphan)) or die('Insert failed: ' . pg_last_error() . "\n");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	82
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	83 // This also doesn't work: pg_delete( $dbconn, $INCOMING, $orphan ) or die('Removing orphan failed: ' . pg_last_error() . "\n");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	84 $sql = "DELETE FROM \"$INCOMING\" WHERE ";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	85 $sql .= '"Acct-Session-Id" = $1 AND "recorded_on" = $2';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	86 pg_query_params($dbconn, $sql, array($orphan["Acct-Session-Id"], $orphan["recorded_on"])) or die('Removing orphan failed: ' . pg_last_error() . "\n");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	87 }
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	88 echo $i." orphans have been moved to '".$ORPHANED_TABLE."'\n";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	89 }
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	90 pg_free_result($result);
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	91 }
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	92
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	93 /* Delete duplicate records (which might have been received by different servers on different time, but are identical otherwise */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	94 $duplicate_sql = ' SELECT * FROM (SELECT count(*) as cnt, min("recorded_on") as first, "Session-Id", "Acct-Session-Id", "Accounting-Record-Type", "Accounting-Record-Number"';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	95 $duplicate_sql .= ' FROM "'.$INCOMING.'" GROUP BY "Session-Id", "Acct-Session-Id", "Accounting-Record-Type", "Accounting-Record-Number") as qry WHERE qry.cnt > 1';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	96 $result = pg_query($dbconn, $orphans_sql) or die('Query failed: ' . pg_last_error() . "\n");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	97 if (pg_num_rows($result) > 0) {
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	98 /* We have some duplicates to delete */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	99 $i=0;
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	100 while ($dup = pg_fetch_array($result, null, PGSQL_ASSOC)) {
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	101 $i++;
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	102 $sql = "DELETE FROM \"$INCOMING\" WHERE ";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	103 $sql .= '"Session-Id" = $1 AND "Acct-Session-Id" = $2 AND "Accounting-Record-Type" = $3 AND "Accounting-Record-Number" = $4 AND "recorded_on" <> $5';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	104 $params = array($dup["Session-Id"], $dup["Acct-Session-Id"], $dup["Accounting-Record-Type"], $dup["Accounting-Record-Number"], $dup["first"]);
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	105 pg_query_params($dbconn, $sql, $params) or die('Removing duplicates failed: ' . pg_last_error() . "\n");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	106 }
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	107 echo $i." duplicate records have been deleted from '".$INCOMING."'\n";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	108 }
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	109 pg_free_result($result);
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	110
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	111
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	112 /* Now, find Acct-Session-Id of completed sessions */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	113 $completed_sql = ' SELECT "Session-Id", "Acct-Session-Id" FROM (';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	114 $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 ';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	115 $completed_sql .= ' FROM "'.$INCOMING.'" GROUP BY "Session-Id", "Acct-Session-Id"';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	116 $completed_sql .= ' ) as input';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	117 $completed_sql .= ' WHERE got_start AND got_stop';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	118
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	119 $sids = pg_query($dbconn, $completed_sql) or die('Query failed: ' . pg_last_error() . "\n");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	120 if (pg_num_rows($sids) > 0) {
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	121 while ($sids_line = pg_fetch_array($sids, null, PGSQL_ASSOC)) {
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	122 $sid = $sids_line["Session-Id"];
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	123 $asid = $sids_line["Acct-Session-Id"];
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	124 echo "Processing Acct-Session-Id '".$asid."', Session-Id '".$sid ."'... ";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	125
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	126 $data=array();
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	127
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	128 /* 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. */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	129
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	130 $result = pg_query_params($dbconn, 'SELECT *, "recorded_on" - CAST(textcat(text("Acct-Session-Time"), text(\' seconds\')) as INTERVAL) as start_time FROM "'.$INCOMING.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	131 '" WHERE "Session-Id" = $1 AND "Acct-Session-Id" = $2 AND "Accounting-Record-Type" = 4 ORDER BY "recorded_on"',
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	132 array($sid, $asid)) or die('Query failed: ' . pg_last_error() . "\n");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	133 $record = pg_fetch_array($result, null, PGSQL_ASSOC) or die('Internal error, got_stop is true but no record was returned');
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	134
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	135 $data[/* "user_name" */] = $record["User-Name"];
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	136 $data[/* "user_device" */] = $record["Calling-Station-Id"];
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	137
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	138 $nas_id= ($record["NAS-Identifier"] ?: $record["Origin-Host"]) ?: "<unidentified NAS>";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	139 $ip = $record["NAS-IP-Address"] ?: $record["NAS-IPv6-Address"];
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	140 $nas_ip= $ip ? inet_ntop(pg_unescape_bytea($ip)) : "<unknown NAS IP>";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	141 $nas_csi = $record["Called-Station-Id"];
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	142 $data[/* "nas_info" */] = $nas_id . " (".$nas_ip.")" . ($nas_csi ? " - Called Station: ".$nas_csi : "");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	143
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	144 /* Is it possible to infer the session start time from this record only? */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	145 if ($record["Acct-Session-Time"]) {
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	146 /* Yes, let's go */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	147 $data[/* "sess_start" */] = $record["start_time"];
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	148 $data[/* "sess_duration" */] = $record["Acct-Session-Time"]." seconds";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	149 } else {
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	150 /* No the information is missing, let's compute the approx value with the START record timestamp */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	151 $res = pg_query_params($dbconn, 'SELECT t_start."recorded_on" as begining, t_end."recorded_on" - t_start."recorded_on" as duration'.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	152 ' 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, '.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	153 ' (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',
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	154 array($sid, $asid)) or die('Query failed: ' . pg_last_error() . "\n");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	155 $vals = pg_fetch_array($result, null, PGSQL_ASSOC) or die('Internal error, unable to compute session time');
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	156 $data[/* "sess_start" */] = $vals["begining"];
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	157 $data[/* "sess_duration" */] = $vals["duration"];
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	158 pg_free_result($res);
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	159 }
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	160
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	161 $data[/* "downl_bytes" */] = $record["Accounting-Output-Octets"];
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	162 $data[/* "downl_packets" */] = $record["Accounting-Output-Packets"];
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	163 $data[/* "upl_bytes" */] = $record["Accounting-Input-Octets"];
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	164 $data[/* "upl_packets" */] = $record["Accounting-Input-Packets"];
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	165 pg_free_result($result);
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	166
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	167 $result = pg_query_params($dbconn,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	168 'INSERT INTO "'.$PROCESSED.'" (user_name, user_device, nas_info, sess_start, sess_duration, downl_bytes, downl_packets, upl_bytes, upl_packets) '.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	169 'VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)', $data) or die('Query failed: ' . pg_last_error() . "\n");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	170 pg_free_result($result);
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	171 echo "Data stored into '$PROCESSED'... ";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	172
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	173 /* Now that we have processed it, move these records to the $ARCHIVES_TABLE table. */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	174 if ($ARCHIVES_TABLE) {
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	175 $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");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	176 $i = 0;
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	177 while ($rec = pg_fetch_array($result, null, PGSQL_ASSOC)) {
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	178 $i++;
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	179
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	180 $sql = "INSERT INTO \"$ARCHIVES_TABLE\" ";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	181 $sql .= '("';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	182 $sql .= join('", "', array_keys($rec));
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	183 $sql .= '") VALUES (';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	184 for($c = 0; $c < count($rec); $c++)
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	185 $sql .= ($c ? ', ' : '').'$'.($c+1);
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	186 $sql .= ')';
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	187 pg_query_params($dbconn, $sql, array_values($rec)) or die('Insert failed: ' . pg_last_error() . "\n");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	188 }
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	189 echo $i." records archived into '".$ARCHIVES_TABLE."'";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	190 pg_free_result($result);
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	191 }
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	192 echo "\n";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	193 $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");
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	194 pg_free_result($result);
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	195 }
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	196 }
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	197 pg_free_result($sids);
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	198
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	199 echo "Operation completed with success!\n";
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	200
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	201 /* Closing connection */
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	202 pg_close($dbconn);
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	203
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	204
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data. Sebastien Decugis <sdecugis@nict.go.jp> parents: diff changeset	205 ?>

Mercurial > hg > freeDiameter

annotate contrib/app_acct_tools/process_records.php @ 533:4cdf146f11d5