Brian Waters | 13d9601 | 2017-12-08 16:53:31 -0600 | [diff] [blame^] | 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 | ?> |