Shulou(Shulou.com)06/01 Report--

The Python script is as follows:

#! / usr/bin/env python#_*_coding:utf-8 _ * _ author__ = 'lvnianqiaoyuanbin env python# coding:utf- 8import MySQLdb as mysqlimport sys, osdb = mysql.connect (user= "root", passwd= "xxxxx@2015", db= "intest") Host= "192.168.10.12") # Database connection information db.autocommit (True) cur = db.cursor () cur.execute ('set names utf8') dict_list = {} with open (' access.log') as logfin: for line in logfin: arr = line.split ('') # get ip url and status ip = arr [0] url = arr [6] status = arr [8] # ip url and status when key Each statistic + 1 dict_list [(ip,url,status)] = dict_list.get ((ip,url,status), 0) + 1 # is converted into a list ip_list = [(k [0], k [1], k [2], v) for KJ v in dict_list.items ()] # sorted by statistical quantity, sorted and saved to the database. For insert in sorted (ip_list,key=lambda x reverse=True): # if you only want to extract the first ten lines, you can [: 10]. For print insert # testing, you don't have to. The whole function is to output a line and save it to the database. Sql = 'insert loginfo values ("% s", "% s")'% insert print sql cur.execute (sql)

Create the corresponding table (based on the previous article)

Python analyzes the ip of the nginx log (source)

Use intest;show tables; CREATE TABLE `loginfo` (`ip` text DEFAULT NULL, `url` longtext DEFAULT NULL, `status` varchar (200) DEFAULT NULL, `count_ num` varchar (200) DEFAULT NULL)

The results seen in the database after the execution of the script are as follows:

Mysql > select * from loginfo where count_num > 3 limit 5 +-+-+ | ip | url | status | count_num | +-+- -+ | 121.42.0.85 | / | 200 | 39 | | 121.42.0.37 | / | 403 | 34 | | 121.42.0.39 | / | 403 | 34 | 222.95 .400 | HTTP://www.baidu.com/ | 248.220 | 26 | | 23.251.49.10 | www.baidu.com:443 | | 21 | +-+-- + 5 rows in set (sec) mysql >

Add a time field script to the following

#! / usr/bin/env python#_*_coding:utf-8 _ * _ author__ = 'lvnianqiaoyuanbin env python# coding:utf- 8import MySQLdb as mysqlimport sys, os,timeimport datetimeaddtime = datetime.datetime.now () db= mysql.connect (user= "root", passwd= "xxxx@2015", db= "intest") Host= "192.168.10.12") # Database connection information db.autocommit (True) cur = db.cursor () cur.execute ('set names utf8') dict_list = {} with open (' access.log') as logfin: for line in logfin: arr = line.split ('') # get ip url and status ip = arr [0] url = arr [6] status = arr [8] # ip url and status when key Each time, + 1 dict_list [(ip,url,status)] = dict_list.get ((ip,url,status), 0) + 1 # is converted into a list ip_list = [(k [0], k [1], k [2], v in dict_list.items ()] # sorted by statistics, sorted and saved to the database. For insert in sorted (ip_list,key=lambda x reverse=True): # if you only want to extract the first ten lines, you can [: 10]. For print insert # testing, you don't have to. The whole function is to output a line and save it to the database. Sql = 'insert loginfo values ("% s", "% s")'% insert print sql cur.execute (sql) # to the database output query: 123.125.160.217 this ip is often disorderly * * other servers are exposed. If you see this ip accessing log, please call 911 directly.

Database modification

Add a field to the database: alter table loginfo add time datetime not Null; or rebuild library CREATE TABLE `loginfo` (`ip` text, `url` longtext, `status` varchar (200) DEFAULT NULL, `count_ num` varchar (200) DEFAULT NULL, `time` datetime NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.