#!/usr/local/bin/python3
# coding:utf-8

# ====================================================
# Author: chang - EMail:changbo@hmg100.com
# Last modified: 2017-5-13
# Filename: accesstimes.py
# Description: real time analysis nginx log,base time, os, re, pymysql, Thread 
# blog:http://www.cnblogs.com/changbo
# ====================================================

"""
需求：每隔1分钟读取nginx日志文件
notice:
模拟日志切割过程中初始化脚本参数
cp access.log access2017xxxx.log  && echo > access.log && echo ‘0‘> offset1.txt
"""


import time
import os
import re
import pymysql
from threading import Thread
import logging
# from django.db import connection

logging.basicConfig(level=logging.DEBUG,
                    format=‘%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s‘,
                    datefmt=‘%a, %d %b %Y %H:%M:%S‘,
                    filename=‘accesstimes.log‘,
                    filemode=‘w‘)

keypage = [‘/sys/get_user.do‘]
keyIP = [‘127.0.0.1‘, ‘119.28.61.122‘]
engdate = {‘Jan‘: ‘1‘, ‘Feb‘: ‘2‘, ‘Mar‘: ‘3‘, ‘Apr‘: ‘4‘, ‘May‘: ‘5‘, ‘Jun‘: ‘6‘, ‘Jul‘: ‘7‘, ‘Aug‘: ‘8‘, ‘Sept‘: ‘9‘,
           ‘Oct‘: ‘10‘, ‘Nov‘: ‘11‘, ‘Dec‘: ‘12‘}


def dateformat(nginxdate):
    day = (nginxdate.split(‘[‘)[1]).split(‘/‘)[0]
    month = engdate[(nginxdate.split(‘[‘)[1]).split(‘/‘)[1]]
    year = (nginxdate.split(‘[‘)[1]).split(‘/‘)[2]

    return year + ‘-‘ + month + ‘-‘ + day


# write log offset
def writeoffset(number):
    with open(‘offset1.txt‘, ‘w+‘) as f3:
        f3.write(number)
        f3.flush()


# get log offset
def getoffset():
    with open(‘offset1.txt‘) as f2:
        offset = f2.readline()
        return offset

db = pymysql.connect("xxx.xxx.xxx.xxx", "xxx", "xxxxxx", "yunwei")
cursor = db.cursor()
# cleantable = ‘TRUNCATE abnormal‘


def handleline(logline):
    susptmp = logline.split(" ")
    if len(susptmp) > 2:
        if susptmp[0] not in keyIP:
            del susptmp[1:3]
            if len(susptmp) > 2:
                ip = susptmp[0]
                time1 = (susptmp[1].split(‘:‘, 1))[1]
                dated = dateformat((susptmp[1].split(‘:‘, 1))[0])

                # print(ip + ‘---‘, time1 + ‘---‘, date + ‘---‘, user + ‘---‘, passd + ‘---‘)
                sql = "INSERT INTO reqinfo(ip, timedd, datedd) VALUES(‘%s‘, ‘%s‘, ‘%s‘)" % (ip, time1, dated)
                try:
                    cursor.execute(sql)
                    db.commit()
                    logging.debug(‘Insert success!‘)
                except Exception as e:
                    logging.debug(e)


# online analysis log
def analysislog():
    with open(‘access.log‘) as f1:
        while True:
            # get offset
            lastoffset = getoffset()
            # jump the Specify log line
            f1.seek(int(lastoffset))
            # 获取该行偏移量
            where = f1.tell()
            line = f1.readline()
            writeoffset(str(where))
            if not line:
                time.sleep(10)
                f1.seek(where)
            else:
                # 处理该行，并获取改行的偏移量且写入文件
                handleline(line)
                nowoffset = f1.tell()
                writeoffset(str(nowoffset))

if __name__ == ‘__main__‘:
    if not os.path.exists(‘offset1.txt‘):
        with open("offset1.txt", ‘w‘) as f:
            f.write(‘0‘)

    t1 = Thread(target=analysislog)
    t1.start()

#!/usr/local/bin/python3
# coding:utf-8

# ====================================================
# Author: chang - EMail:changbo@hmg100.com
# Last modified: 2017-5-13
# Filename: reqcounts.py
# Description: real time analysis nginx log,pymysql, Thread, logging
# blog:http://www.cnblogs.com/changbo
# ====================================================


import pymysql
import logging
from threading import Thread

logging.basicConfig(level=logging.DEBUG,
                    format=‘%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s‘,
                    datefmt=‘%a, %d %b %Y %H:%M:%S‘,
                    filename=‘reqcounts.log‘,
                    filemode=‘w‘)


db = pymysql.connect("xxx.xxxx.xxx.xxx", "xxxx", "xxxxx", "xxxxx")
cursor = db.cursor()
# cleantable = ‘TRUNCATE tablename‘


def analysisdb():
    listtime = []
    listip = []
    listdated = []

    sql3 = ‘SELECT ip, timedd, datedd FROM reqinfo‘
    cursor.execute(sql3)
    results = cursor.fetchall()
    for row in results:
        listtime.append(row[1])
        listip.append(row[0])
        listdated.append(row[2])

        try:
            # 统计1分钟内页面访问次数
            sql1 = "SELECT count(*) from reqinfo where timedd=‘%s‘ and ip=‘%s‘ and datedd=‘%s‘" % (
                listtime[0], listip[0], listdated[0])
            sql4 = "DELETE from reqinfo where timedd=‘%s‘ and ip=‘%s‘ and datedd=‘%s‘" % (
                listtime[0], listip[0], listdated[0])
            sql5 = "DELETE FROM reqcounts WHERE timesddd=0"
            cursor.execute(sql1)
            datad = cursor.fetchone()
            sql2 = "INSERT INTO reqcounts(ip, timesddd, timeddd, dateddd) VALUES(‘%s‘ , ‘%s‘ , ‘%s‘, ‘%s‘)" % (
            listip[0], datad[0], listtime[0], listdated[0])
            cursor.execute(sql2)
            db.commit()
            logging.debug(‘-----Insert success -------‘)
            # delete already insert data of requinfo
            cursor.execute(sql4)
            db.commit()
            del listtime[0]
            del listip[0]
            del listdated[0]
            cursor.execute(sql5)
            db.commit()
        except Exception as e:
            logging.debug(e)

if __name__ == ‘__main__‘:
        t2 = Thread(target=analysisdb)
        t2.start()

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    {% load static %}
    <link rel="stylesheet"  type="text/css" href="{% static "css/style.css" %}" />
    <title>访问统计</title>
<script language="javascript" type="text/javascript">
window.onload = function(){
    var oTable = document.getElementById("bbsTab");
    for(var i=0;i<oTable.rows.length;i++){
        oTable.rows[i].cells[0].innerHTML = (i+1);
        if(i%2==0)    //偶数行
            oTable.rows[i].className = "ys01";
    }
}
</script>
</head>
<body>

<div id="container">
<table class="zebra">
<caption>IP/s 并发量统计</caption>
<thead>
<tr>
    <th>序号</th>
    <th>ip</th>
    <th>登录次数</th>
    <th>时间</th>
    <th>日期</th>
</tr>
</thead>
<tbody id="bbsTab">
{% for reqinfocount in reqinfocounts %}
    <tr>
    <td></td>
    <td>{{ reqinfocount.ip|safe }}</td>
    <td>{{ reqinfocount.timesddd|safe }}</td>
    <td>{{ reqinfocount.timeddd|safe }}</td>
    <td>{{ reqinfocount.dateddd|safe }}</td>
    </tr>
{% endfor %}
</tbody>
</table>
</div>
</body>
</html>