RE: [Phpwiki-talk] New plugin: AnalyseAccessLog

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

Reini Urban wrote
> There are some more useful accesslog plugins in the works for
> which I had no time yet. RhNav (FractalNavigation) by Walter
> Rafelsberger.
> http://phpwiki.sourceforge.net/phpwiki/FractalNavigationPlugin
> Also author of the shamino_com theme.

I will have a look.  I did look at lib/plugin/RecentReferrals.php but decided it was not flexible enough for what I wanted to do.

> One note: please use _("") and not _(''), xgettext requires "

OK, I redid the plugin below, replacing _('') with _(""). One thing I am not certain of - do all calls to _() have to use "" or is
_($key) legal?

While I was re-doing the file, I also added further documentation regarding adding new queries.

New file lib/plugin/AnalyseAccessLog.php

<?php
/*
 Copyright 2005 Charles Corrigan and $ThePhpWikiProgrammingTeam

 This file is (not yet) part of PhpWiki.

 PhpWiki is free software; you can redistribute it and/or modify
 it under the terms of the GNU General Public License as published by
 the Free Software Foundation; either version 2 of the License, or
 (at your option) any later version.

 PhpWiki is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 GNU General Public License for more details.

 You should have received a copy of the GNU General Public License
 along with PhpWiki; if not, write to the Free Software
 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
 */
/**
 * A plugin that provides a framework and some useful queries to analyse the SQL
 * access log. This information may be sensitive and so is limited to
 * administrator access only.
 * 
 * To add a new query, see _getQueryString() 
 */
class WikiPlugin_AnalyseAccessLog
extends WikiPlugin
{

    /**
     * Build the query string
     * 
     * FIXME - some or all of these queries may be MySQL specific / non-portable.
     * Can someone suggest a way to detect the underlying database engine and
     * then load an engine specific set of queries?
     * 
     * FIXME - is there a more correct way to get the name of the accesslog
     * table?
     * 
     * To add a new query, add a new "} elseif { ($args['mode']=='new-query-name')"
     * section towards the end of this function, using one of the exiting queries
     * as a template. I have used the convention that, while constructing the
     * query string,
     *  - each clause is on a separate line and
     *  - each clause has the required trailing punctuation or space.
     * 
     * The column names displayed are generated from the actual query column
     * names, so make sure that each column in the query is given a user
     * friendly name. Note that the column names are passed to _() and so may be
     * translated.
     * 
     * If there are query specific where conditions, then the construction
     *     if ($where_conditions<>'')
     *         $where_conditions = 'WHERE '.$where_conditions.' ';
     * should be changed to
     *     if ($where_conditions<>'')
     *         $where_conditions = 'AND '.$where_conditions.' ';
     * and in the assignment to the query have something like
     *     $query= "SELECT "
     *         ."referer "
     *         ."FROM $accesslog "
     *         ."WHERE referer IS NOT NULL "
     *         .$where_conditions
     */
    function _getQueryString(&$args) {
        // extract any parametrised conditions from the arguments,
        // in particular, how much history to select
        $where_conditions = $this->_getWhereConditions($args);

        // get the correct name for the table
        //FIXME is there a more correct way to do this?
        global $DBParams;
        $accesslog = (!empty($DBParams['prefix']) ? $DBParams['prefix'] : '')."accesslog";

        $query = '';
        if ($args['mode']=='referring_urls') {
            if ($where_conditions<>'')
                $where_conditions = 'WHERE '.$where_conditions.' ';
            $query = "SELECT "
                ."left(referer,length(referer)-instr(reverse(referer),'?')) AS Referring_URL, "
                ."count(*) AS Referral_Count "
                ."FROM $accesslog "
                .$where_conditions
                ."GROUP BY Referring_URL";

        } elseif ($args['mode']=='referring_domains') {
            if ($where_conditions<>'')
                $where_conditions = 'WHERE '.$where_conditions.' ';
            $query = "SELECT "
                ."left(referer, if(locate('/', referer, 8) > 0, locate('/', referer, 8) -1, length(referer))) AS Referring_Domain, "
                ."count(*) AS Referral_Count "
                ."FROM $accesslog "
                .$where_conditions
                ."GROUP BY Referring_Domain";

        } elseif ($args['mode']=='remote_hosts') {
            if ($where_conditions<>'')
                $where_conditions = 'WHERE '.$where_conditions.' ';
            $query = "SELECT "
                ."remote_host AS Remote_Host, "
                ."count(*) AS Access_Count "
                ."FROM $accesslog "
                .$where_conditions
                ."GROUP BY Remote_Host";

        } elseif ($args['mode']=='users') {
            if ($where_conditions<>'')
                $where_conditions = 'WHERE '.$where_conditions.' ';
            $query = "SELECT "
                ."remote_user AS User, "
                ."count(*) AS Access_Count "
                ."FROM $accesslog "
                .$where_conditions
                ."GROUP BY User";

        } elseif ($args['mode']=='host-users') {
            if ($where_conditions<>'')
                $where_conditions = 'WHERE '.$where_conditions.' ';
            $query = "SELECT "
                ."remote_host AS Remote_Host, "
                ."remote_user AS User, "
                ."count(*) AS Access_Count "
                ."FROM $accesslog "
                .$where_conditions
                ."GROUP BY Remote_Host, User";
        }
        return $query;
    }

    function getDefaultArguments() {
        return array(
            'mode'              => 'referring_domains',
                                            // see _getQueryString(), currently one of
                                                // referring_domains, referring_urls, remote_hosts, users, host-users
            'caption'           => '',      // blank means use the mode as the caption/title for the output
            'local_referrers'   => 'true',  // only show external referring sites
            'period'            => '',      // the type of history period to retrieve, see _getWhereConditions()
                                                // may be weeks, days, hours, minutes, or blank for all
            'count'             => '0'      // the number of periods to retrieve
            );
    }

    function getName () {
        return _("AnalyseAccessLog");
    }

    function getDescription () {
        return _("Show summary information from the access log table.");
    }

    function getVersion() {
        return preg_replace("/[Revision: $]/", '',
                            "\$Revision: 0.1 $");
    }

    function run($dbi, $argstr, &$request, $basepage) {
        // flag that the output may not be cached - i.e. it is dynamic
        $request->setArg('nocache',1);

        if (!$request->_user->isAdmin())
            return HTML::p(_("The requested information is available only to administrators."));

        if (!ACCESS_LOG_SQL==2)
            return HTML::p(_("The SQL_ACCESS_LOG is not enabled."));

        // set aside a place for the table headers, see _setHeaders()
        $this->_headerSet = false;
        $this->_theadRow = HTML::tr();

        $args = $this->getArgs($argstr, $request);

        $query = $this->_getQueryString($args);

        if ($query=='')
            return HTML::p(sprintf( _("Unrecognised parameter 'mode=%s'"), $args['mode']));

        // get the data back.
            // Note that this must be done before the final generation of the html table,
            // otherwise the headers will not be ready
        $tbody = $this->_getQueryResults($query, $dbi);

        return HTML::table(array(   'border'        => 1,
                                    'cellspacing'   => 1,
                                    'cellpadding'   => 1),
                    HTML::caption(HTML::h1(HTML::br(),$this->_getCaption($args))),
                    HTML::thead($this->_theadRow),
                    $tbody);
    }

    function _getQueryResults($query, &$dbi) {
        $queryResult = $dbi->genericSqlIter($query);
        if (!$queryResult) {
            $tbody = HTML::tbody((HTML::tr(HTML::td(_("_nothing_")))));
        } else {
            $tbody = HTML::tbody();
            while ($row = $queryResult->next()) {
                $this->_setHeaders($row);
                $tr = HTML::tr();
                foreach ($row as $value) {
                    // output a '-' for empty values, otherwise the table looks strange
                    $tr->pushContent(HTML::td( empty($value) ? '-' : $value ));
                }
                $tbody->pushContent($tr);
            }
        }
        $queryResult->free();
        return $tbody;
    }

    function _setHeaders($row) {
        if(!$this->_headerSet) {
            foreach ($row as $key => $value) {
                $this->_theadRow->pushContent(HTML::th(_($key)));
            }
            $this->_headerSet = true;
        }
    }

    function _getWhereConditions(&$args) {
        $where_conditions = '';

        if ($args['period']<>'') {
            $since = 0;
            if ($args['period']=='minutes') {
                $since = 60;
            } elseif ($args['period']=='hours') {
                $since = 60 * 60;
            } elseif ($args['period']=='days') {
                $since = 60 * 60 * 24;
            } elseif ($args['period']=='weeks') {
                $since = 60 * 60 * 24 * 7;
            }
            $since = $since * $args['count'];
            if ($since>0) {
                if ($where_conditions<>'')
                    $where_conditions = $where_conditions.' AND ';
                $since = time() - $since;
                $where_conditions = $where_conditions."time_stamp>$since";
            }
        }

        if ($args['local_referrers']<>'true') {
            if ($where_conditions<>'')
                $where_conditions = $where_conditions.' AND ';
            $localhost = SERVER_URL;
            $len = strlen($localhost);
            $where_conditions = $where_conditions."left(referer,$len)<>'$localhost'";
        }

        // the assumed contract is that there is a space at the end of the condtions string,
        // so that following SQL clauses (such as GROUP BY) will not cause a syntax error
        if ($where_conditions<>'')
            $where_conditions = $where_conditions.' ';

        return $where_conditions;
    }

    function _getCaption(&$args) {
        $caption = $args['caption'];
        if ($caption=='')
            $caption = _($args['mode']);
        if ($args['period']<>'' && $args['count'])
            $caption = $caption." - ".$args['count']." "._($args['period']);
        return $caption;
    }

}
?>