atlas 日志分析脚本

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88

#!/usr/bin/env python # encoding: utf-8   #@author: 东哥加油! #@file: log_analyze.py #@time: 2018/8/23 17:15   import pandas as pd import re import time import datetime       def tj_log_to_excel(from_file_name,to_file_name):     file=open(from_file_name,'r',encoding='UTF-8')     columns = ('表名','SELECT 统计','UPDATE 统计','INSERT 统计','DELETE 统计')     results = []     results.append(columns)     dict1 = {}     print(time.strftime("%H:%M:%S"))     for (num,line) in enumerate(file):         line = line.lower()         l_row = []         if(re.search(r'delete from ',line)):             str = re.findall(r"delete from ([a-zA-Z0-9_\.]*)", line)             str = str[0]             if(dict1.get(str) == None):                 l_row = [0,0,0,1]                 dict1[str] = l_row             else:                 v = dict1.get(str)                 v[3] = v[3] + 1         elif (re.search(r' from ',line)):             str = re.findall(r" from ([a-zA-Z0-9_\.]*)", line)             str = str[0]             if str != '':                 if (dict1.get(str) == None):                     l_row = [1, 0, 0, 0]                     dict1[str] = l_row                 else:                     v = dict1.get(str)                     v[0] = v[0] + 1         elif (re.search(r'"update ', line)):             str = re.findall(r"update ([a-zA-Z0-9_\.]*)", line)             str = str[0]             if str != '':                 if (dict1.get(str) == None):                     l_row = [0, 1, 0, 0]                     dict1[str] = l_row                 else:                     v = dict1.get(str)                     v[1] = v[1] + 1         elif (re.search(r'"insert into ', line)):             str = re.findall(r"insert into ([a-zA-Z0-9_\.]*)", line)             str = str[0]             if str != '':                 if (dict1.get(str) == None):                     l_row = [0, 0, 1, 0]                     dict1[str] = l_row                 else:                     v = dict1.get(str)                     v[2] = v[2] + 1         for key in dict1:         t_row = []         t_row.append(key)         t_row.append(dict1[key][0])         t_row.append(dict1[key][1])         t_row.append(dict1[key][2])         t_row.append(dict1[key][3])         results.append(t_row)         df = pd.DataFrame(results)     df.to_excel(to_file_name)     print(time.strftime("%H:%M:%S"))   if __name__ == '__main__':     now_time = datetime.datetime.now()     step_time = datetime.timedelta(days=1)     yes_time = now_time - step_time     pdate = yes_time.strftime('%Y%m%d')     from_file_name = '/usr/local/mysql-proxy/log/sql_balance.log_'+pdate     print(from_file_name)     to_file_name= '/data/shell/sk/sql_balance_'+pdate+'.xls'     tj_log_to_excel(from_file_name,to_file_name)