python 分析设备日志统计在线时间
#!/usr/bin/env python# -*- coding: utf-8 -*-
import sys
import json
from collections import Counter
from mysql import connector
import datetime
today = datetime.datetime.now().strftime("%Y%m%d")
def execute_sql(insert_sql) :
try:
# 配置信息
config = {
'host': '127.0.0.1',
'port': 3306,
'user': 'analy',
'password': '****',
'database': 'analy',
'charset': 'utf8'
}
# 连接
con = connector.connect(**config)
cursor = con.cursor()
# 利用字符串方式查询
cursor.execute(insert_sql)
con.commit()
# 关闭
cursor.close()
con.close()
except connector.Error, e:
print(e)
print(e.message)
#cur = conn.cursor()
records = None
def device():
with open(sys.argv) as f:
for line in f:
#取出日志里的json字符串
json_origin = line.split('---')
#将json字符串格式化为字典
json_final = json.loads(json_origin)
DeviceId = json_final["deviceid"]
NetworkState = json_final["networkState"]
DeviceState = json_final["deviceState"]
yield
DeviceTotal,NetworkState_0,NetworkState_1,DeviceState_0,DeviceState_1,DeviceState_2 = Counter(), Counter(), Counter(), Counter(), Counter(), Counter()
header = ['deviceid','NetworkState','DeviceState']
for line in device():
datadict = dict(zip(header,line))
DeviceTotal] +=1
if datadict['NetworkState'] == 0:
NetworkState_0] +=1
elif datadict['NetworkState'] == 1:
NetworkState_1] +=1
if datadict['DeviceState'] == 0:
DeviceState_0] +=1
elif datadict['DeviceState'] == 1:
DeviceState_1] +=1
elif datadict['DeviceState'] == 2:
DeviceState_2] +=1
if DeviceTotal:
for k,v in DeviceTotal.most_common(records):
data = (today,k, (NetworkState_0/float(v))*100, (NetworkState_1/float(v))*100, (DeviceState_0/float(v))*100, (DeviceState_1/float(v))*100, (DeviceState_2/float(v))*100,((DeviceState_1 + DeviceState_0)*10))
query = ("INSERT INTO device_data_report""(date,deviceid,netstate_0_rate,netstate_1_rate,devicestate_0_rate,devicestate_1_rate,devicestate_2_rate,Online_time)" "VALUES('%s','%s','%s','%s','%s','%s','%s','%s')") % data
execute_sql(query)
页:
[1]