Apache log analysis with Pandas¶

Nikolay Koldunov
koldunovn@gmail.com

koldunov.net

In this notebook, I am going to show simple example of an Apache access log analysis with pandas. It's my first experience with pandas, and I am sure there are better and more efficient ways to do some of the things shown here. So comments, suggestions and corrections of my broken English are very welcome. You can send me an email, or create a PR for this notebook at github.

Load and parse the data¶

We will need apachelog module, for parsing the log. We also have to know the format of the log that is set in the Apache config. In my case I have no access to the Apache config, but the hoster provide description of the format on his help page. Below is the format itself and a short description of every element:

format = r'%V %h  %l %u %t \"%r\" %>s %b \"%i\" \"%{User-Agent}i\" %T'

Here (copied mostly from this SO post):

%V          - the server name according to the UseCanonicalName setting
%h          - remote host (ie the client IP)
%l          - identity of the user determined by identd (not usually used since not reliable)
%u          - user name determined by HTTP authentication
%t          - time the server finished processing the request.
%r          - request line from the client. ("GET / HTTP/1.0")
%>s         - status code sent from the server to the client (200, 404 etc.)
%b          - size of the response to the client (in bytes)
\"%i\"      - Referer is the page that linked to this URL.
User-agent  - the browser identification string
%T          - Apache request time  

Set the format:

Create the parcer:

Sample string:

    koldunov.net 85.26.235.202 - - [16/Mar/2013:00:19:43 +0400] "GET /?p=364 HTTP/1.0" 200 65237 "http://koldunov.net/?p=364" "Mozilla/5.0 (Windows NT 5.1) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.64 Safari/537.11" 0

Looks like the parser works. Now to load some real world data (sample file located here and here):

Parse every line of it and create a list of dictionaries:

We had to tweak the time format a bit, since otherwise pandas will not be able to parse it.

Create and tune Data Frame¶

This will create a list of dictionaries, that can be transformed in to a Data Frame:

Show the first two lines of the Data Frame:

We are not going to use all the data, so let's delete some of the columns:

and rename columns to something that humans can understand:

First five rows of resulting Data Frame:

Convert Time column to datetime format and make an index out of it (pop will drop original Time column):

The Status variable is a string type, so we have to change it to int:

Some of the rows in the b column contain '-' string, so we can't convert them with astype:

We can apply a custom function to this column, that will convert all dashes to NaN, and the rest to floats, additionally converting from bytes to megabytes:

I am sure there is a more elegant way to do this :)

Traffic analysis¶

Our first, simplest plot: outgoing traffic from the website:

Look's like somebody downloaded something big from the website around 9 a.m.

But actually the first thing that you probably want to know is how many visits (actually hits) your site has, and how they are distributed in time. We resample the series of variable b with 5 minute intervals, and calculate number of requests during every time span. Actually, in this case it doesn't matter what variable we use, these numbers will indicate just how many times information from the the website was requested.

We can not only count number of requests per time, but also calculate the sum of the traffic for every time span:

On the plot you can see, that the number of server requests do not always coincide with the amount of traffic, and correlation is actually not extremely high:

We can have a closer look at the curious morning peak:

Seems that this traffic spike was caused by only one request. Let's find out how this request looks. Choose all requests with the size of the response larger than 20 Mb (remember we convert bytes to Mb):

It was a .pdf file of a book (look at Request field), and this explains the peak in outgoing trafic at 2013-03-16 09:02:59.

Clearly 20 Mb is a large request (for our website at least :)). But what is the typical size of the server's response? The histogram of response sizes (less than 20 Mb) looks like this:

So, most of the files are less than 0.5 Mb large. In fact they are even smaller:

Very small responses can be all kinds of stuff, but larger ones are probably some relatively big files. Let's have a look at these little spikes between 0.2 and 0.25 Mb:

We now can locate the size of the file more precisely, and find out its name:

This is one of the image files from the front page of the website.

Server status codes¶

I would like to know how different server responses are distributed through time. Let's first try a not very elegant approach. We create several variables with time series of Status values, each containing only a time series with one particular value. Then a Data Frame is created out of this time series.

We plot all values at once:

Show only Error and Redirection status codes as a stacked plot:

But there is a better way to receive statistics about different groups of values. Here we group our data by Status:

Data are now rearranged in to several groups, each corresponding to a certain value of Status (here, only the first two values of every group are shown):

We can count the number of values in each group:

We can look at individual groups:

IPs¶

Now I want to group by IPs and calculate how many requests we get from each of them:

I don't want to see all of them, just the top 10:

I just want to have a nice table with the top 10 IPs, so I convert them to Data Frame :)

We also can group by both IP and Status:

Geographical information¶

I would like to know from which countries people or robots come to the website. It turns out that precise geolocation information is not free, but for our purposes free data bases are fine. We will use pygeoip to get information from GeoIP database, and that has to be downloaded from here.

Import module and initialise data base:

This is how output of the pygeoip looks (we test our top IP from the previous section):

Guess who it is :) Looks like Google Bot is my main reader. That's rather sad :(

Loop to create a list of dictionaries with information about IP numbers. We also add information about the number of requests from each of the IPs:

Create Data Frame from this list, use ips index:

Group by Country:

The website is in Russian, so no surprises here.

Group by city: