Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am looking for a Linux based solution to see summary stats of web page hits by user agent or IP with ability to drill down and see data for each actual page hit from that agent and/or IP.

My primary goal is to identify bots, abusers, etc by first identifying sources of high usage, and then scanning through the details of each page hit to identify what is happening.

I have looked into (but not tested) several open source solutions but found none that state clearly if they offer detailed drill down. They all seem to offer only stats and summary data.

It seems odd because I'm looking for something quite simple; So simple in fact that I have considered just writing it myself. More or less I can simply dump the logs into MySQL and then write a few simple queries with a minimal interface for convenience. However I thought, why re-invent the wheel. Surely someone out there has done this (dozens of times) before and made the code available.

share|improve this question

migrated from stackoverflow.com Jun 26 '10 at 11:27

This question came from our site for professional and enthusiast programmers.

5 Answers 5

For simple (and not too large) log files try asql. If you need or want more sophisticated filters try Scalp!.

share|improve this answer

I had to do some basic log analysis just the other day, I didn't need any reports I just wanted to be able to query based on certain criteria. I wrote some scripts. There are probably far better/more efficient ways to do this but I just used languages I was comfortable with. This may help with some basic analysis.

apache_logger.php

    #!/usr/bin/php -q
    <?php

    function format_log_line($line) {
            preg_match("/^(\S+) (\S+) (\S+) \[([^:]+):(\d+:\d+:\d+) ([^\]]+)\] \"(\S+) (.*?) (\S+)\" (\S+) (\S+) (\".*?\") (\".*?\")$/", $line, $matches);
            return $matches;
    }

    function format_line($line) {
            $logs = format_log_line($line);
            if (isset($logs[0])) {
                    $formated_log = array();
                    $formated_log['ip'] = trim($logs[1]);
                    $formated_log['identity'] = parseVars($logs[2]);
                    $formated_log['user'] = parseVars($logs[2]);
                    $formated_log['date'] = $logs[4];
                    $formated_log['time'] = $logs[5];
                    $formated_log['timezone'] = trim($logs[6]);
                    $formated_log['method'] = parseVars($logs[7]);
                    $formated_log['path'] = parseVars($logs[8]);
                    $formated_log['protocal'] = parseVars($logs[9]);
                    $formated_log['status'] = parseVars( $logs[10]);
                    $formated_log['bytes'] = parseVars($logs[11]);
                    $formated_log['referer'] = parseVars($logs[12]);
                    $formated_log['agent'] = parseVars($logs[13]);
                    return $formated_log;
            } else {
                    return false;
            }
    }

    function parseVars($var) {
            return trim(str_replace('\\', '', str_replace('\'', '', str_replace('"', '', $var))));
    }

function create_sql($a,$table = 'access',$cont = true) {
        $time = strtotime(str_replace("/", " ", $a['date'])." ".$a['time']);

        if ( $cont ) {
                $sql = ",";
        }
        else {
                $sql = "INSERT INTO `apache_log`.`" . $table . "` VALUES ";
        }

        $sql .= "(0,'".$a['ip']."','".$a['identity']."','".$a['user']."',FROM_UNIXTIME(".$time."),'".$a['timezone']."','".$a['method']."','".$a['path']."','".$a['protocal']."','".$a['status']."','".$a['bytes']."','".$a['referer']."','".$a['agent']."')";
        return $sql;
}

$max = ( isset( $argv[3] ) ) ? $argv[3] : 100;
$file = ( isset( $argv[1] ) ) ? $argv[1] : 'access.log';
$table = ( isset( $argv[2] ) ) ? $argv[2] : 'access';
$incr = 0;

$fp = fopen($file, "r");
while (($buf = fgets($fp,1024)) != false) {
        if ( $incr >= $max )
        {
                echo create_sql(format_line($buf),$table), ";", "\n";
                $incr = 0;
        }
        else {
                if ( $incr == 0 ) {
                        echo create_sql(format_line($buf),$table, false);
                }
                else {
                        echo create_sql(format_line($buf),$table);
                }
                $incr++;
        }
}
?>

install.sql

DROP DATABASE IF EXISTS `apache_log`;
CREATE DATABASE IF NOT EXISTS `apache_log`;

CREATE TABLE `apache_log`.`access` (
 `uid` INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT 'Unique Identifier',
 `addr` VARCHAR(32) NOT NULL COMMENT 'Host Address',
 `identity` VARCHAR(64) NOT NULL COMMENT 'Identity',
 `user`  VARCHAR(64) NOT NULL COMMENT 'User',
 `date_time` DATETIME NOT NULL COMMENT '',
 `timezone` VARCHAR(32) NOT NULL COMMENT '',
 `method` VARCHAR(64) NOT NULL COMMENT 'Unique Method',
 `path` VARCHAR(1024) NOT NULL COMMENT '',
 `protocol` VARCHAR(32) NOT NULL COMMENT '',
 `status` VARCHAR(32) NOT NULL COMMENT '',
 `bytes` VARCHAR(11) NOT NULL,
 `referrer` VARCHAR(1024) NOT NULL COMMENT '',
 `agent` VARCHAR(1024) NOT NULL COMMENT ''
) ENGINE=MyISAM COMMENT '';

Run the SQL against the MySQL DB, save the php file and make it executable (requires php cli) Then to import apache log files you can do the following.

apache_logger.php [log file] [database table] [number of rows per insert]

This command will output SQL insert statements. You can pipe them directly to MySQL or redirect them to a file to import later.

This is a little crude but it works and helped me locate some interesting requests and enabled us to tune our servers better. I hope it helps.

share|improve this answer

Maybe Piwik can be useful for your needs. It really does more than giving you page hits so maybe it can be also useful for other monitoring tasks you may be interested in.

share|improve this answer

Well, it's definitely not open source, but Splunk offers excellent drill-down by source address and many other criteria.

Other than that I'd suggest using AWstats and some shell scripts.

share|improve this answer

AWStats http://awstats.sourceforge.net/

share|improve this answer
    
A simple link without explanation is not a terribly helpful answer. Can you explain how AWStats solves the specific problem described in the question? –  pjmorse Dec 14 '12 at 2:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.