python 分析设备日志统计在线时间

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172

#!/usr/bin/env python# -*- coding: utf-8 -*-import sysimport jsonfrom collections import Counterfrom mysql import connectorimport datetimetoday = 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 = Nonedef device():    with open(sys.argv[1]) as f:        for line in f:#取出日志里的json字符串            json_origin = line.split('---')[1]#将json字符串格式化为字典            json_final = json.loads(json_origin)            DeviceId = json_final["deviceid"]            NetworkState = json_final["networkState"]            DeviceState = json_final["deviceState"]              yield [DeviceId,NetworkState,DeviceState]  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[datadict['deviceid']] +=1    if datadict['NetworkState'] == 0:        NetworkState_0[datadict['deviceid']] +=1    elif datadict['NetworkState'] == 1:        NetworkState_1[datadict['deviceid']] +=1    if datadict['DeviceState'] == 0:        DeviceState_0[datadict['deviceid']] +=1    elif datadict['DeviceState'] == 1:        DeviceState_1[datadict['deviceid']] +=1    elif datadict['DeviceState'] == 2:        DeviceState_2[datadict['deviceid']] +=1  if DeviceTotal:    for k,v in DeviceTotal.most_common(records):        data = (today,k, (NetworkState_0[k]/float(v))*100, (NetworkState_1[k]/float(v))*100, (DeviceState_0[k]/float(v))*100, (DeviceState_1[k]/float(v))*100, (DeviceState_2[k]/float(v))*100,((DeviceState_1[k] + DeviceState_0[k])*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)

12345678910111213141516171819202122232425

#!/bin/bash#show all deviceid countsToday=`date "+%Y%m%d"`#FileName='test.txt'FileName='devicereport'TempFile='count1.txt'mysql_sql='mysql -uanaly -p****** 'cat ${FileName} | awk -F'---' '{print $2}' | jq '.deviceid'|sort|uniq -c >> ${TempFile}for deviceid in `cat ${TempFile}|awk '{print $2}'`do   #find networkstate   netstate_0=`grep $deviceid ${FileName}| awk -F'---' '{print $2}' | jq '.networkState'|grep 0 |wc -l`   netstate_1=`grep $deviceid ${FileName}| awk -F'---' '{print $2}' | jq '.networkState'|grep 1 |wc -l`   devicestate_0=`grep $deviceid ${FileName}| awk -F'---' '{print $2}' | jq '.deviceState'|grep 0 |wc -l`   devicestate_1=`grep $deviceid ${FileName}| awk -F'---' '{print $2}' | jq '.deviceState'|grep 1 |wc -l`   devicestate_2=`grep $deviceid ${FileName}| awk -F'---' '{print $2}' | jq '.deviceState'|grep 2 |wc -l`   sumdevice=`grep $deviceid $TempFile|awk '{print $1}'`#compute rate   netstate_0_rate=`echo "scale=2;a=(${netstate_0}/${sumdevice})*100;if(length(a)==scale(a)) print 0;print a"|bc`   netstate_1_rate=`echo "scale=2;a=(${netstate_1}/${sumdevice})*100;if(length(a)==scale(a)) print 0;print a"|bc`   devicestate_0_rate=`echo "scale=2;a=(${devicestate_0}/${sumdevice})*100;if(length(a)==scale(a)) print 0;print a"|bc`   devicestate_1_rate=`echo "scale=2;a=(${devicestate_1}/${sumdevice})*100;if(length(a)==scale(a)) print 0;print a"|bc`   devicestate_2_rate=`echo "scale=2;a=(${devicestate_2}/${sumdevice})*100;if(length(a)==scale(a)) print 0;print a"|bc`   Online_time=$(((${devicestate_0} + ${devicestate_1})/360))   ${mysql_sql} -e 'insert into analy.device_data_report (date,deviceid,netstate_0_rate,netstate_1_rate,devicestate_0_rate,devicestate_1_rate,devicestate_2_rate,Online_time) values ('$Today','$deviceid','$netstate_0_rate','$netstate_1_rate','$devicestate_0_rate','$devicestate_1_rate','$devicestate_2_rate','$Online_time')'