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