sonickun.log

備忘録

Nginxのアクセスログを自動でSQLiteに出力する

 先日Nginxを使ってWEBサーバを立ち上げたのだが,アクセスログを解析しやすいようにデータベースに突っ込みたかったので自分で実装してみた.sonickun.hatenablog.com

 Apacheのログなどをデータベースに出力するツールとしてfluentd等があるようだが,導入が面倒そうだったのと,自分で自由にカスタマイズしたかったという理由で今回はpythonで書いてみる.

Nginxのアクセスログ

 /etc/nginx/nginx.confによれば,Nginxのアクセスログのフォーマットは以下のようになっている.

log_format main    '$remote_addr - $remote_user [$time_local] "$request" '
                   '$status $body_bytes_sent "$http_referer"'
                   '"$http_user_agent" "$http_x_forwarded_for"';

 出力されたログの例は以下のとおり.

AA.BB.CC.DD - - [07/Apr/2015:03:36:34 +0000] "GET / HTTP/1.1" 
200 1913 "http://sonickun.hatenablog.com/" 
"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.101 Safari/537.36" "-"

 また,ログファイルは以下のように1日毎に分割され,gzipで圧縮して/var/log/nginx/下に保存される.

# ls /var/log/nginx/
access.log-20150405.gz 
access.log-20150406.gz 
access.log-20150407.gz 
access.log-20150408.gz 
access.log-20150409 

 

実装

 まずはログを格納するSQLiteデータベース(log.db)を/var/log/nginx/db/下に作成する.

CREATE TABLE log(
    ip,
    remotelog,
    remoteuser,
    date,
    time,
    method,
    url,
    status,
    byte,
    refer,
    agent,
    xforwarded
);

 次に,ログファイルを読み込んでデータベースに出力するプログラムを作成する.

parse.py
import sqlite3
import datetime
import gzip

def parse(log, db):
    try:
        f = gzip.open(log, "rb") # decompress gzip
        conn = sqlite3.connect(db)
        cursor = conn.cursor()

        for line in f:
            data = []
            a = line.split('"')
            line = line.split()
            #ip
            data.append(line[0])
            #remotelog
            data.append(line[1])
            #remoteuser
            data.append(line[2])
            #date (ex. "07/Apr/2015" -> "2015/04/07")
            date = datetime.datetime.strptime(line[3][1:line[3].index(":")],"%d/%b/%Y")
            data.append(date.strftime("%Y/%m/%d"))
            #time
            data.append(line[3][line[3].index(":") + 1:])
            #method
            data.append(a[1].split()[0])
            #url
            data.append(" ".join(a[1].split()[1:]))
            #status
            data.append(line[8]) 
            #byte
            data.append(line[9])
            #refer
            data.append(a[-6])
            #agent
            data.append(a[-4])
            #x forwarded for
            data.append(a[-2])
    
            try:
                cursor.execute( "insert into log values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", 
                    (data[0], data[1], data[2], data[3], 
                        data[4], data[5], data[6], data[7],
                        data[8], data[9], data[10], data[11]) )
            except Exception as e:
                print str(e)
        conn.commit()
        cursor.close()
        conn.close()
        f.close()
        print "Complete."
    except Exception as e:
        print str(e)

if __name__ == "__main__":
    # extract filename
    d = datetime.datetime.now()
    d = d - datetime.timedelta(days=1)
    d = d.strftime("%Y%m%d")

    log = "/var/log/nginx/access.log-" + d + ".gz"
    db = "/var/log/nginx/db/log.db"
    
    print log + " -> " + db,

    parse(log, db)

 ログファイルは収集した翌日の午前3時過ぎころに圧縮され保存されるようなので,このプログラムを毎日午前5時ころに実行するようにする.以下のようにparse.shを作成する.

parse.sh
#!/bash/sh
python parse.py

 これをcronを使って実行をスケジューリングする./etc/crontabを以下のように編集する.

/etc/crontab
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/

0 5 * * * root /var/log/nginx/src/parse.sh

 これにより,毎日午前5時にparse.shが実行される.

 

出力結果

 Nginxのログがデータベースに出力されているので,中身を確認してみる.

# sqlite3 log.db
sqlite> select * from log limit 5;
AA.BB.CC.DD|-|-|2015/04/07|03:36:34|GET|/ HTTP/1.1|200|1913|http://sonickun.hatenablog.com/|Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.101 Safari/537.36|-
AA.BB.CC.DD|-|-|2015/04/07|03:36:34|GET|/css/vendor/fluidbox.min.css HTTP/1.1|200|346|http://sonickun.xyz/|Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.101 Safari/537.36|-
AA.BB.CC.DD|-|-|2015/04/07|03:36:34|GET|/css/main.css HTTP/1.1|200|2753|http://sonickun.xyz/|Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.101 Safari/537.36|-
AA.BB.CC.DD|-|-|2015/04/07|03:36:34|GET|/js/vendor/jquery.fluidbox.min.js HTTP/1.1|200|1526|http://sonickun.xyz/|Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.101 Safari/537.36|-
AA.BB.CC.DD|-|-|2015/04/07|03:36:34|GET|/js/main.js HTTP/1.1|200|140|http://sonickun.xyz/|Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.101 Safari/537.36|-

 ログがデータベースに格納されている事がわかる.これで任意のSQL文を実行してさまざまな統計を行うことができるようになった.