Menu
▾
▴
RE: [Phpwiki-talk] New plugin: AnalyseAccessLog
|
From: Charles C. <ch...@ru...> - 2005-02-03 15:37:36
|
Attached is a patch to include my latest updates to this plugin. I have included the patch inline and also attached a file incase
the message gets badly mangled.
Changes:
- change parameter: mode is now queryName
- changed query names and column names to include spaces
- make it easier to add a new query
- fix use of _()
- new parameters to filter the contents of the accesslog
- new parameter to caption tables
- new parameter to display the actual SQL for debugging purposes
- make output table look nicer
TODO: provide mechanism to make it easier to put the queries in a configuration file
Regards,
Charles
Index: AnalyseAccessLog.php
===================================================================
RCS file: /cvsroot/phpwiki/phpwiki/lib/plugin/AnalyseAccessLog.php,v
retrieving revision 1.1
diff -u -r1.1 AnalyseAccessLog.php
--- AnalyseAccessLog.php 2 Feb 2005 20:41:02 -0000 1.1
+++ AnalyseAccessLog.php 3 Feb 2005 15:22:36 -0000
@@ -19,6 +19,7 @@
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
@@ -32,105 +33,97 @@
/**
* Build the query string
*
- * FIXME - some or all of these queries may be MySQL specific / non-portable
+ * 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 ($queryName=='your 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 query have something like
- * " $query= "SELECT "
- * ."referer "
- * ."FROM $accesslog "
- * ."WHERE referer IS NOT NULL "
- * .$where_conditions
+ * names, I suggest that each column in the query is given a user friendly
+ * name.
*/
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?
+ // get the correct table name
global $DBParams;
- $accesslog = (!empty($DBParams['prefix']) ? $DBParams['prefix'] : '')."accesslog";
+ $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 "
+ $queryName = $args['queryName'];
+
+ if ($queryName=='Referring URLs') {
+ $query = 'SELECT '
+ ."left(referer,length(referer)-instr(reverse(referer),'?')) AS 'Referring URL', "
+ ."count(*) AS 'Referral Count' "
."FROM $accesslog "
- .$where_conditions
- ."GROUP BY Referring_URL";
+ .($where_conditions ? 'WHERE '.$where_conditions : '')
+ ."GROUP BY 'Referring URL'";
- } elseif ($args['mode']=='referring_domains') {
- if ($where_conditions<>'')
- $where_conditions = 'WHERE '.$where_conditions.' ';
+ } elseif ($queryName=="Referring Domains") {
$query = "SELECT "
- ."left(referer, if(locate('/', referer, 8) > 0,locate('/', referer, 8) -1, length(referer))) AS Referring_Domain, "
- ."count(*) AS Referral_Count "
+ ."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";
+ .($where_conditions ? 'WHERE '.$where_conditions : '')
+ ."GROUP BY 'Referring Domain'";
- } elseif ($args['mode']=='remote_hosts') {
- if ($where_conditions<>'')
- $where_conditions = 'WHERE '.$where_conditions.' ';
+ } elseif ($queryName=="Remote Hosts") {
$query = "SELECT "
- ."remote_host AS Remote_Host, "
- ."count(*) AS Access_Count "
+ ."remote_host AS 'Remote Host', "
+ ."count(*) AS 'Access Count' "
."FROM $accesslog "
- .$where_conditions
- ."GROUP BY Remote_Host";
+ .($where_conditions ? 'WHERE '.$where_conditions : '')
+ ."GROUP BY 'Remote Host'";
- } elseif ($args['mode']=='users') {
- if ($where_conditions<>'')
- $where_conditions = 'WHERE '.$where_conditions.' ';
+ } elseif ($queryName=="Users") {
$query = "SELECT "
."remote_user AS User, "
- ."count(*) AS Access_Count "
+ ."count(*) AS 'Access Count' "
."FROM $accesslog "
- .$where_conditions
+ .($where_conditions ? 'WHERE '.$where_conditions : '')
."GROUP BY User";
- } elseif ($args['mode']=='host-users') {
- if ($where_conditions<>'')
- $where_conditions = 'WHERE '.$where_conditions.' ';
+ } elseif ($queryName=="Host Users") {
$query = "SELECT "
- ."remote_host AS Remote_Host, "
+ ."remote_host AS 'Remote Host', "
."remote_user AS User, "
- ."count(*) AS Access_Count "
+ ."count(*) AS 'Access Count' "
."FROM $accesslog "
- .$where_conditions
- ."GROUP BY Remote_Host, User";
+ .($where_conditions ? 'WHERE '.$where_conditions : '')
+ ."GROUP BY 'Remote Host', User";
}
return $query;
}
function getDefaultArguments() {
return array(
- 'mode' => 'referring_domains',
- // 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 period to report:
- // may be weeks, days, hours, minutes, or blank for all
- 'count' => '0' // the number of periods to report
- );
+ 'queryName' => '', // the name of the query to execute, see _getQueryString()
+ // "Referring Domains", "Referring URLs", "Remote Hosts",
+ // "Users", "Host Users"
+ // but note that this is subject to change
+ 'caption' => '', // blank means use the mode as the caption/title for the output
+ 'debugShowQuery' => '', // if set, show the query string in the table footer
+ '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 - must be a number
+ 'user' => '', // empty/default to retrieve all users, or a specific user or
+ // <NONE> for no user
+ 'host' => '', // empty/default to retrieve all remote_hosts, or a specific host
+ 'referrer' => '', // empty/default to retrieve all referrers,
+ // <NONE> to retrieve no referrer or
+ // otherwise matched to the start of the referrer string
+ 'local_referrers' => 'true' // if not true, return only external referring sites
+ );
}
function getName () {
@@ -143,48 +136,52 @@
function getVersion() {
return preg_replace("/[Revision: $]/", '',
- "\$Revision: 1.1 $");
+ "\$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);
+ // flag that the output is dynamic and may not be cached
+ $request->setArg('nocache',1);
if (!$request->_user->isAdmin())
- return HTML::p(_("The requested information is available only to Administrators."));
+ return HTML::p(_("The requested information is available only to administrators."));
- if (!ACCESS_LOG_SQL) // need read access
+ 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->_theadrow = HTML::tr();
$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']));
+ if (!$query)
+ return HTML::p(sprintf(_("Unrecognised 'queryName=%s'"),
+ $args['queryName']));
// get the data back.
- // Note that this must be done before the final generation ofthe table,
- // otherwise the headers will not be ready
+ // 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),
+ $caption = $this->_getCaption($args);
+
+ return HTML::table(array('border' => 1,
+ 'cellspacing' => 2,
+ 'cellpadding' => 3,
+ 'summary' => $caption),
+ HTML::caption(HTML::br(), HTML::h1($caption)),
+ HTML::thead($this->_theadRow),
+ HTML::tfoot(HTML::tr(HTML::td(array('colspan' => 100), $args['debugShowQuery'] ? $query : ''))),
$tbody);
}
function _getQueryResults($query, &$dbi) {
$queryResult = $dbi->genericSqlIter($query);
- if (!$queryResult) {
- $tbody = HTML::tbody(HTML::tr(HTML::td(_("<empty>"))));
+ if (!$queryResult or !$queryResult->count()) {
+ $tbody = HTML::tbody((HTML::tr(HTML::td(_("_ no result _")))));
} else {
$tbody = HTML::tbody();
while ($row = $queryResult->next()) {
@@ -204,7 +201,7 @@
function _setHeaders($row) {
if(!$this->_headerSet) {
foreach ($row as $key => $value) {
- $this->_theadrow->pushContent(HTML::th(_($key)));
+ $this->_theadRow->pushContent(HTML::th($key));
}
$this->_headerSet = true;
}
@@ -226,25 +223,54 @@
}
$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";
+ $where_conditions = (!$where_conditions ? '' : $where_conditions.' AND ')
+ ."time_stamp>$since";
+ }
+ if (1==2) { // this section is to ensure that the period names are available for translation
+ _("minutes"); _("hours"); _("days"); _("weeks");
+ }
+ }
+
+ $host = $args['host'];
+ if ($host)
+ $where_conditions = (!$where_conditions ? '' : $where_conditions.' AND ')
+ ."remote_host='$host'";
+
+ $user = $args['user'];
+ if ($user) {
+ if ($user=='<NONE>') {
+ $where_conditions = (!$where_conditions ? '' : $where_conditions.' AND ')
+ ."(remote_user IS NULL OR remote_user='' OR remote_user=' ' OR remote_user='-')";
+ } else {
+ $where_conditions = (!$where_conditions ? '' : $where_conditions.' AND ')
+ ."remote_user='$user'";
+ }
+ }
+
+ $referrer = $args['referrer'];
+ if ($referrer) {
+ if ($referrer=='<NONE>') {
+ $where_conditions = (!$where_conditions ? '' : $where_conditions.' AND ')
+ ."(referer IS NULL OR referer='' OR referer=' ' OR referer='-')";
+ } else {
+ $len = strlen($referrer);
+ $where_conditions = (!$where_conditions ? '' : $where_conditions.' AND ')
+ ."left(referer,$len)<>'$referrer'";
}
}
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'";
+ $where_conditions = (!$where_conditions ? '' : $where_conditions.' AND ')
+ ."left(referer,$len)<>'$localhost'";
}
- // The assumed contract is that there is a space at the end of the
- // conditions string,so that following SQL clauses (such as GROUP BY)
- // will not cause a syntax error
- if ($where_conditions<>'')
+ // 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
+ // but we want a blank string if there are no where conditions.
+ if ($where_conditions)
$where_conditions = $where_conditions.' ';
return $where_conditions;
@@ -252,10 +278,10 @@
function _getCaption(&$args) {
$caption = $args['caption'];
- if ($caption=='')
- $caption = _($args['mode']);
- if ($args['period']<>'' && $args['count'])
- $caption = $caption." - ".$args['count']." "._($args['period']);
+ if (!$caption)
+ $caption = $args['queryName'];
+ if ($args['period'] && $args['count'])
+ $caption = $caption.' - '.$args['count'].' '._($args['period']);
return $caption;
}
|
