]> CyberLeo.Net >> Repos - SourceForge/phpwiki.git/blob - lib/plugin/AnalyseAccessLogSql.php
New FSF address
[SourceForge/phpwiki.git] / lib / plugin / AnalyseAccessLogSql.php
1 <?php
2 // $Id$
3 /*
4  * Copyright 2005 Charles Corrigan and $ThePhpWikiProgrammingTeam
5  *
6  * This file is (not yet) part of PhpWiki.
7  *
8  * PhpWiki is free software; you can redistribute it and/or modify
9  * it under the terms of the GNU General Public License as published by
10  * the Free Software Foundation; either version 2 of the License, or
11  * (at your option) any later version.
12  *
13  * PhpWiki is distributed in the hope that it will be useful,
14  * but WITHOUT ANY WARRANTY; without even the implied warranty of
15  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16  * GNU General Public License for more details.
17  *
18  * You should have received a copy of the GNU General Public License along
19  * with PhpWiki; if not, write to the Free Software Foundation, Inc.,
20  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
21  */
22
23 /**
24  * A plugin that provides a framework and some useful queries to analyse the SQL
25  * access log. This information may be sensitive and so is limited to
26  * administrator access only.
27  *
28  * To add a new query, see _getQueryString()
29  */
30 class WikiPlugin_AnalyseAccessLogSql
31 extends WikiPlugin
32 {
33     /**
34      * Build the query string
35      *
36      * FIXME: some or all of these queries may be MySQL specific / non-portable
37      * FIXME: properly quote the string args
38      *
39      * The column names displayed are generated from the actual query column
40      * names, so make sure that each column in the query is given a user
41      * friendly name. Note that the column names are passed to _() and so may be
42      * translated.
43      *
44      * If there are query specific where conditions, then the construction
45      * "    if ($where_conditions<>'')
46      *          $where_conditions = 'WHERE '.$where_conditions.' ';"
47      * should be changed to
48      * "    if ($where_conditions<>'')
49      *          $where_conditions = 'AND '.$where_conditions.' ';"
50      * and in the assignment to query have something like
51      * "    $query= "SELECT "
52      *          ."referer "
53      *          ."FROM $accesslog "
54      *          ."WHERE referer IS NOT NULL "
55      *          .$where_conditions
56      */
57     function _getQueryString(&$args) {
58         // extract any parametrised conditions from the arguments,
59         // in particular, how much history to select
60         $where_conditions = $this->_getWhereConditions($args);
61
62         // get the correct name for the table
63         //FIXME is there a more correct way to do this?
64         global $DBParams, $request;
65         $accesslog = (!empty($DBParams['prefix']) ? $DBParams['prefix'] : '')."accesslog";
66
67         $query = '';
68         $backend_type = $request->_dbi->_backend->backendType();
69         switch ($backend_type) {
70         case 'mysql':
71             $Referring_URL = "left(referer,length(referer)-instr(reverse(referer),'?'))"; break;
72         case 'pgsql':
73         case 'postgres7':
74             $Referring_URL = "substr(referer,0,position('?' in referer))"; break;
75         default:
76             $Referring_URL = "referer";
77         }
78         switch ($args['mode']) {
79         case 'referring_urls':
80             if ($where_conditions<>'')
81                 $where_conditions = 'WHERE '.$where_conditions.' ';
82             $query = "SELECT "
83                 . "$Referring_URL AS Referring_URL, "
84                 . "count(*) AS Referral_Count "
85                 . "FROM $accesslog "
86                 . $where_conditions
87                 . "GROUP BY Referring_URL";
88             break;
89         case 'external_referers':
90             $args['local_referrers'] = 'false';
91             $where_conditions = $this->_getWhereConditions($args);
92             if ($where_conditions<>'')
93                 $where_conditions = 'WHERE '.$where_conditions.' ';
94             $query = "SELECT "
95                 . "$Referring_URL AS Referring_URL, "
96                 . "count(*) AS Referral_Count "
97                 . "FROM $accesslog "
98                 . $where_conditions
99                 . "GROUP BY Referring_URL";
100             break;
101         case 'referring_domains':
102             if ($where_conditions<>'')
103                 $where_conditions = 'WHERE '.$where_conditions.' ';
104             switch ($backend_type) {
105             case 'mysql':
106                 $Referring_Domain = "left(referer, if(locate('/', referer, 8) > 0,locate('/', referer, 8) -1, length(referer)))"; break;
107             case 'pgsql':
108             case 'postgres7':
109                 $Referring_Domain = "substr(referer,0,8) || regexp_replace(substr(referer,8), '/.*', '')"; break;
110             default:
111                 $Referring_Domain = "referer"; break;
112             }
113             $query = "SELECT "
114                 . "$Referring_Domain AS Referring_Domain, "
115                 . "count(*) AS Referral_Count "
116                 . "FROM $accesslog "
117                 . $where_conditions
118                 . "GROUP BY Referring_Domain";
119             break;
120         case 'remote_hosts':
121             if ($where_conditions<>'')
122                 $where_conditions = 'WHERE '.$where_conditions.' ';
123             $query = "SELECT "
124                 ."remote_host AS Remote_Host, "
125                 ."count(*) AS Access_Count "
126                 ."FROM $accesslog "
127                 .$where_conditions
128                 ."GROUP BY Remote_Host";
129             break;
130         case 'users':
131             if ($where_conditions<>'')
132                 $where_conditions = 'WHERE '.$where_conditions.' ';
133             $query = "SELECT "
134                 ."remote_user AS User, "
135                 ."count(*) AS Access_Count "
136                 ."FROM $accesslog "
137                 .$where_conditions
138                 ."GROUP BY remote_user";
139             break;
140         case 'host_users':
141             if ($where_conditions<>'')
142                 $where_conditions = 'WHERE '.$where_conditions.' ';
143             $query = "SELECT "
144                 ."remote_host AS Remote_Host, "
145                 ."remote_user AS User, "
146                 ."count(*) AS Access_Count "
147                 ."FROM $accesslog "
148                 .$where_conditions
149                 ."GROUP BY remote_host, remote_user";
150             break;
151         case "search_bots":
152             // This queries for all entries in the SQL access log table that
153             // have a dns name that I know to be a web search engine crawler and
154             // categorises the results into time buckets as per the list below
155             // 0 - 1 minute - 60
156             // 1 - 1 hour   - 3600     = 60 * 60
157             // 2 - 1 day    - 86400    = 60 * 60 * 24
158             // 3 - 1 week   - 604800   = 60 * 60 * 24 * 7
159             // 4 - 1 month  - 2629800  = 60 * 60 * 24 * 365.25 / 12
160             // 5 - 1 year   - 31557600 = 60 * 60 * 24 * 365.25
161             $now = time();
162             $query = "SELECT "
163                 ."CASE WHEN $now-time_stamp<60 THEN '"._("0 - last minute")."' ELSE "
164                   ."CASE WHEN $now-time_stamp<3600 THEN '"._("1 - 1 minute to 1 hour")."' ELSE "
165                     ."CASE WHEN $now-time_stamp<86400 THEN '"._("2 - 1 hour to 1 day")."' ELSE "
166                       ."CASE WHEN $now-time_stamp<604800 THEN '"._("3 - 1 day to 1 week")."' ELSE "
167                         ."CASE WHEN $now-time_stamp<2629800 THEN '"._("4 - 1 week to 1 month")."' ELSE "
168                           ."CASE WHEN $now-time_stamp<31557600 THEN '"._("5 - 1 month to 1 year")."' ELSE "
169                             ."'"._("6 - more than 1 year")."' END END END END END END AS Time_Scale, "
170                 ."remote_host AS Remote_Host, "
171                 ."count(*) AS Access_Count "
172                 ."FROM $accesslog "
173                 ."WHERE (remote_host LIKE '%googlebot.com' "
174                 ."OR remote_host LIKE '%alexa.com' "
175                 ."OR remote_host LIKE '%inktomisearch.com' "
176                 ."OR remote_host LIKE '%msnbot.msn.com') "
177                 .($where_conditions ? 'AND '.$where_conditions : '')
178                 ."GROUP BY Time_Scale, remote_host";
179             break;
180         case "search_bots_hits":
181             // This queries for all entries in the SQL access log table that
182             // have a dns name that I know to be a web search engine crawler and
183             // displays the URI that was hit.
184             // If PHPSESSID appears in the URI, just display the URI to the left of this
185             $sessname = session_name();
186             switch ($backend_type) {
187             case 'mysql':
188                 $Request_URI = "IF(instr(request_uri, '$sessname')=0, request_uri,left(request_uri, instr(request_uri, '$sessname')-2))";
189                 break;
190             case 'pgsql':
191             case 'postgres7':
192                 $Request_URI = "regexp_replace(request_uri, '$sessname.*', '')"; break;
193             default:
194                 $Request_URI = 'request_uri'; break;
195             }
196             $now = time();
197             $query = "SELECT "
198                 ."CASE WHEN $now-time_stamp<60 THEN '"._("0 - last minute")."' ELSE "
199                   ."CASE WHEN $now-time_stamp<3600 THEN '"._("1 - 1 minute to 1 hour")."' ELSE "
200                     ."CASE WHEN $now-time_stamp<86400 THEN '"._("2 - 1 hour to 1 day")."' ELSE "
201                       ."CASE WHEN $now-time_stamp<604800 THEN '"._("3 - 1 day to 1 week")."' ELSE "
202                         ."CASE WHEN $now-time_stamp<2629800 THEN '"._("4 - 1 week to 1 month")."' ELSE "
203                           ."CASE WHEN $now-time_stamp<31557600 THEN '"._("5 - 1 month to 1 year")."' ELSE "
204                             ."'"._("6 - more than 1 year")."' END END END END END END AS Time_Scale, "
205                 ."remote_host AS Remote_Host, "
206                 ."$Request_URI AS Request_URI "
207                 ."FROM $accesslog "
208                 ."WHERE (remote_host LIKE '%googlebot.com' "
209                 ."OR remote_host LIKE '%alexa.com' "
210                 ."OR remote_host LIKE '%inktomisearch.com' "
211                 ."OR remote_host LIKE '%msnbot.msn.com') "
212                 .($where_conditions ? 'AND '.$where_conditions : '')
213                 ."ORDER BY time_stamp";
214         }
215         return $query;
216     }
217
218     /** Honeypot for xgettext. Those strings are translated dynamically.
219      */
220     function _locale_dummy() {
221         $dummy = array(
222                        // mode caption
223                        _("referring_urls"),
224                        _("external_referers"),
225                        _("referring_domains"),
226                        _("remote_hosts"),
227                        _("users"),
228                        _("host_users"),
229                        _("search_bots"),
230                        _("search_bots_hits"),
231                        // period header
232                        _("minutes"),
233                        _("hours"),
234                        _("days"),
235                        _("weeks"),
236                        );
237     }
238
239     function getDefaultArguments() {
240         return array(
241                      'mode'             => 'referring_domains',
242                      // referring_domains, referring_urls, remote_hosts, users, host_users, search_bots, search_bots_hits
243                      'caption'          => '',
244                      // blank means use the mode as the caption/title for the output
245                      'local_referrers'  => 'true',  // only show external referring sites
246                      'period'           => '',      // the type of period to report:
247                      // may be weeks, days, hours, minutes, or blank for all
248                      'count'            => '0'      // the number of periods to report
249                      );
250     }
251
252     function getName () {
253         return _("AnalyseAccessLogSql");
254     }
255
256     function getDescription () {
257         return _("Show summary information from the access log table.");
258     }
259
260     function run($dbi, $argstr, &$request, $basepage) {
261         // flag that the output may not be cached - i.e. it is dynamic
262         $request->setArg('nocache', 1);
263
264         if (!$request->_user->isAdmin())
265             return HTML::p(_("The requested information is available only to Administrators."));
266
267         if (!ACCESS_LOG_SQL) // need read access
268             return HTML::p(_("The SQL_ACCESS_LOG is not enabled."));
269
270         // set aside a place for the table headers, see _setHeaders()
271         $this->_theadrow = HTML::tr();
272         $this->_headerSet = false;
273
274         $args = $this->getArgs($argstr, $request);
275
276         $query = $this->_getQueryString($args);
277
278         if ($query=='')
279             return HTML::p(sprintf( _("Unrecognised parameter 'mode=%s'"),
280                                     $args['mode']));
281
282         // get the data back.
283         // Note that this must be done before the final generation ofthe table,
284         // otherwise the headers will not be ready
285         $tbody = $this->_getQueryResults($query, $dbi);
286
287         return HTML::table(array('border'        => 1,
288                                  'cellspacing'   => 1,
289                                  'cellpadding'   => 1),
290                     HTML::caption(HTML::h1(HTML::br(),$this->_getCaption($args))),
291                     HTML::thead($this->_theadrow),
292                     $tbody);
293     }
294
295     function _getQueryResults($query, &$dbi) {
296         $queryResult = $dbi->genericSqlIter($query);
297         if (!$queryResult) {
298             $tbody = HTML::tbody(HTML::tr(HTML::td(_("<empty>"))));
299         } else {
300             $tbody = HTML::tbody();
301             while ($row = $queryResult->next()) {
302                 $this->_setHeaders($row);
303                 $tr = HTML::tr();
304                 foreach ($row as $value) {
305                     // output a '-' for empty values, otherwise the table looks strange
306                     $tr->pushContent(HTML::td( empty($value) ? '-' : $value ));
307                 }
308                 $tbody->pushContent($tr);
309             }
310         }
311         $queryResult->free();
312         return $tbody;
313     }
314
315     function _setHeaders($row) {
316         if (!$this->_headerSet) {
317             foreach ($row as $key => $value) {
318                 $this->_theadrow->pushContent(HTML::th(_($key)));
319             }
320             $this->_headerSet = true;
321         }
322     }
323
324     function _getWhereConditions(&$args) {
325         $where_conditions = '';
326
327         if ($args['period']<>'') {
328             $since = 0;
329             if ($args['period']=='minutes') {
330                 $since = 60;
331             } elseif ($args['period']=='hours') {
332                 $since = 60 * 60;
333             } elseif ($args['period']=='days') {
334                 $since = 60 * 60 * 24;
335             } elseif ($args['period']=='weeks') {
336                 $since = 60 * 60 * 24 * 7;
337             }
338             $since = $since * $args['count'];
339             if ($since>0) {
340                 if ($where_conditions<>'')
341                     $where_conditions = $where_conditions.' AND ';
342                 $since = time() - $since;
343                 $where_conditions = $where_conditions."time_stamp > $since";
344             }
345         }
346
347         if ($args['local_referrers']<>'true') {
348             global $request;
349             if ($where_conditions<>'')
350                 $where_conditions = $where_conditions.' AND ';
351             $localhost = SERVER_URL;
352             $len = strlen($localhost);
353             $backend_type = $request->_dbi->_backend->backendType();
354             switch ($backend_type) {
355             case 'mysql':
356                 $ref_localhost = "left(referer,$len)<>'$localhost'"; break;
357             case 'pgsql':
358             case 'postgres7':
359                 $ref_localhost = "substr(referer,0,$len)<>'$localhost'"; break;
360             default:
361                 $ref_localhost = "";
362             }
363             $where_conditions = $where_conditions.$ref_localhost;
364         }
365
366         // The assumed contract is that there is a space at the end of the
367         // conditions string, so that following SQL clauses (such as GROUP BY)
368         // will not cause a syntax error
369         if ($where_conditions<>'')
370             $where_conditions = $where_conditions.' ';
371
372         return $where_conditions;
373     }
374
375     function _getCaption(&$args) {
376         $caption = $args['caption'];
377         if ($caption=='')
378             $caption = gettext($args['mode']);
379         if ($args['period']<>'' && $args['count'])
380             $caption = $caption." - ".$args['count']." ". gettext($args['period']);
381         return $caption;
382     }
383
384 }
385
386 // Local Variables:
387 // mode: php
388 // tab-width: 8
389 // c-basic-offset: 4
390 // c-hanging-comment-ender-p: nil
391 // indent-tabs-mode: nil
392 // End:
393 ?>