I want to count file downloads for about 10 different files that a client has asked for. We've generated a separate log format for the apache logs that can be loaded into a MySQL database.

For such a specific query I was hoping to be able to write a SQL statement to count the downloads rather than installing an over-bloated log analyser.

This is the log format used for the MySQL logs:

LogFormat "\"%a\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\", \"%U\",\"%{Referer}i\",\"%{User-Agent}i\"" mysql

This produces this kind of csv file:

"",20121017113516,200,"10094440","application/x-msdos-program",          "/flexnet/FLEXnetWinSetup.exe","http://www.example.com/downloads/flexnet","Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.4 (KHTML, like Gecko) Chrome/22.0.1229.94 Safari/537.4"

But the larger 100MB+ files are split into chunks so its not a simple count of the logs.

Has anyone tried something similar?


If by split into chuncks you means that clients are donwloading the files with multiple 206 HTTP responses then you should try to log the content of header Content-Range included into the reply :

LogFormat "\"%a\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Range}o\",\"%{Content-Type}o\", \"%U\",\"%{Referer}i\",\"%{User-Agent}i\"" mysql

This should produces something like this (not tested)

"",20121017113516,200,"10094440","bytes 0-999/3980","application/x-msdos-program",          "/flexnet/FLEXnetWinSetup.exe","http://www.example.com/downloads/flexnet","Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.4 (KHTML, like Gecko) Chrome/22.0.1229.94 Safari/537.4"
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.