Simone 'Wiz' Tellini (Entries tagged as logminer)

LogMiner 1.23

nospam@example.com (Simone) — Tue, 30 Jun 2009 10:42:32 -0700

Version 1.23 of LogMiner, my Apache/IIS log analysis package, is now available.

This release mainly fixes some build problem on modern distributions.

Also it finally includes the cleanup function I was talking about some days ago...

Who's linking to your site?

nospam@example.com (Simone) — Fri, 20 Jun 2008 14:57:28 -0700

If you're using LogMiner to analyse your Apache/IIS logs, here's an easy recipe to be notified of sites that send visitors your way.

First, let's define a Pg/PLSQL function to extract the new referrers:

CREATE TYPE new_referrer AS (
site_id int8,
site_name text,
referrer text,
hits int
);

CREATE OR REPLACE FUNCTION get_new_referrers()
RETURNS SETOF new_referrer AS $body$
DECLARE
rec new_referrer;
lastRef int8;
BEGIN

SELECT INTO lastRef CAST( value AS int8 )
FROM catalog
WHERE name = 'LastReferrer';

IF lastRef IS NULL THEN
INSERT INTO catalog ( name, value ) VALUES ( 'LastReferrer', 0 );
lastRef := 0;
END IF;

FOR rec IN
EXECUTE 'SELECT s.id AS site_id, s.name AS site_name, r.referrer, tmp.hits ' ||
'FROM ( ' ||
' SELECT site, referrer, COUNT(*) AS hits ' ||
' FROM accesses ' ||
' WHERE referrer > ' || lastRef ||
' GROUP BY site, referrer ' ||
') tmp ' ||
'JOIN sites s ON s.id = tmp.site ' ||
'JOIN referrers r ON tmp.referrer = r.id ' ||
'WHERE r.extern = true ' ||
'ORDER BY s.name, tmp.hits DESC, r.referrer'
LOOP

RETURN NEXT rec;

END LOOP;

UPDATE catalog
SET value = ( SELECT MAX( id ) FROM referrers )
WHERE name = 'LastReferrer';

RETURN;
END;
$body$ LANGUAGE plpgsql;

Then, a little PHP script to run the query and format the results:

#!/usr/local/bin/php
<?php

$lines = array();
$lastSite = '';

// quickest and dirtiest way to execute a query in PHP ;-)
exec ( "echo 'SELECT r.* " .
"FROM get_new_referrers() r " .
"LEFT JOIN search_engines se ON ( r.referrer ~* se.pattern AND r.referrer ~* se.query_pattern )" .
"WHERE se.id IS NULL' | /usr/local/pgsql/bin/psql -U logminer -At logminer",
$lines );

foreach( $lines as $line ) {

$parts = explode( '|', $line, 4 );

if( $lastSite != $parts[ 0 ] ) {

printf( "\n\n\n\n" .
"-------------------------------------------------------\n" .
"Referrers for %s\n" .
"-------------------------------------------------------\n\n",
$parts[ 1 ] );

$lastSite = $parts[ 0 ];
}

printf( "[%d] %s\n\n", $parts[ 3 ], $parts[ 2 ] );
}
?>

The query above grabs the referrers for every site available in the DB, filtering requests coming from known search engines to reduce the level of "noise".

This script is finally invoked by the cron job that processes the logs:

referrers_report.php | mail -s 'New referrers report' email@example.com

How much would it cost to rewrite it?

nospam@example.com (Simone) — Mon, 02 Jun 2008 17:13:44 -0700

I've just added LogMiner to Ohloh, a site that offers an interesting feature: it can analyse a project source code and estimate how much it would cost to hire a development team to recreate the project from scratch.

I think that it's a simple way to estimate the effort you put over the time in open source projects.

Here's the resulting figure for LogMiner:

LogMiner: purging old accesses

nospam@example.com (Simone) — Sat, 26 Jan 2008 18:53:22 -0800

I've just committed a function to easily purge old data from LogMiner's database, useful to prevent it from growing too much.

It'll appear in the next version, but since its release might still be far from now, here's the code for those who need it:

CREATE OR REPLACE FUNCTION cleanup( _site int8, _upToDate date )
RETURNS INT AS $body$
BEGIN

DELETE FROM accesses
WHERE req_time < _upToDate
AND site = _site;

ALTER TABLE accesses DROP CONSTRAINT accesses_request_fkey;
ALTER TABLE accesses DROP CONSTRAINT accesses_search_fkey;
ALTER TABLE accesses DROP CONSTRAINT accesses_referrer_fkey;

DELETE FROM requests
WHERE id NOT IN ( SELECT DISTINCT request FROM accesses );

DELETE FROM search_referrals
WHERE id NOT IN ( SELECT DISTINCT search FROM accesses );

DELETE FROM referrers
WHERE id NOT IN ( SELECT DISTINCT referrer FROM accesses );

ALTER TABLE accesses
ADD CONSTRAINT "accesses_request_fkey"
FOREIGN KEY ( request )
REFERENCES requests( id ) ON DELETE CASCADE;

ALTER TABLE accesses
ADD CONSTRAINT "accesses_search_fkey"
FOREIGN KEY ( search )
REFERENCES search_referrals( id ) ON DELETE SET NULL;

ALTER TABLE accesses
ADD CONSTRAINT "accesses_referrer_fkey"
FOREIGN KEY ( referrer )
REFERENCES referrers( id ) ON DELETE SET NULL;

RETURN 0;
END;
$body$ LANGUAGE plpgsql;

For instance, if you want to keep only the last six month of data, you can set up a cron job which runs at the first day of every month executing the command:

# supposing 1 is the id of your site
echo "SELECT * FROM cleanup( 1, CAST( date_trunc( 'month', now() ) - interval '5 months' AS date ));" | psql -U logminer logminer

Mac OS X version statistics through CFNetwork analysis

nospam@example.com (Simone) — Fri, 07 Dec 2007 12:05:00 -0800

Recently I needed to gather some statistics about the distribution of the different Mac OS X versions installed by users of a certain application.

Since the application has an update-check feature, I thought I'd use the web server logs to infer the data I was interested in. The only thing I could use was the User-Agent string sent by the application when requesting the file containing the update information.

After some digging in Darwin's build plists and a bit of googling, I came up with this list of patterns:



            OS            |                user agent pattern

--------------------------+---------------------------------------------------

 Mac OS X 10.2            | ^CFNetwork/1\.1$

 Mac OS X 10.3.2-10.3.8   | ^CFNetwork/1\.2\.1$

 Mac OS X 10.3.9          | ^CFNetwork/1\.2\.[2-6]$

 Mac OS X 10.4            | ^CFNetwork/128$

 Mac OS X 10.4.10         | ^CFNetwork/129\.21$

 Mac OS X 10.4.11         | ^CFNetwork/129\.22$

 Mac OS X 10.4.2          | ^CFNetwork/128\.2$

 Mac OS X 10.4.3          | ^CFNetwork/(129\.5|10\.4\.3)$

 Mac OS X 10.4.4          | ^CFNetwork/(129\.(9|10)|10\.4\.4)$

 Mac OS X 10.4.5          | ^CFNetwork/129\.11$

 Mac OS X 10.4.6          | ^CFNetwork/129\.13$

 Mac OS X 10.4.7          | ^CFNetwork/(129\.16|4\.0)$

 Mac OS X 10.4.8          | ^CFNetwork/129\.1(8|9)$

 Mac OS X 10.4.9          | ^CFNetwork/129\.20$

 Mac OS X 10.5            | ^CFNetwork/21[7-9]$

 Mac OS X 10.5.1          | ^CFNetwork/220$

 Mac OS X 10.5-prerelease | ^CFNetwork/1[4-9][0-9](\.[0-9])?|20[0-9]|21[0-9]$

They might not be 100% correct, but they're good enough.

LogMiner 1.21

nospam@example.com (Simone) — Sat, 03 Nov 2007 13:26:58 -0700

Version 1.21 of LogMiner, my Apache/IIS log analysis package, is now available.

This release sports a new report: Networks.

It allows you to monitor where your visitor come from, in terms of organization and network. To use this report, you need to define a list of organizations and a list of networks owned by them.

For instance, if you wish to check how many hits you get from Google's crawlers, you can define a Crawler network owned by Google with the address 66.249.64.0/19 (this is just an example and may not be entirely correct, although my logs always show GoogleBot coming from that IP range).

LogMiner 1.20

nospam@example.com (Simone) — Fri, 10 Aug 2007 14:25:00 -0700

Version 1.20 of LogMiner, my Apache/IIS log analysis package, is now available.

This version doesn't require anymore libpqxx 2.5.5: it compiles as well with version 2.6.9 (I don't understand why they had to remove a couple of really useful functions in the 2.6.x series, though).

The other most important change is that in case of SQL errors while inserting data into the DB, LogMiner won't abort the parsing of the log. Instead, it will record the error and skip the log line that caused the problem. This fix has actually been inspired by lame spammers who keep on trying to exploit the contact page on this site; too bad that they didn't study HTTP well enough: the content of a POST request follows the headers, it doesn't precede the method. They were causing LogMiner to try to fit the spam body into the varchar(32) method column.