Mercurial > hg > freeDiameter

comparison contrib/app_acct_tools/display_stats.php @ 533:4cdf146f11d5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
3 # The copyright of this file is the same as the freeDiameter project. Licence is BSD.
4
5 #------------------------------------------------------------------------------------------
6 # DATABASE:
7
8 /* The Connection String used to access that database:
9 Example: "host=localhost dbname=app_acct user=freediameter password=foo" */
10 $CONNSTR="";
11
12 /* The name of the table containing the processed data (from process_records.php script) */
13 $PROCESSED="processed";
14
15 #------------------------------------------------------------------------------------------
16
17 ?>
18 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
19 <html lang="en-US">
20 <head>
21 <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
22
23 <title>Accounting Data</title>
24 <meta name="description" content="This page gives you access to your personal accounting data.">
25 <meta name="keywords" content="accounting">
26
27 <style type="text/css">
28 body { text-align:center; }
29 table.colored { border-collapse:collapse; margin-left:auto; margin-right:auto; }
30 table.colored td, table.colored th { border:1px solid green; padding-left:.5em; padding-right:.5em;}
31 .colored th { background-color:green; color:white; }
32 hr { width:15px; margin-top:2em;margin-bottom:2em; }
33 </style>
34 </head>
35
36 <body >
37 <h1>Statistical accounting data</h1>
38 <form method="GET">
39 <table class="colored">
40 <tr>
41 <th colspan="3">
42 Filtering options
43 </th>
44 </tr>
45 <tr>
46 <td><input type="checkbox" name="c_starttime"<?php if (isset($_GET["c_starttime"])) echo " checked"; ?>></td>
47 <td>Limit to sessions starting from (<a href="http://www.postgresql.org/docs/8.4/static/datatype-datetime.html">hint</a>):</td>
48 <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>
49 </tr>
50 <tr>
51 <td><input type="checkbox" name="c_endtime"<?php if (isset($_GET["c_endtime"])) echo " checked"; ?>></td>
52 <td>Limit to sessions starting until (<a href="http://www.postgresql.org/docs/8.4/static/datatype-datetime.html">hint</a>):</td>
53 <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>
54 </tr>
55 <tr>
56 <th colspan="3">
57 Apply this filter: <input type="submit" name="Submit">
58 </th>
59 </tr>
60 </table>
61 </form>
62 <p>Note well: this page displays only data about <em>terminated</em> sessions.</p>
63 <?php
64
65 /* Connect to the database */
66 $dbconn = pg_connect($CONNSTR)
67 or die('Could not connect: ' . pg_last_error() . "\n");
68
69 if ($_GET["c_starttime"] && $_GET["t_starttime"]) {
70 $START_TIME=$_GET["t_starttime"];
71 }
72 if ($_GET["c_endtime"] && $_GET["t_endtime"]) {
73 $END_TIME=$_GET["t_endtime"];
74 }
75 $sql_cond="";
76 if ($START_TIME) {
77 $sql_cond = "sess_start >= '".pg_escape_string($START_TIME)."'";
78 }
79 if ($END_TIME) {
80 if ($sql_cond)
81 $sql_cond .= " AND ";
82 $sql_cond .= "sess_start =< '".pg_escape_string($END_TIME)."'";
83 }
84
85
86 /* Function to format download size (from php.net) */
87 function human_readable( $size )
88 {
89 $count = 0;
90 $format = array("B","KB","MB","GB","TB","PB","EB","ZB","YB");
91 while(($size/1024)>1 && $count<8)
92 {
93 $size=$size/1024;
94 $count++;
95 }
96 if( $size >= 100 ) $decimals = 0;
97 elseif ($size >= 10 ) $decimals = 1;
98 else $decimals = 2;
99 $return = number_format($size,$decimals,'.',' ')." ".$format[$count];
100 return $return;
101 }
102
103 ?>
104
105 <hr />
106
107 <?php
108
109 /* First query: global data */
110 $sql = 'SELECT SUM(downl_bytes) as total_down, SUM(upl_bytes) as total_up,';
111 $sql .= ' to_char(min(sess_start), \'YYYY-MM-DD&nbsp;HH24:MI:SS&nbsp;TZ\') as first_sess,';
112 $sql .= ' to_char(max(sess_start), \'YYYY-MM-DD&nbsp;HH24:MI:SS&nbsp;TZ\') as last_sess,';
113 $sql .= ' count(distinct user_name) as nb_users,';
114 $sql .= ' count(distinct nas_info) as nb_ap';
115 $sql .= ' FROM "'.$PROCESSED.'"';
116 if ($sql_cond)
117 $sql .= ' WHERE '.$sql_cond;
118
119 /* Execute the query */
120 $result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
121 if (pg_num_rows($result) == 0) {
122 echo "<p><em>No data is available in the selected period.</em></p>\n";
123 die("</body></html>\n");
124 }
125
126 $data = pg_fetch_array($result, null, PGSQL_ASSOC);
127 ?>
128 <table class="colored">
129 <tr>
130 <th colspan="2">Total accounted data</th>
131 </tr>
132 <?php
133 echo " <tr><td>First session started:</td><td>".$data["first_sess"]."</td></tr>\n";
134 echo " <tr><td>Last session started:</td><td>".$data["last_sess"]."</td></tr>\n";
135 echo " <tr><td>Total data downloaded:</td><td><strong>".human_readable($data["total_down"])."</strong></td></tr>\n";
136 echo " <tr><td>Total data uploaded:</td><td><strong>".human_readable($data["total_up"])."</strong></td></tr>\n";
137 echo " <tr><td>Number of users who connected at least once:</td><td>".$data["nb_users"]."</td></tr>\n";
138 echo " <tr><td>Number of access points involved:</td><td>".$data["nb_ap"]."</td></tr>\n";
139 echo " </tr>\n";
140 echo " </table>\n";
141
142 pg_free_result($result);
143
144 ?>
145
146 <hr />
147
148 <table class="colored">
149 <tr>
150 <th colspan="2">Top-5 <strong>downloading</strong> users</th>
151 </tr>
152 <?php
153
154 /* Now, get the top 5 downloaders */
155 $sql = 'SELECT SUM(downl_bytes) as total_down, user_name';
156 $sql .= ' FROM "'.$PROCESSED.'"';
157 if ($sql_cond)
158 $sql .= ' WHERE '.$sql_cond;
159 $sql .= ' GROUP BY user_name';
160 $sql .= ' ORDER BY total_down desc, user_name';
161 $sql .= ' LIMIT 5';
162
163 /* Execute the query */
164 $result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
165 while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
166 echo " <tr>\n";
167 echo " <td>".htmlentities(pg_unescape_bytea($data["user_name"]))."</td>\n";
168 echo " <td>".human_readable($data["total_down"])."</td>\n";
169 echo " </tr>\n";
170 }
171 pg_free_result($result);
172 ?>
173 </table>
174
175 <hr />
176
177 <table class="colored">
178 <tr>
179 <th colspan="2">Top-5 <strong>uploading</strong> users</th>
180 </tr>
181 <?php
182
183 /* Now, get the top 5 downloaders */
184 $sql = 'SELECT SUM(upl_bytes) as total_up, user_name';
185 $sql .= ' FROM "'.$PROCESSED.'"';
186 if ($sql_cond)
187 $sql .= ' WHERE '.$sql_cond;
188 $sql .= ' GROUP BY user_name';
189 $sql .= ' ORDER BY total_up desc, user_name';
190 $sql .= ' LIMIT 5';
191
192 /* Execute the query */
193 $result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
194 while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
195 echo " <tr>\n";
196 echo " <td>".htmlentities(pg_unescape_bytea($data["user_name"]))."</td>\n";
197 echo " <td>".human_readable($data["total_up"])."</td>\n";
198 echo " </tr>\n";
199 }
200 pg_free_result($result);
201 ?>
202 </table>
203
204 <hr />
205
206 <table class="colored">
207 <tr>
208 <th colspan="2">Top-8 access devices (by number of users)</th>
209 </tr>
210 <?php
211
212 /* Now, get the top 5 downloaders */
213 $sql = 'SELECT count(distinct user_name) as unc, nas_info';
214 $sql .= ' FROM "'.$PROCESSED.'"';
215 if ($sql_cond)
216 $sql .= ' WHERE '.$sql_cond;
217 $sql .= ' GROUP BY nas_info';
218 $sql .= ' ORDER BY unc desc, nas_info';
219 $sql .= ' LIMIT 8';
220
221 /* Execute the query */
222 $result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
223 while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
224 echo " <tr>\n";
225 echo " <td>".htmlentities(pg_unescape_bytea($data["nas_info"]))."</td>\n";
226 echo " <td>".$data["unc"]."</td>\n";
227 echo " </tr>\n";
228 }
229 pg_free_result($result);
230 ?>
231 </table>
232
233 <hr />
234
235 <table class="colored">
236 <tr>
237 <th colspan="2">Top-8 access devices (by traffic: up+down)</th>
238 </tr>
239 <?php
240
241 /* Now, get the top 5 downloaders */
242 $sql = 'SELECT SUM(upl_bytes) + SUM(downl_bytes) as traffic, nas_info';
243 $sql .= ' FROM "'.$PROCESSED.'"';
244 if ($sql_cond)
245 $sql .= ' WHERE '.$sql_cond;
246 $sql .= ' GROUP BY nas_info';
247 $sql .= ' ORDER BY traffic desc, nas_info';
248 $sql .= ' LIMIT 8';
249
250 /* Execute the query */
251 $result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
252 while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
253 echo " <tr>\n";
254 echo " <td>".htmlentities(pg_unescape_bytea($data["nas_info"]))."</td>\n";
255 echo " <td>".human_readable($data["traffic"])."</td>\n";
256 echo " </tr>\n";
257 }
258 pg_free_result($result);
259 ?>
260 </table>
261
262 <hr />
263 <?php
264 /* Closing connection */
265 pg_close($dbconn);
266 ?>
267 <p><small>Data generated by scripts from the <a href="http://www.freediameter.net">freeDiameter</a> project.</small></p>
268 </body>
269 </html>
"Welcome to our mercurial repository"