Sometimes you have a lot of log files that are hard to search using a text editor either because of the size or you want to do more complicated queries. You can quite easily load an Apache log file into a database to allow easy searching, and even allows you to generate useful statistics.
The info below is based on a standard Apache 2.2 installation, the log file format may have changed in newer versions of Apache, but the principles will still apply.
Create table to hold the log data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE `access_logs` ( `f1` TEXT NOT NULL, `f2` VARCHAR(45) NULL DEFAULT NULL, `f3` VARCHAR(45) NULL DEFAULT NULL, `f4` VARCHAR(45) NULL DEFAULT NULL, `f5` VARCHAR(45) NULL DEFAULT NULL, `f6` VARCHAR(45) NULL DEFAULT NULL, `f7` VARCHAR(45) NULL DEFAULT NULL, `f8` TEXT NULL, `f9` VARCHAR(45) NULL DEFAULT NULL, `f10` VARCHAR(45) NULL DEFAULT NULL, `f11` TEXT NULL, `f12` TEXT NULL ) COLLATE=‘utf8_general_ci’ ENGINE=InnoDB ; |
Import the data file
|
1 2 3 4 |
LOAD DATA INFILE ‘C:/logs/access.log_2015-02-03’ INTO TABLE access_logs FIELDS TERMINATED BY ‘ ‘ OPTIONALLY ENCLOSED BY ‘”‘ ESCAPED BY ” LINES TERMINATED BY ‘\n’; |
You can run this for multiple files if needed.
That’s it really!
Useful queries
You can query the data however you like, e.g.
Most requested files
|
1 2 3 4 |
select f8, count(f8) as hits from access_logs group by f8 order by hits desc; |
Most requested html files
|
1 2 3 4 5 |
select f8, count(f8) as hits from access_logs where f8 like ‘%htm%’ group by f8 order by hits desc; |