开发笔记:python分析设备日志统计在线时间

篇首语：本文由编程笔记#小编为大家整理，主要介绍了python 分析设备日志统计在线时间相关的知识，希望对你有一定的参考价值。

需求：领导要求分析服务日志，计算出各个设备的在线时长，在线率，设备使用率等。

服务日志格式为：

2018-03-01 00:13:52,815 [protocol.handler.1][INFO] - cn.testin.trans.controller.req.script.HeartBeat.deviceLog---{"sdcardWriteable":0,"deviceAction":1,"sdcardAvail":10113417216,"wifiSsid":"testin03_default","rootEnable":0,"deviceid":"863563023966636","networkState":1,"errorMsg":"","minicapEnable":1,"deviceState":1,"romAvail":1655619584,"rpiid":"localhost","networkType":1,"ramAvail":1488954688,"debugMode":1,"errorCount":0,"batteryLevel":100}

以上标红部分：

deviceid: 为设备id， networkState:为网络状态，该值有1、0两个值  deviceState:为设备状态，该值有0、1、2三个值

device_report.py

#!/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 = {
                        &＃39;host&＃39;: &＃39;127.0.0.1&＃39;,
                        &＃39;port&＃39;: 3306,
                        &＃39;user&＃39;: &＃39;analy&＃39;,
                        &＃39;password&＃39;: &＃39;****&＃39;,
                        &＃39;database&＃39;: &＃39;analy&＃39;,
                        &＃39;charset&＃39;: &＃39;utf8&＃39;
                }
                # 连接
                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[1]) as f:
        for line in f:
#取出日志里的json字符串
            json_origin = line.split(&＃39;---&＃39;)[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 = [&＃39;deviceid&＃39;,&＃39;NetworkState&＃39;,&＃39;DeviceState&＃39;]
for line in device():
    datadict = dict(zip(header,line))
    DeviceTotal[datadict[&＃39;deviceid&＃39;]] +=1
    if datadict[&＃39;NetworkState&＃39;] == 0:
        NetworkState_0[datadict[&＃39;deviceid&＃39;]] +=1
    elif datadict[&＃39;NetworkState&＃39;] == 1:
        NetworkState_1[datadict[&＃39;deviceid&＃39;]] +=1
    if datadict[&＃39;DeviceState&＃39;] == 0:
        DeviceState_0[datadict[&＃39;deviceid&＃39;]] +=1
    elif datadict[&＃39;DeviceState&＃39;] == 1:
        DeviceState_1[datadict[&＃39;deviceid&＃39;]] +=1
    elif datadict[&＃39;DeviceState&＃39;] == 2:
        DeviceState_2[datadict[&＃39;deviceid&＃39;]] +=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(&＃39;%s&＃39;,&＃39;%s&＃39;,&＃39;%s&＃39;,&＃39;%s&＃39;,&＃39;%s&＃39;,&＃39;%s&＃39;,&＃39;%s&＃39;,&＃39;%s&＃39;)") % data
        execute_sql(query)

此时执行完成，需要不到两分钟。

此前shell脚本：

#!/bin/bash
#show all deviceid counts
Today=`date "+%Y%m%d"`
#FileName=&＃39;test.txt&＃39;
FileName=&＃39;devicereport&＃39;
TempFile=&＃39;count1.txt&＃39;
mysql_sql=&＃39;mysql -uanaly -pTestin.cn_123 &＃39;
cat ${FileName} | awk -F&＃39;---&＃39; &＃39;{print $2}&＃39; | jq &＃39;.deviceid&＃39;|sort|uniq -c >> ${TempFile}
for deviceid in `cat ${TempFile}|awk &＃39;{print $2}&＃39;`
do
   #find networkstate
   netstate_0=`grep $deviceid ${FileName}| awk -F&＃39;---&＃39; &＃39;{print $2}&＃39; | jq &＃39;.networkState&＃39;|grep 0 |wc -l`
   netstate_1=`grep $deviceid ${FileName}| awk -F&＃39;---&＃39; &＃39;{print $2}&＃39; | jq &＃39;.networkState&＃39;|grep 1 |wc -l`
   devicestate_0=`grep $deviceid ${FileName}| awk -F&＃39;---&＃39; &＃39;{print $2}&＃39; | jq &＃39;.deviceState&＃39;|grep 0 |wc -l`
   devicestate_1=`grep $deviceid ${FileName}| awk -F&＃39;---&＃39; &＃39;{print $2}&＃39; | jq &＃39;.deviceState&＃39;|grep 1 |wc -l`
   devicestate_2=`grep $deviceid ${FileName}| awk -F&＃39;---&＃39; &＃39;{print $2}&＃39; | jq &＃39;.deviceState&＃39;|grep 2 |wc -l`
   sumdevice=`grep $deviceid $TempFile|awk &＃39;{print $1}&＃39;`
#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 &＃39;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 (&＃39;$Today&＃39;,&＃39;$deviceid&＃39;,&＃39;$netstate_0_rate&＃39;,&＃39;$netstate_1_rate&＃39;,&＃39;$devicestate_0_rate&＃39;,&＃39;$devicestate_1_rate&＃39;,&＃39;$devicestate_2_rate&＃39;,&＃39;$Online_time&＃39;)&＃39;
   echo $Today $deviceid $netstate_0_rate $netstate_1_rate $devicestate_0_rate $devicestate_1_rate $devicestate_2_rate $Online_time
done

此时执行完需要两个多小时。

脚本执行完入库的情况如下图：