<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link rel="home" title="HomePage" href="HomePage.html" />
<link rel="help" title="HowToUseWiki" href="HowToUseWiki.html" />
<link rel="copyright" title="GNU General Public License" href="http://www.gnu.org/copyleft/gpl.html#SEC1" />
<link rel="author" title="The PhpWiki Programming Team" href="http://phpwiki.org/ThePhpWikiProgrammingTeam" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<meta http-equiv="Content-Language" content="en" />
<meta name="robots" content="noindex,nofollow" />
<meta name="language" content="en" />
<meta name="document-type" content="Public" />
<meta name="document-rating" content="General" />
<meta name="generator" content="PhpWiki" />
<meta name="PHPWIKI_VERSION" content="1.3.14" />

<base href="../" />
<link rel="stylesheet" type="text/css" charset="iso-8859-1" href="phpwiki-printer.css" />
<title>PhpWiki - Help  /  Analyse Access Log Sql Plugin</title>
</head>
<body>

<div id="header">
  
<!-- Page title -->
<h1>Help  /  Analyse Access Log Sql Plugin</h1>
</div>
<div id="content">



<div class="wikitext"><p class="tightenable top">The <u title="Empty link to: AnalyseAccessLogSql" onmouseover="window.status=&quot;Empty link to: AnalyseAccessLogSql&quot;; return true;" onmouseout="window.status='';return true;" class="wikiunknown">AnalyseAccessLogSql</u> plugin provides a framework to analyse the SQL access log
and display the results.
This information may be sensitive and therefore this plugin is limited to
administrator access only.</p>
<p class="tightenable">A small number of queries that may (or might not) use the MySQL
specific dialect of SQL are provided in <tt>lib/plugin/AnalyseAccessLogSql.php</tt>.
For more information, please see the section <b>Adding new queries</b> below.</p>
<h2>Usage</h2>
<p class="tightenable top">Experimental! Will change a lot.</p>
<h2>Usage</h2>
<pre class="tightenable top">
  &lt;?plugin AnalyseAccessLogSql mode=&lt;QUERY NAME&gt; ?&gt;</pre>
<h4>Parameters</h4>
<dl><dt class="tightenable top bottom">mode <i>(required parameter)</i></dt>
<dd class="tightenable top">The name of the query to execute. See <b>Adding new queries</b> below.</dd>
<dt class="tightenable bottom">period</dt>
<dd class="tightenable top">Use in combination with the <i>count</i> parameter. The type of history period to retrieve from the SQL access log. Blank (the default) for all history, or one of &quot;<b>weeks</b>&quot;, &quot;<b>days</b>&quot;, &quot;<b>hours</b>&quot;, or &quot;<b>minutes</b>&quot;.</dd>
<dt class="tightenable bottom">count</dt>
<dd class="tightenable top">Use in combination with the <i>period</i> parameter. Must be a number. The number of periods to retrieve from the access log.</dd>
<dt class="tightenable bottom">caption</dt>
<dd class="tightenable top">The value of this parameter is used as the caption for the output. If not set, the caption on the table defaults to the mode.</dd>
<dt class="tightenable bottom">center</dt>
<dd class="tightenable top">If set, the table is centered on the page, otherwise left justified.</dd>
<dt class="tightenable bottom">debugShowQuery</dt>
<dd class="tightenable top">If set, the SQL will be displayed in the table footer. Useful while debugging new queries or just for interest.</dd>
<dt class="tightenable bottom">user</dt>
<dd class="tightenable top">Blank (the default) to retrieve all users, or a specific user, or &quot;<b>&lt;NONE&gt;</b>&quot; for SQL access log entries with no user.</dd>
<dt class="tightenable bottom">host</dt>
<dd class="tightenable top">Blank (the default) to retrieve all remote_hosts, or a specific host.</dd>
<dt class="tightenable bottom">referrer</dt>
<dd class="tightenable top">Blank (the default) to retrieve all referrers, &quot;<b>&lt;NONE&gt;</b>&quot; to retrieve entries with no referrer, otherwise any referrer that matches this parameter, truncating longer referrers before matching. So a parameter of referrer='http://localhost' will match 'http://localhost/wiki/index.php', 'http://localhost/wiki/index.php/HomePage' etc.</dd>
<dt class="tightenable bottom">local_referrers</dt>
<dd class="tightenable top">If set (the default), return all referrers. If not set, return only external referring sites, excluding references from within the wiki.</dd>
</dl>
<h2>Adding new queries</h2>
<p class="tightenable top">The file <tt>lib/plugin/<u title="Empty link to: AnalyseAccessLogSql" onmouseover="window.status=&quot;Empty link to: AnalyseAccessLogSql&quot;; return true;" onmouseout="window.status='';return true;" class="wikiunknown">AnalyseAccessLogSql</u>.php</tt> contains a default set of queries against the SQL access log.
Feel free to change this file to suit your own needs, but please note that this requires filesystem access to the web server where <a href="PhpWiki.html" class="wiki">PhpWiki</a> is running.
In particular, this file may need to be modified to correctly query databases other than MySQL.
The queries supplied by default are: &quot;<b>Referring URLs</b>&quot;, &quot;<b>Referring Domains</b>&quot;, &quot;<b>Remote Hosts</b>&quot;, &quot;<b>Users</b>&quot; and &quot;<b>Host Users</b>&quot;.</p>
<p class="tightenable bottom">To add a new query, add a new section towards the end of the file, using one of the existing queries as a template. An example of an existing query is:</p>
<pre class="tightenable top">
} elseif ($mode==&quot;Users&quot;) {
    $query = &quot;SELECT &quot;
        .&quot;remote_user AS User, &quot;
        .&quot;count(*) AS 'Access Count' &quot;
        .&quot;FROM $accesslog &quot;
        .($whereConditions ? 'WHERE '.$whereConditions : '')
        .&quot;GROUP BY User&quot;;</pre>
<p class="tightenable bottom">If you do add a query, please consider contributing it back to the <a href="PhpWiki.html" class="wiki">PhpWiki</a> project at <a href="http://http://sourceforge.net/projects/phpwiki" target="" class="namedurl"><span style="white-space: nowrap"><img src="images/http.png" alt="" class="linkicon" border="0" />http://http://sourceforge.net/projects/phpwiki</span></a></p>
<h4>API for AnalyseAccessLogSql</h4>
<ul><li class="tightenable top"><p class="tightenable top bottom">input arguments:</p>
<ul><li class="tightenable top bottom"><b>$mode</b> contains the name of the query requested in the plugin parameters.</li>
<li class="tightenable top bottom"><b>$accesslog</b> contains the qualified name of the accesslog table, which may or may not be equal to &quot;accesslog&quot;.</li>
<li class="tightenable top bottom"><b>$whereConditions</b> contains the conditions (specific user, etc.) requested in the plugin parameters.</li>
</ul>
</li>
<li class="tightenable"><p class="tightenable top bottom">output:</p>
<ul><li class="tightenable top bottom"><b>$query</b> must contain the final SQL query to be executed.</li>
</ul>
</li>
<li class="tightenable"><p class="tightenable top bottom">conventions:</p>
<ul><li class="tightenable top bottom"><p class="tightenable top bottom">While constructing the query string,</p>
<ul><li class="tightenable top bottom">each clause is on a separate line and</li>
<li class="tightenable top bottom">each clause contains the required trailing punctuation or space.</li>
</ul>
</li>
<li class="tightenable top bottom">The mode parameter is, by default, displayed as the caption in the final output, so please choose a user friendly name.</li>
<li class="tightenable top bottom">The column names displayed on the wiki page are generated from the SQL column names defined in the queries, so please choose user friendly names. MySQL allows column names to include spaces.  If spaces are not allowed, then &quot;<b>_</b>&quot; is probably the next best choice.</li>
</ul>
</li>
<li class="tightenable"><p class="tightenable top bottom">language hints for PHP newbies - please remember:</p>
<ul><li class="tightenable top bottom">terminate statements with &quot;<b>;</b>&quot;,</li>
<li class="tightenable top bottom">assignment uses &quot;<b>=</b>&quot; and comparison uses &quot;<b>==</b>&quot;,</li>
<li class="tightenable top bottom">concatenate strings using the &quot;<b>.</b>&quot; operator,</li>
<li class="tightenable top bottom">the parentheses around the expression &quot;<b><tt>($whereConditions ? 'WHERE '.$whereConditions : '' )</tt></b>&quot; are essential,</li>
<li class="tightenable top bottom">strings enclosed in single quotes may not contain substrings enclosed in single quotes (actually the truth is more complex but let's leave it like that here), and</li>
<li class="tightenable top bottom">strings enclosed in double quotes will evaluate and replace variable names so that, for example, <b>&quot;FROM $accesslog &quot;</b> might be evaluated to <b>&quot;FROM phpwiki_accesslog &quot;</b>.</li>
</ul>
</li>
</ul>
<h4>Author</h4>
<p class="tightenable top bottom"><a href="http://phpwiki.sourceforge.net/phpwiki/CharlesCorrigan" class="interwiki"><span style="white-space: nowrap"><img src="images/interwiki.png" alt="" class="linkicon" border="0" />PhpWiki:</span><span class="wikipage">CharlesCorrigan</span></a> <br />
Changes by <a href="http://phpwiki.sourceforge.net/phpwiki/ReiniUrban" class="interwiki"><span style="white-space: nowrap"><img src="images/interwiki.png" alt="" class="linkicon" border="0" />PhpWiki:</span><span class="wikipage">ReiniUrban</span></a></p>
<hr />
<p class="tightenable top bottom"><a href="PhpWikiDocumentation.html" class="wiki">PhpWikiDocumentation</a> <a href="Help/WikiPlugin" class="interwiki"><span style="white-space: nowrap"><img src="images/interwiki.png" alt="" class="linkicon" border="0" />Help:</span><span class="wikipage">WikiPlugin</span></a></p>
</div>

<div id="footer">  <hr class="printer" noshade="noshade" />
<p class="editdate">Owner: &quot;admin&quot;  Last edited on August 14, 2007  7:11 am by &quot;The PhpWiki programming team&quot;</p>
</div>

</div>
<!-- Add your Disclaimer here -->
</body>
</html>
