Python Analytics device log statistics online time

Requirement: The leader requests the Analysis Service log, calculates each device's online time, the online rate, the equipment utilization rate and so on.

The Service log format is:

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}

The above marked red part:

DeviceID: For Device ID, networkstate: For network status, this value has 1, 2 values devicestate: For device state, this value has 0, 1, 23 values

device_report.py

#!/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:                 #  configuration Information                  config = {                          ' host ':  ' 127.0.0.1 ',                           ' Port ': 3306,                          ' user ':  ' analy ',                           ' Password ':  ' * * * * *,                          ' database ':  ' analy ',                           ' charset ':  ' UTF8 '                  }                 #  Connectivity                  con = connector.connect (**config)                  cursor = con.cursor ()                  #  query by string method                  cursor.execute (Insert_sql)                  con.commit ()                                  #  Close                  cursor.close ()                  con.close ()         except  Connector. error, e:                 Print (e)  &nbSp;              print (e.message) #cur  = conn.cursor () Records = nonedef device ():     with open ( SYS.ARGV[1])  as f:        for line in f:# Take out the JSON string in the log             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 ']] +=1if 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) 

This will take less than two minutes to complete.

Previous shell script:

#!/bin/bash#show all deviceid countstoday= ' date  "+%y%m%d" ' #FileName = ' test.txt ' filename= ' Devicereport ' tempfile= ' count1.txt ' mysql_sql= ' mysql -uanaly -ptestin.cn_123  ' 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 ') '    echo  $Today   $deviceid   $netstate _0_rate  $netstate _1_rate  $devicestate _0_rate  $devicestate _1_rate $ devicestate_2_rate  $Online _timedone

This will take two hours to complete.

When the script finishes executing the library, such as:

Python Analytics device log statistics online time