Home / server / Questions / 439292
Accepted
icc97
Asked: 2012-10-18 03:15:54 +0800 CST

Count large file downloads from apache logs in mysql

  • 772

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:

"156.107.33.66",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?

apache-2.2

1 Answers

  1. Best Answer

    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)

    "156.107.33.66",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"
    • 3