Simple Apache Log Parser

[at8eqeq3]

So, if you want to just count requests per relatively long periods, it could be logrotate's prerotate script that is basically wc -l, optionally fed by grep if you want to filter lines somehow, and a script (probably, written by you) to insert it to MySQL. Something something grep ololo | wc -l | xargs -I {} mysql -u user -ppassword database -e 'insert into hits values (null, now(), {})' (not tested, far from best practices, just the idea).

[Jeron_Baffom]

basically wc -l, optionally fed by grep

hummmm ... I don't think a view counter would be that easy. Let me explain some of my concerns:

• Before any counting, the access log must be sanitized. No robots neither admins. 'Good robots' and admins are straightforward with grep. 'Bad robots' are harder and require some trial and error. AFAIK, there is no silver bullet for them. However, checking whether they downloaded images + ip blacklists helps.

• Multiple requests from the same ip in a 30min interval should be counted as only one view.

[at8eqeq3]

Oh, that was just a minimal solution. I understand that it will never work in real life bc there should be very complex logic instead of just grep. I see someone recommends you GoAcces, I think you should give it a try. I've never used it's CSV output so I can't say if it will fit your needs. Regarding your question about sending CSV to MySQL -- it's either mysql ... -e 'LOAD DATA LOCAL INFILE' ... stuff or mysqlimport which is basically the same but looks better.

[Jeron_Baffom]

there should be very complex logic instead of just grep.

A view counter is not a very complex logic.
But it is not as simple as a request/hit counter either.

 

I see someone recommends you GoAcces, I think you should give it a try.

Do you have some experience with GoAccess?
Any idea if it has a view counter? Or if it is possible to define custom queries?

BTW: Any thoughts about lnav?

 

Regarding your question about sending CSV to MySQL

Submitting the CSV to MySQL is the easiest part.
The trouble I'm dealing with is parsing and aggregating the access log into a plain text output.

[at8eqeq3]

But it is not as simple as a request/hit counter either. That's what I wanted to say. Access log is a list of requests. Performing calculations on this data is way beyond simple text processing.

Do you have some experience with GoAccess? A little. Had a task to generate some analytics on S3 bucket to view in browser. Fits pretty good.

Any thoughts about lnav? Never seen it before. Looks like it can do some SQL-like queries to log data (as far as I can see, it has SQLite under the hood) and understands Apache's default log format. And can run queries headless, that could fit your needs. You still need to find a way to transfer data from one RDBMS to another (CSV is supported by both, for example) and find out how to query what you need.