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