lib/plugin/AnalyseAccessLogSql.php

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