Simone 'Wiz' Tellini - Entries tagged as iis

Friday, June 20. 2008

Who's linking to your site?

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

Posted by Simone in SysAdmin at 14:57 | Comments (0) | Trackbacks (0)

299 hits

Defined tags for this entry: apache, iis, logminer, logs, referrers, sysadmin

Friday, August 10. 2007

LogMiner 1.20

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.

Posted by Simone in Software at 14:25 | Comments (0) | Trackbacks (0)

321 hits

Defined tags for this entry: analysis, apache, iis, logminer, logs, software

(Page 1 of 1, totaling 2 entries)

About me

Born in Guastalla the 14th of February, 1978; since I was a child I've always enjoyed disassembling (and re-assembling, often succesfully) everything I could put my hands on.

Eventually I ended up disassembling and re-assembling software... it's not exactly the same thing as dismantling toys, but it can be equally funny

Nota per i connazionali: la maggior parte di questo sito è in inglese, in modo da poter essere compreso dalla maggior parte delle persone che passano di qui.