Converts an apache common log to a sqlite database.
- perl (5.014)
- List::MoreUtils;
- DBI;
- Data::Dumper;
- DateTime;
- Getopt::Long;
The easiest way is just to:
perl commonLogToSQLite.pl LOG_FILE
This will create a common_log.db file. This file is a sqlite file containing all the requests from the LOG_FILE parsed and inserted.
The more complexed way:
perl commonLogToSQLite.pl --batch 100000 --db my_sqlite.db LOG_FILE
This does the same as the previous example, but it will create a database named my_sqlite.db and it will perform the insertions on the database using batches of 100000 requests.
If the database doesn't exists it will created, otherwise the requests from the log will be inserted in the database.
The database will contain only one table. The table name is REQUESTS:
- ORIGIN_IP (TEXT NOT NULL) - The IP of the requester
- USER_CLIENT (TEXT) - The user identifier
- USER (TEXT) - The user id
- TIMESTAMP (INTEGER NOT NULL) - The timestamp in unix epoch
- REQUEST_METHOD (TEXT NOT NULL) - The request method (GET, POST, PUT...)
- RESOURCE (TEXT NOT NULL) - The resource of the request
- PROTOCOL (TEXT) - The protocol (basically it's always HTTP)
- PROTOCOL_VERSION (REAL) - The version of the protocol (for HTTP protocol, it should be 1.0 or 1.1)
- STATUS (INTEGER NOT NULL) - The status code of the response (for HTTP protocol it should be 200,404,401...)
- BYTES_TRANSFERRED (INTEGER) - The size (in bytes) of the response
Please e-mail me at demanuel@ymail.com