def insertNetInfo(waveCursor, netList): try: strLst = [] for netElm in netList: if netElm['ntype'] == 'w': strValue = "('%s', %s, '%s', '%s', '%s', %s, unix_timestamp())" % \ (netElm['playSessionID'], netElm['stTime'], netElm['ntype'], \ netElm['bssid'], netElm['ssid'], netElm['traffic']) else: strValue = "('%s', %s, '%s', NULL, NULL, %s, unix_timestamp())" % \ (netElm['playSessionID'], netElm['stTime'], netElm['ntype'], netElm['traffic']) strLst.append(strValue) if len(strLst) > 0: sql = """insert into vidsession_net (playSessionID, stTime, ntype, bssid, ssid, traffic, lstuptmp) values %s on duplicate key update ntype = values(ntype), bssid = values(bssid), ssid = values(ssid), traffic = values(traffic), lstuptmp = unix_timestamp() """ % ', '.join(strLst) ret = waveCursor.execute(sql) if ret == 0: log.warn("insertNetInfo no record affected [%s]" % sql) except Exception, e: log.error("insertNetInfo error:%s" % e) log.error(strLst) raise e
def __makeCellId(self, plmnid, cid, lac): try: cellId = map(lambda x: str(x) if str(x).isdigit() else '0', [plmnid, cid, lac]) if 0 not in map(int, cellId) and len(cellId[0]) < 7: return '_'.join(cellId) except Exception, e: log.error(e)
def handler(self, params): try: log.info("evtNetworkLog get") #cocoa Log skip if params.has_key('evtKey') == False and params.has_key('startTime') == False: return # 구 버전의 로그 무시 apatLogVer = map(int, params.get('ver', "1.00.0").split('.')) if apatLogVer < [1,0,10]: return dataVer = map(int, params.get('ansLogVer', "0.5").split('.')) if dataVer < [1,0] and params.has_key('evtKey') == False: return self.logInsert(params) log.info("logInsert done") except Exception, e: log.error("processAnsLog ERROR[deviceID:%s, sID:%s, numTotalHits:%s] %s" % (params['deviceID'], params['sID'], params['numTotalHits'], e) ) if str(e).find('restart') > 0: log.error("processAnsLog raise e") raise e
def updateVidnet(waveCursor, vidDict): if vidDict == None: log.warn('updateVidnet : vidDict is null') return False playSessionID = vidDict.pop('playSessionID') cols = vidDict.keys() vals = vidDict.values() try: slist = [] for key in vidDict: if type(vidDict[key]) == str or type(vidDict[key]) == unicode: s = "%s = '%s'" % (key, vidDict[key].replace("'", "''")) else: s = "%s = %s" % (key, str(vidDict[key])) slist.append(s) slist.append("lstuptmp = unix_timestamp()") #sql = "UPDATE vidsession SET %s WHERE playSessionID = '%s'" % (unicode(',', 'utf-8').join(map(lambda key:"%s='%s'" % (key, unicode(vidDict[key], 'utf-8').replace("'", "''")), vidDict)), playSessionID) sql = "UPDATE vidsession SET %s WHERE playSessionID = '%s'" % (unicode(',', 'utf-8').join(slist), playSessionID) waveCursor.execute(sql) except Exception, e: log.error("update vidsession ERROR:%s, playSessionID:%s" % (e, playSessionID)) log.error(vidDict) raise e
def getBBinfo(rBBList, aatLog): try: if aatLog.has_key('bbCount') == False or aatLog.has_key('bbList') == False: return BBcount = aatLog['bbCount'] if BBcount == 0: return elif BBcount > 40: BBcount = 40 if isinstance(aatLog['bbList'], list): bblst = aatLog['bbList'][0:BBcount] else: bblst = aatLog['bbList'].strip('[ ]').split(',') bblst = bblst[0:BBcount] bblst = bblst[0:BBcount] bbdict = {} for bbItem in bblst: if bbItem.find('|') < 0: continue bbElm = bbItem.strip(" u'\"").split('|') bbdict['psid'] = aatLog['playSessionId'] bbdict['bb'] = list(bbElm) rBBList.append(bbdict.copy()) except Exception, e: log.error("getBBinfo error:%s" % e) log.error(aatLog) raise e
def insertBBSQL(waveCursor, bbList): try: strLst = [] for bbElm in bbList: if len(bbElm['bb']) == 8 and bbElm['bb'][7] == 'e': strValue = "('%s', %s, %s, '%s', '%s', %s, %s, %s, unix_timestamp())" % (bbElm['psid'], bbElm['bb'][0], bbElm['bb'][2], \ '{0:02d}'.format(int(bbElm['bb'][1])), '{0:02d}'.format(int(bbElm['bb'][3])), bbElm['bb'][4], bbElm['bb'][5], bbElm['bb'][6]) elif len(bbElm['bb']) == 6: strValue = "('%s', %s, %s, '%s', '%s', %s, %s, NULL, unix_timestamp())" % (bbElm['psid'], bbElm['bb'][0], bbElm['bb'][2], \ '{0:02d}'.format(int(bbElm['bb'][1])), '{0:02d}'.format(int(bbElm['bb'][3])), bbElm['bb'][4], bbElm['bb'][5]) else: log.warn("BBList format error:") log.warn(bbElm) continue strLst.append(strValue) if len(strLst) > 0: sql = """insert into vidsession_bb (playSessionID, stTime, endTime, stCode, endCode, trWF, trCell, stBBTime, lstuptmp) values %s on duplicate key update endTime = values(endTime), stCode = values(stCode), trWF = values(trWF), trCell = values(trCell), stBBTime = values(stBBTime), lstuptmp = unix_timestamp() """ % ', '.join(strLst) ret = waveCursor.execute(sql) if ret == 0: log.warn("insertBBSQL no record affected [%s]" % sql) except Exception, e: log.error("insertBBSQL error:%s" % e) log.error(bbList) raise e
def _closePrevAndMakeNewSession(self, session, data): self.closeSession(session, data) try: self.makeSession(data) except Exception, e: log.error("makeSession Error:%s" % e) raise e
def extractNetworkNode(self, params): # net info structure # wifi : 'wifi', status, ssid, bssid # cell : 'cell', status, celltower id # status : 'active' for current network, 'inactive' for logged network timestamp = time.strftime('%Y%m%d%H%M%S', time.gmtime(params['tTM'])) logType = params.get('log_type', 'unknown') netList = UniqueList() netList.setKey(key=lambda x: x.cellid if isinstance(x, CellNode) else x.bssid) if 'lat' in params and 'lng' in params: geoloc = GeoInfo(lat=params.get('lat'), lng=params.get('lng'), acc=params.get('accuracy', 500), geosrc='device') else: geoloc = None # APAT Header fields try: if 'pwf' in params: pwf = params['pwf'] if 'bssid' in pwf and EthAddrType.isEthAddr(pwf['bssid']): node = WiFiNode(state='active', bssid=pwf['bssid'], ssid=pwf.get('ssid', ''), regdtm=timestamp, geoloc=geoloc) netList.addSet(node) except Exception, e: log.error(e) log.error(params)
def handler(self, params): # Event Key/Value 인 경우, 무시 if 'evtKey' in params.keys(): return # Extract network nodes from logging try: networks = self.extractNetworkNode(params) except Exception, e: log.error(e) log.error(params) return
def closeSession(self, session, data): try: if session['startTime'] == session['endTime'] or session['startTime'] == 0: sqlStr = """DELETE vd, vbb, vbc FROM appsession AS vd LEFT JOIN appsession_net AS vbb ON vd.sessionID = vbb.sessionID LEFT JOIN appsession_netcell AS vbc ON vd.sessionID = vbc.sessionID WHERE vd.sessionID = '%s'""" % (session['sessionID']) self.rdbCur.execute(sqlStr) actionkey = 'D' else: sqlStr = "UPDATE appsession SET valid=1, statAppss = '0', lstuptmp = unix_timestamp() WHERE sessionID='%s'" % (session['sessionID']) self.rdbCur.execute(sqlStr) actionkey = 'U' anID = session['androidID'] pkgname = session['pkgnm'] appSSid = session['sessionID'] sID = session['sID'] sqlStr = """UPDATE vidsession SET appSessionIDSt = IF(appSessionIDSt = '%s', '', appSessionIDSt), appSessionIDEnd = IF(appSessionIDEnd = '%s', '', appSessionIDEnd), lstuptmp = unix_timestamp() WHERE (appSessionIDSt = '%s' or appSessionIDEnd = '%s') AND playSessionID like '%s%%' and androidID = '%s' and pkgnm = '%s' and sID = %d """ % (appSSid, appSSid, appSSid, appSSid, anID, anID, pkgname, sID) ret = self.rdbCur.execute(sqlStr) sqlStr = """UPDATE vidsession_log SET appSessionID = '' WHERE appSessionID = '%s' and playSessionID like '%s%%'""" % (appSSid, anID) ret = self.rdbCur.execute(sqlStr) if actionkey == 'U': sqlStr = """UPDATE vidsession SET appSessionIDSt = '%s', lstuptmp = unix_timestamp() WHERE playSessionID LIKE '%s%%' AND androidID = '%s' AND pkgnm = '%s' AND sID = %d AND vidnetStartTime >= %d AND vidnetStartTime < %d""" % (appSSid, anID, anID, pkgname, sID, session['startTime'], session['endTime']) ret = self.rdbCur.execute(sqlStr) sqlStr = """UPDATE vidsession SET appSessionIDEnd = '%s', lstuptmp = unix_timestamp() WHERE playSessionID LIKE '%s%%' AND androidID = '%s' AND pkgnm = '%s' AND sID = %d AND vidnetEndTime >= %d AND vidnetEndTime < %d""" % (appSSid, anID, anID, pkgname, sID, session['startTime'], session['endTime']) ret = self.rdbCur.execute(sqlStr) sqlStr = """UPDATE vidsession_log SET appSessionID = '%s', lstuptmp = unix_timestamp() WHERE playSessionID like '%s%%' and logStartTime >= %d and logStartTime < %d """ % (appSSid, anID, session['startTime'], session['endTime']) ret = self.rdbCur.execute(sqlStr) except Exception, e: log.error("appSession closeSession Error[sessionID:%s] %s" % (session['sessionID'], e)) raise e
def updateMcc(deviceID, plmnId): try: rcur = None strSQL = "" if plmnId.isdigit() == True and int(plmnId) > 0: rcur = worker.dbmanager.allocDictCursor('myapmain') strSQL = "UPDATE mdev SET plmnid = '%s' WHERE mosid = '%s' and (plmnid <= ' ' or plmnid is NULL)" % (plmnId, deviceID) ret = rcur.execute(strSQL) except Exception, e: log.error("updateMcc: %s" % e) log.error("updateMcc: [deviceID:%s, plmnid:%s, strSQL:%s]" % (deviceID, plmnId, strSQL))
def process(self, params): try: self.rdbCur = None self.rdbCur = worker.dbmanager.allocDictCursor('myapwave') pnm = "p%x" % (int(random.random()*100) % 15 + 1) while True: self.rdbCur.execute("START TRANSACTION") strSQL = "SELECT deviceID, lstuptmp from appssdist partition (%s) where isbusy = '0' and procTmp < %d order by lstuptmp limit 1 for update" % (pnm, params['tmp']) ret = self.rdbCur.execute(strSQL) if ret > 0 and ret < 10000: # Sometimes Mysql returns 18446744073709551615 instead of -1 row = self.rdbCur.fetchone() devid = row['deviceID'] lstuptmp = row['lstuptmp'] strSQL = "UPDATE appssdist SET isbusy = '1' where deviceID = '%s'" % (devid) ret = self.rdbCur.execute(strSQL) self.rdbCur.execute("COMMIT") log.info("process:appssdist '1' %s" % devid) try: procnt = self.passLog(devid) except Exception, e: log.error("Appsession.process in while [%s] %s" % (devid, e)) while True: try: rtcnt = 0 if procnt > 0: strSQL = "UPDATE appssdist SET isbusy = '0', procTmp = %d, lstuptmp = unix_timestamp() where deviceID = '%s'" % (params['tmp'], devid) else: if lstuptmp < params['tmp'] - 600: strSQL = "DELETE FROM appssdist where deviceID = '%s'" % (devid) else: strSQL = "UPDATE appssdist SET isbusy = '0', procTmp = %d where deviceID = '%s'" % (params['tmp'], devid) ret = self.rdbCur.execute(strSQL) log.info("process: appssdist '0' %s" % devid) break except Exception, e: if str(e).find('restart') > 0: log.warn("process: appssdist[%s] set to '0' retry: %d, %s" % (devid, rtcnt, e)) if rtcnt > 10: raise e time.sleep(rtcnt+1) rtcnt += 1 else: log.error("process: appssdist[%s] set to '0' %s" % (devid, e)) raise e else:
def logInsert(self, params): try: if params.has_key('tTM') == False: try: vrdict = params.get('__validityReport', '') if vrdict.has_key('tTM'): params['tTM'] = float(vrdict['tTM'][0][0].replace(",", ".")) except Exception, e: log.error("logInsert tTM convert [androidID:%s, pkgName:%s, sID:%s, tTM:%s, numTotalHits:%s] %s" % \ (params.get('deviceID', '-'), params.get('pkgName', '-'), params.get('sID', -9), params.get('tTM', -9), params.get('numTotalHits', -9), e)) return self.rdbCur = None self.rdbCur = worker.dbmanager.allocDictCursor('myapwave') log.info("logInsert: before tran") self.rdbCur.execute("START TRANSACTION") strSQL = """INSERT IGNORE INTO appsslog ( deviceID, pkgName, sID, tTM, objid, agentAllowAns, ansLogVer, ansVer, avgTP, bssid, cellId, duration, estTP, evtKey, evtValue, evtSyncID, startCode, exitCode, startTime, eventTime, initWifi, model, netType, numTotalHits, NWcharac, playAppPackageName, playTime, activeDownload, activeTime, totalDownload, linkspd, apfreq, ver, verCode, myfiOn, myfiAP, parentAppName, insTs) VALUES ( '%s', '%s', %d, %.3f, '%s', %s, '%s', '%s', %s, '%s', '%s', %s, %s, '%s', %s, %s, %s, %s, %s, %s, %s, '%s', '%s', %s, '%s', '%s', %s, %s, %s, %s, %s, %s, '%s', %s, %s, %s, '%s', %s)""" % \ (params['deviceID'], params['pkgName'], int(params['sID']), float(params['tTM']), params.get('objid', ''), int(params.get('agentAllowAns', -1)), params.get('ansLogVer', ''), params.get('ansVer', ''), params.get('avgTP', 'NULL'), params.get('bssid', ''), params.get('cellId', ''), params.get('duration', 'NULL'), params.get('estTP', 'NULL'), params.get('evtKey', ''), params.get('evtValue', 'NULL'), params.get('evtSyncID', 'NULL'), params.get('startCode', 'NULL'), params.get('exitCode', 'NULL'), params.get('startTime', 'NULL'), params.get('eventTime', 'NULL'), params.get('initWifi', 'NULL'), params.get('model', ''), params.get('netType', ''), params.get('numTotalHits', 'NULL'), params.get('NWcharac', ''), params.get('playAppPackageName', ''), params.get('playTime', 'NULL'), params.get('activeDownload', 'NULL'), params.get('activeTime', 'NULL'),params.get('totalDownload', 'NULL'), params.get('lnkspd', 'NULL'), params.get('apfreq', 'NULL'), params.get('ver', ''), params.get('verCode', 'NULL'), params.get('myfiOn', 'NULL'), params.get('myfiAP', 'NULL'), params.get('parentAppName', ''), params.get('insTs', 'NULL') ) ret = self.rdbCur.execute(strSQL) strSQL = """INSERT IGNORE INTO appssdist (deviceID, isbusy, lstuptmp) VALUES ('%s', '0', unix_timestamp())""" % (params['deviceID']) ret = self.rdbCur.execute(strSQL) self.rdbCur.execute("COMMIT") log.info("logInsert: commit tran")
def handler(self, params): global appss global lastParamTmp try: log.info("evtTimeInterval get %d (last:%d)" % (params['tmp'], lastParamTmp)) if params['tmp'] > lastParamTmp: log.info("appss.process start") appss.process(params) lastParamTmp = params['tmp'] log.info("appss.process done") except Exception, e: log.error("processAnsClock ERROR %s" % (e) )
def updateVidLog(waveCursor, vLog, row): try: strSQL = "" strSQL = """UPDATE vidsession_log SET playTime = if(playTime >= %d, playTime - %d, playTime), pauseTime = if(pauseTime >= %d, pauseTime - %d, pauseTime), elapsedTime = if(elapsedTime >= %d, elapsedTime - %d, elapsedTime), cellRxBytes = if(cellRxBytes >= %d, cellRxBytes - %d, cellRxBytes), wfRxBytes = if(wfRxBytes >= %d, wfRxBytes - %d, wfRxBytes), cellDuration = if(cellDuration >= %d, cellDuration - %d, cellDuration), wfDuration = if(wfDuration >= %d, wfDuration -%d, wfDuration), lstuptmp = unix_timestamp() WHERE playSessionID = '%s' and tTM = %s """ % (vLog['playTime'], vLog['playTime'], vLog['pauseTime'], vLog['pauseTime'], vLog['elapsedTime'], vLog['elapsedTime'], vLog['cellRxBytes'], vLog['cellRxBytes'], vLog['wfRxBytes'], vLog['wfRxBytes'], vLog['cellDuration'], vLog['cellDuration'], vLog['wfDuration'], vLog['wfDuration'], vLog['playSessionID'], row['nextTTM']) waveCursor.execute(strSQL) except Exception, e: log.error("updateVidLog %s" % e) log.error(vLog) log.error(row) if strSQL > "": log.error("[SQL] %s" % strSQL) raise e
def updateAppssLst(self, data, sessionID): try: strSQL = """REPLACE appsession_lst (androidID, pkgnm, tTM, numTotalHits, sID,sessionID, oid) VALUES ('%s', '%s', %.3f, %d, %d, '%s', '%s') """ % (data['deviceID'], data['pkgName'], float(data['tTM']), int(data['numTotalHits']), int(data.get('sID', 0)), sessionID, data.get('objID', '')) ret = self.rdbCur.execute(strSQL) if ret == 0: log.warn("UPDATE appsession_lst 0 row strSQL:%s" % strSQL) except Exception, e: log.error("update appsession_lst :%s" % e) log.error(strSQL) raise e
def addCellTower(cursor, node): strSql = """INSERT INTO apmain.cellinfo (fullid, plmnid, cellid, lac, celltype, regdtm, lat, lng, acc, geosrc, seq) VALUES('%s','%s','%s','%s','%d','%s','%s','%f','%f','%s', '1') ON DUPLICATED UPDATE lat=((lat*seq)+VALUES(lat))/(seq+1), lng=((lng*seq)+VALUES(lng))/(seq+1), seq=seq+1, geosrc=VALUES(geosrc)""" try: strSql = strSql % (node.cellid, node.plmnid, node.cid, node.lac, 0, node.regdtm, node.geoloc.lat, node.geoloc.lng, node.geoloc.acc, 'cellLoc' if node.geoloc.from_cell else node.geoloc.geosrc) cursor.execute(strSql) log.debug("INSERT - %s" % node.cellid) except Exception, e: # Duplicate entry error if e[0] != 1062: log.error(e) log.error(strSql) return False
def preprocessLog(self, data): #exit code conversion codeMap = {15:0, 21:10, 12:20, 20:30, 19:40, 22:50, 14:60, 13:100, 16:120, 23:121, 7:141, 6:201, 5:0, 17:202, 0:301, 3:302, 2:0, 1:303, 4:304, 11:0, 10:0, 8:305, 9:306, 24:307, -1:402} try: dataVer = map(int, data['ansLogVer'].split('.')) if dataVer < [2,0]: data['exitCode'] = codeMap.get(int(data.get('exitCode', -99)), -99) data['startCode'] = codeMap.get(int(data.get('startCode', -99)), -99) else: data['exitCode'] = int(data.get('exitCode', -99)) data['startCode'] = int(data.get('startCode', -99)) except Exception, e: log.error("exitCode field Error %s:%s" % (e, data.get('exitCode'))) data['exitCode'] = 0
def insertVidnetLog(waveCursor, vidLogDict): if vidLogDict == None: log.warn('vidLogDict is null') return False cols = vidLogDict.keys() vals = vidLogDict.values() try: sql = """insert into vidsession_log (%s, lstuptmp) values (%s, unix_timestamp())""" % (",".join(cols), ",".join(["'" + str(val).replace("'", "''") + "'" for val in vals])) waveCursor.execute(sql) except Exception, e: log.error("INSERT vidsession_log ERROR:%s" % e) log.error(vidLogDict) raise e
def addWiFi(cursor, node): strSql = """INSERT INTO apmain.apinfo (bssid, ssid, regdtm, bregap, bmap, lat, lng, acc, geosrc, optrcom, seq) VALUES('%s','%s','%s','%d','%d','%f','%f','%d','%s','%s','%s') ON DUPLICATED UPDATE lat = IF(VALUES(seq) > seq, VALUES(lat), lat), lng = IF(VALUES(seq) > seq, VALUES(lng), lng), seq = IF(VALUES(seq) > seq, VALUES(seq), seq), acc = IF(VALUES(seq) > seq, VALUES(acc), acc), geosrc=VALUES(geosrc)""" try: strSql = strSql % (node.bssid, node.ssid, node.regdtm, int(node.bregap), int(node.bmap), node.geoloc.lat, node.geoloc.lng, node.geoloc.acc, node.geoloc.geosrc, node.optrcom, node.rssi) except Exception, e: log.error("SQL GEN ERR - %s" % bytes(node.ssid)) strSql = strSql % (node.bssid, '', node.regdtm, int(node.bregap), int(node.bmap), node.geoloc.lat, node.geoloc.lng, node.geoloc.acc, node.geoloc.geosrc, node.optrcom, node.rssi)
def procSyncID(self, data, sessionID): try: self.rdbCur.execute("START TRANSACTION") tbCols = ['deviceID', 'pkgName', 'sID', 'evtSyncID', 'tTM', 'numTotalHits', 'evtValue', 'eventTime', 'startTime', 'startCode', 'exitCode', 'activeDownload', 'activeTime', 'agentAllowAns', 'ansLogVer', 'ansVer', 'avgTP', 'bssid', 'cellId', 'duration', 'initWifi', 'model', 'netType', 'NWcharac', 'playAppPackageName', 'parentAppName', 'playTime', 'totalDownload', 'ver', 'verCode', 'myfiOn', 'myfiAP'] strSQL = """SELECT * FROM appsync WHERE deviceID = '%s' and pkgName = '%s' and sID = %d and evtSyncID = %d """ % (data.get('deviceID', ''), data.get('pkgName', ''), int(data.get('sID', 0)), int(data.get('evtSyncID', 0))) retFunc = self.rdbCur.execute(strSQL) if retFunc == 0: sqlDict = {} for col in tbCols: if data.has_key(col): if col == 'initWifi' or col == 'agentAllowAns': if str(data[col]) in ['true', 'True', '1']: sqlDict[col] = 1 elif str(data[col]) in ['false', 'False', '0']: sqlDict[col] = 0 else: sqlDict[col] = -1 else: sqlDict[col] = data[col] #sqlStr = "INSERT INTO appsession (%s) VALUES (%s)" % (','.join(session.keys()), str(map(str, session.values())).strip('[*]')) strSQL = "INSERT INTO appsync (%s, lstuptmp) VALUES (%s, unix_timestamp())" % (','.join(sqlDict.keys()), str(map(str, sqlDict.values())).strip('[*]')) self.rdbCur.execute(strSQL) else: row = self.rdbCur.fetchone() for key in row: if row[key] <> None and (data.get(key, None) == None or data[key] == ''): data[key] = row[key] strSQL = """DELETE FROM appsync WHERE deviceID = '%s' and pkgName = '%s' """ % (data.get('deviceID', ''), data.get('pkgName', '')) self.rdbCur.execute(strSQL) self.updateAppssLst(data, sessionID) self.rdbCur.execute("COMMIT") return retFunc except Exception, e: self.rdbCur.execute("ROLLBACK") log.error("procSyncID:%s" % e) log.error(data) return 0
def passLog(self, devid): try: strSQL = """SELECT deviceID, pkgName, sID, tTM, objid, agentAllowAns, ansLogVer, ansVer, avgTP, bssid, cellId, duration, estTP, evtKey, evtValue, evtSyncID, startCode, exitCode, startTime, eventTime, initWifi, model, netType, numTotalHits, NWcharac, playAppPackageName, playTime, activeDownload, activeTime, totalDownload, linkspd, apfreq, ver, verCode, myfiOn, myfiAP, parentAppName, insTs from appsslog where deviceID = '%s' order by deviceID, pkgName, sID, tTM""" % (devid) ret = self.rdbCur.execute(strSQL) log.info("passLog:appsslog ret = %d " % ret) if ret > 0: rows = self.rdbCur.fetchall() cutTs = time.time() - 5 pdevid = 'NULL' ppkgname = 'NULL' psid = -9 for row in rows: if pdevid == row['deviceID'] and ppkgname == row['pkgName'] and psid == row['sID']: log.info("passLog: pass %s %s %s" % (pdevid, ppkgname, psid)) continue if row['insTs'] > cutTs: pdevid = row['deviceID'] ppkgname = row['pkgName'] psid = row['sID'] log.info("passLog: insTs(%d) > cutTs(%d) %s %s %s" % (row['insTs'], cutTs, pdevid, ppkgname, psid)) continue keepkeys = filter(lambda x: row[x] <> None, row) newrow = {k: row[k] for k in keepkeys} # log.info("passLog: before processLog") # log.info(newrow) self.processLog(newrow) strSQL = """DELETE from appsslog where deviceID = '%s' and pkgName = '%s' and sID = %d and tTM = %.3f""" % \ (newrow['deviceID'], newrow['pkgName'], newrow['sID'], newrow['tTM']) ret = self.rdbCur.execute(strSQL) log.info("passLog: delete appsslog %d (inTs:%.3f cutTs:%.3f) (%s, %s, %d, %.3f)" % (ret, newrow['insTs'], cutTs, newrow['deviceID'], newrow['pkgName'], newrow['sID'], newrow['tTM'])) return ret except Exception, e: log.error("passLog : %s" % e) log.error(newrow) raise e
async def set_all_challenges(lang: str) -> None: html = http_get(f'{URL}/{lang}/Challenges/') if html is None: log.error('challenges_page_not_found') return categories = extract_categories(html) if not categories: log.warn('fetch_all_categories_failed', lang=lang) return log.debug('fetched_categories', categories=categories, lang=lang) tp_func = partial(retrieve_category_info, lang) with ThreadPool(len(categories)) as tp: response = tp.map(tp_func, categories) timestamp = datetime.now().isoformat() await app.redis.set( f'{lang}.challenges', json.dumps({ 'body': response, 'last_update': timestamp })) await app.redis.set( f'{lang}.categories', json.dumps({ 'body': categories, 'last_update': timestamp })) for category_data in response: await app.redis.set( f'{lang}.categories.{category_data[0]["name"]}', json.dumps({ 'body': category_data, 'last_update': timestamp })) log.debug('set_all_challenges_success', lang=lang)
def __new__(cls, state, bssid, ssid='', regdtm='19000101000000', rssi=-200, bregap=False, bmap=False, optrcom='none', geoloc=None, priority=Priority.NORMAL): # Classify WiFi try: if ssid not in ('', None): ssid = re.sub(r'^\s*"(.*)"\s*$', r'\1', unicode(ssid)) if ssid.find('"') >= 0: log.error("!!! SSID - %s" % ssid) if cls.isHotspot(ssid): priority = Priority.LOW else: optrcom = cls.getWiFiOperator(ssid) bregap = True if optrcom != 'none' else False if not bregap: bmap = cls.isMobile(ssid) try: ssid = MySQLdb.escape_string(unicode(ssid).encode('utf-8')) except Exception, e: # Non-ascii data. log.warn("SSID MySQLdb.escape_string Error - %s, %s" % (ssid, e)) if not geoloc: geoloc = GeoInfo()
def insertVidnet(waveCursor, vidDict): if vidDict == None: log.warn('vidnetDict is null') return False cols = vidDict.keys() vals = vidDict.values() try: slist = [] for v in vals: if type(v) == str or type(v) == unicode: slist.append("'" + v.replace("'", "''") + "'") else: slist.append(str(v)) sql = """insert into vidsession (%s, lstuptmp) values (%s, unix_timestamp())""" % (",".join(cols), unicode(",", "utf-8").join(slist)) waveCursor.execute(sql) except Exception, e: log.error("INSERT VIDNET ERROR:%s" % e) log.error(vidDict) raise e
def processNWcharac(self, alog): try: if alog.get('bssid', '') == '' or alog['bssid'] == None: return bssid = alog['bssid'] SumfrLst = [{'fq':0, 'avgrssi':0} for i in range(10)] SumTpDict = {} cocoaAvg = {'freq':0, 'estTP':0.0} tpAvg = {'freq':0, 'avgTP':0.0} #get saved nwcharac values from RDB strSQL = "SELECT itype, lvl, freq, fvalue FROM apnwc WHERE bssid = '%s'" % bssid ret = self.rdbCur.execute(strSQL) if ret > 0: rows = self.rdbCur.fetchall() for row in rows: if row['itype'] == '0': SumfrLst[row['lvl']]['avgrssi'] = row['fvalue'] SumfrLst[row['lvl']]['fq'] = row['freq'] elif row['itype'] == '1': SumTpDict[str(row['lvl'])] = row['freq'] elif row['itype'] == '2': cocoaAvg = {'freq':row['freq'], 'estTP':row['fvalue']} elif row['itype'] == '3': tpAvg = {'freq':row['freq'], 'avgTP':row['fvalue']} else: log.warn("processNWcharac Unknow itype %s:%s" % (bssid, row['itype'])) continue #parse aatlog's NWcharac string & store into temporary storage if alog.get('NWcharac', None) <> None and alog['NWcharac'] > '': nwstr = alog['NWcharac'].split('|') if len(nwstr) == 2: fqrssiLst = nwstr[0].split('.') for i, fqrssi in enumerate(fqrssiLst): fqlst = fqrssi.split(',') if len(fqlst) == 2 and fqlst[0].isdigit() and fqlst[0] > 0 and fqlst[1].isdigit(): #RSSI AVG: frequency number * rssi / frequency number = (lastest) avg. rssi #RSSI AVG Accumulated: (lastest avg. rssi * sum of frequency number + new freq * rssi) / (sum of freq number + new freq) SumfrLst[i]['avgrssi'] = float(SumfrLst[i]['avgrssi']*SumfrLst[i]['fq'] + int(fqlst[0])*int(fqlst[1])) / float(SumfrLst[i]['fq']+int(fqlst[0])) SumfrLst[i]['fq'] += int(fqlst[0]) varTPLst = nwstr[1].split('.') for varTP in varTPLst: TPLst = varTP.split(',') if len(TPLst) == 2 and TPLst[0].isdigit(): if SumTpDict.has_key(TPLst[0]): SumTpDict[TPLst[0]] += 1 else: SumTpDict[TPLst[0]] = 1 if alog.has_key('estTP') and alog['estTP'] <> None: if alog['estTP'] > 0: cocoaAvg['freq'] += 1 cocoaAvg['estTP'] += int(alog['estTP']) if alog.has_key('avgTP') and alog['avgTP'] <> None: if alog['avgTP'] > 0: tpAvg['freq'] += 1 tpAvg['avgTP'] += alog['avgTP'] #update RDB data valuesLst = [] for i, fr in enumerate(SumfrLst): strvalue = "('%s', '0', %d, %d, %f)" % (bssid, i, fr['fq'], fr['avgrssi']) valuesLst.append(strvalue) for key in SumTpDict: strvalue = "('%s', '1', %d, %d, 0.0)" % (bssid, int(key), SumTpDict[key]) valuesLst.append(strvalue) strvalue = "('%s', '2', 0, %d, %f)" % (bssid, cocoaAvg['freq'], cocoaAvg['estTP']) valuesLst.append(strvalue) strvalue = "('%s', '3', 0, %d, %f)" % (bssid, tpAvg['freq'], tpAvg['avgTP']) valuesLst.append(strvalue) strSQL = """INSERT INTO apnwc (bssid, itype, lvl, freq, fvalue) VALUES %s ON DUPLICATE KEY UPDATE freq = VALUES(freq), fvalue = VALUES(fvalue) """ % ', '.join(valuesLst) ret = self.rdbCur.execute(strSQL) except Exception, e: log.error("processNWcharac: %s" % e) log.error(alog)
acc = IF(VALUES(seq) > seq, VALUES(acc), acc), geosrc=VALUES(geosrc)""" try: strSql = strSql % (node.bssid, node.ssid, node.regdtm, int(node.bregap), int(node.bmap), node.geoloc.lat, node.geoloc.lng, node.geoloc.acc, node.geoloc.geosrc, node.optrcom, node.rssi) except Exception, e: log.error("SQL GEN ERR - %s" % bytes(node.ssid)) strSql = strSql % (node.bssid, '', node.regdtm, int(node.bregap), int(node.bmap), node.geoloc.lat, node.geoloc.lng, node.geoloc.acc, node.geoloc.geosrc, node.optrcom, node.rssi) try: cursor.execute(strSql) log.debug("INSERT - %s" % node.bssid) except Exception, e: # Duplicate entry error if e[0] != 1062: log.error(e) log.error(strSql) return False return True netTypeCode = {'gsm':1, 'cdma':2, 'lte':3} def addCellTower(cursor, node): strSql = """INSERT INTO apmain.cellinfo (fullid, plmnid, cellid, lac, celltype, regdtm, lat, lng, acc, geosrc, seq) VALUES('%s','%s','%s','%s','%d','%s','%s','%f','%f','%s', '1') ON DUPLICATED UPDATE lat=((lat*seq)+VALUES(lat))/(seq+1), lng=((lng*seq)+VALUES(lng))/(seq+1), seq=seq+1, geosrc=VALUES(geosrc)"""
netDict['ntype'] = 'w' netDict['bssid'] = netElm[4] netDict['ssid'] = netElm[3].replace("'", "''") netDict['traffic'] = netElm[6] rstNetList.append(netDict.copy()) else: if len(netElm) == 5: netDict['playSessionID'] = aatLog['playSessionId'] netDict['stTime'] = netElm[0] netDict['ntype'] = 'm' netDict['traffic'] = netElm[4] rstNetList.append(netDict.copy()) except Exception, e: log.error("getNetconnInfo error:[%s]%s" % (type(e), e)) log.error(aatLog) raise e #Following function is not related to Open DD. You should ignore it def insertNetInfo(waveCursor, netList): try: strLst = [] for netElm in netList: if netElm['ntype'] == 'w': strValue = "('%s', %s, '%s', '%s', '%s', %s, unix_timestamp())" % \ (netElm['playSessionID'], netElm['stTime'], netElm['ntype'], \ netElm['bssid'], netElm['ssid'], netElm['traffic']) else: strValue = "('%s', %s, '%s', NULL, NULL, %s, unix_timestamp())" % \ (netElm['playSessionID'], netElm['stTime'], netElm['ntype'], netElm['traffic'])
def handler(self, aatLog): try: waveCursor = None #update apmain.mdev's plmnid for hoppin case if aatLog.get('confOperator', '') > '': updateMcc(aatLog.get('deviceID', ''), aatLog['confOperator']) #in case of Off Log, process only End(0) log if int(aatLog.get('agentAatOnOff', -1)) == 0: if aatLog.get('agentLogType', -1) <> 0: return curAnID = aatLog.get('deviceID').strip(" u'") curPkgname = aatLog.get('pkgName').strip(" u'") curPsID = aatLog.get('playSessionId', '').strip(" u'") curTTM = Decimal(aatLog.get('tTM', 0.0)) curEndTm = int(aatLog.get('agentLogEndTime', 0)) waveCursor = worker.dbmanager.allocDictCursor('myapwave') waveCursor.execute("START TRANSACTION") strSQL= None strSQL = """SELECT a.*, IFNULL(b.psid, '') AS bpsid, b.*, e.* FROM (SELECT m.*, MAX(n.tTM) AS maxTTM, MAX(IF(n.tTM = %.3f, 1, 0) ) AS bExist FROM vidsession m LEFT OUTER JOIN vidsession_log n ON m.playSessionID = n.playSessionID WHERE m.playSessionID = '%s') a LEFT OUTER JOIN (SELECT playSessionID AS psid, MAX(tTM) AS lstTTM, SUBSTR(MAX(CONCAT(RPAD(tTM, 14, '0'), logType)), 15) AS mLogType, SUBSTR(MAX(CONCAT(RPAD(tTM, 14, '0'), logEndTime)), 15) AS mLogEndTime, SUBSTR(MAX(CONCAT(RPAD(tTM, 14, '0'), IFNULL(bufferState, '0'))), 15) AS mBufferState, SUM(playTime) AS mPlayTime, SUM(pauseTime) AS mPauseTime, SUM(elapsedTime) AS mElapsedTime, SUM(cellRxBytes) AS mCellBytes, SUM(wfRxBytes) AS mWFBytes, SUM(cellDuration) AS mCellDur, SUM(wfDuration) AS mWFDur FROM vidsession_log WHERE playSessionID = '%s' AND tTM < %.3f) b ON a.playSessionID = b.psid LEFT OUTER JOIN (SELECT playSessionID AS psid, MIN(tTM) AS nextTTM FROM vidsession_log WHERE playSessionID = '%s' AND tTM > %.3f ) e ON a.playSessionID = e.psid """ % (curTTM, curPsID, curPsID, curTTM, curPsID, curTTM) ret = waveCursor.execute(strSQL) if ret > 0: row = waveCursor.fetchone() if row['playSessionID'] <> None: if row['bExist'] == 1: return else: vidupdate(waveCursor, aatLog, row) else: vidcreate(waveCursor, aatLog) else: # Insert new playsession vidcreate(waveCursor, aatLog) # get BB, BW #Following code is not related to Open DD. You should ignore it. #### BEGIN - IGNORE logSubList = [] getBBinfo(logSubList , aatLog) insertBBSQL(waveCursor, logSubList) logSubList = [] getNetConn(logSubList, aatLog) insertNetInfo(waveCursor, logSubList) #### END - IGNORE waveCursor.execute("COMMIT") except Exception, e: log.error("processAATLOG : %s" % e) log.error(aatLog) if strSQL <> None: log.error(strSQL) if waveCursor <> None: waveCursor.execute("ROLLBACK") if str(e).find('Deadlock')> 0: log.error("processAAATLog raise e") raise e
def vidupdate(waveCursor, aatLog, row): try: vidDict = {} vidLogDict = {} #get some values to use cellid = "%s_%s_%s" % (aatLog.get('confOperator', ''), aatLog.get('netCID', ''), aatLog.get('netLAC', '')) psmode = int(aatLog['playServiceMode']) logType = aatLog.get('agentLogType', -1) if aatLog.get('netActiveNetwork', '').find('WIFI') >= 0: netType = '0' elif aatLog.get('netActiveNetwork', '').find('mobile') >= 0: netType = '1' else: netType = '2' batteryStart = 0 batteryEnd = 0 batteryValid = 0 if aatLog.has_key('batteryInfo'): btList = aatLog['batteryInfo'].split('|') if len(btList) == 2: if len(btList[0].split('/')) >= 5 and len(btList[1].split('/')) >= 5: nTotLevel = float(btList[0].split('/')[3]) nBatLevel = float(btList[0].split('/')[4]) batteryStart = (nBatLevel/nTotLevel)*100 nTotLevel = float(btList[1].split('/')[3]) nBatLevel = float(btList[1].split('/')[4]) batteryEnd = (nBatLevel/nTotLevel)*100 if btList[1].split('/')[1] == 'DISCHARGING': #All batteryInfo reporting log must be 'DISCHARGING' except first. batteryValid = 1 else: batteryValid = 0 elif len(btList) == 1: if len(btList[0].split('/')) >= 5: nTotLevel = float(btList[0].split('/')[3]) nBatLevel = float(btList[0].split('/')[4]) batteryStart = (nBatLevel/nTotLevel)*100 batteryEnd = batteryStart batteryValid = 0 #get appSessionID appSessionId = '' strSQL = """SELECT sessionID FROM appsession WHERE androidID = '%s' and pkgnm = '%s' and sID = %d and (startTime - 5) <= %d and startTime > 0 and (endTime > %d or statAppss > '0') ORDER BY sessionID DESC LIMIT 1""" % (aatLog['deviceID'], aatLog['pkgName'], aatLog['sID'], aatLog['agentLogStartTime'], aatLog['agentLogStartTime']) ret = waveCursor.execute(strSQL) if ret > 0: aarow = waveCursor.fetchone() if aarow['sessionID'] > '': appSessionId = aarow['sessionID'] #vidsession_log values getVidLogStatic(vidLogDict, aatLog, appSessionId, netType) #initialize if as-is record has no valid value. if row['androidID'] == '': vidDict['androidID'] = aatLog.get('deviceID', '') if row['vID'] == '': vidDict['vID'] = aatLog.get('vID', '') if row['sID'] == 0: vidDict['sID'] = aatLog.get('sID', 0) if row['verCode'] == 0: vidDict['verCode'] = aatLog.get('verCode', 0) if row['osVer'] == '': vidDict['osVer'] = aatLog.get('osVer', '') if row['brand'] == '': vidDict['brand'] = aatLog.get('brand', '') if row['model'] == '': vidDict['model'] = aatLog.get('model', '') if row['cellIdSt'] == '' and len(cellid) > 6: vidDict['cellIdSt'] = cellid if row['cellIdEnd'] == '' and len(cellid) > 6: vidDict['cellIdEnd'] = cellid if row['bMao'] < 0: vidDict['bMao'] = int(aatLog.get('agentAatOnOff', -1)) if row['bAnsAllow'] < 0: vidDict['bAnsAllow'] = int(aatLog.get('agentAllowAns', -1)) if row['bCellAllow'] < 0: vidDict['bCellAllow'] = int(aatLog.get('agentAllowMobile', -1)) if row['ansMode'] == '': vidDict['ansMode'] = aatLog.get('agentAnsMode', -1) if row['agentUserSetup'] == '': vidDict['agentUserSetup'] = aatLog.get('agentUserSetup', '') #if row['startLogType'] == '': vidDict['ansMode'] = aatLog.get('agentAnsMode', -1) if row['hostName'] == '': vidDict['hostName'] = aatLog.get('playHost', '') if row['originName'] == '': vidDict['originName'] = aatLog.get('playOrigin', '') if row['contentID'] == '': vidDict['contentID'] = aatLog.get('playContentId', '') if row['playServiceMode'] <= 0: vidDict['playServiceMode'] = aatLog.get('playServiceMode', 0) if row['contentSize'] == 0: if psmode == 1: vidDict['contentSize'] = aatLog.get('vodContentSize', 0) elif psmode == 4: vidDict['contentSize'] = aatLog.get('audContentSize', 0) elif psmode == 5: vidDict['contentSize'] = aatLog.get('adnContentSize', 0) if row['contentDuration'] == 0: if psmode == 1: vidDict['contentDuration'] = aatLog.get('vodContentDuration', 0) elif psmode == 4: vidDict['contentDuration'] = aatLog.get('audContentDuration', 0) if row['contentBitrate'] == 0 and psmode in [2,3]: vidDict['contentBitrate'] = aatLog.get('liveCurrentTSBitrate', 0) #if row['channelName'] == '': vidDict['channelName'] = aatLog.get('playTitle', '').encode('utf-8') if row['channelName'] == '': vidDict['channelName'] = aatLog.get('playTitle', '') if row['pkgnm'] == '': vidDict['pkgnm'] = aatLog.get('pkgName', '') if row['apppkgnm'] == '' or row['appvercd'] == '': if(aatLog.has_key('playAppPackageName')): appPkgs = aatLog['playAppPackageName'].split('/') if len(appPkgs) >= 2: vidDict['apppkgnm'] = appPkgs[0] vidDict['appvercd'] = appPkgs[1] if row['connectedNetCnt'] == 0: if aatLog.has_key('netConnectedNetworkCount'): vidDict['connectedNetCnt']=aatLog['netConnectedNetworkCount'] elif aatLog.has_key('netConnectivityCount'): vidDict['connectedNetCnt']=aatLog['netConnectivityCount'] if row['abrBitrateList'] == '': vidDict['abrBitrateList'] = aatLog.get('playBitrateList', '') if row['abrUserSelBR'] == '': vidDict['abrUserSelBR'] = aatLog.get('userSelectBitrate', '') if psmode == 5: if row['vidnetType'] == 0: vidDict['vidnetType'] = aatLog.get('adnStartCode', 0) if row['adnMode'] == '' or (row['adnMode'] <> 'BB' and aatLog.get('adnMode', '') == 'BB') : vidDict['adnMode'] = aatLog.get('adnMode', '') if row['adnRangeStart'] == 0: vidDict['adnRangeStart'] = aatLog.get('adnContentRangeStart', 0) if row['adnDownSize'] < aatLog.get('adnDownloadSize', 0): vidDict['adnDownSize'] = aatLog.get('adnDownloadSize', 0) if row['contentDuration'] < aatLog.get('adnDownloadTime', 0): vidDict['contentDuration'] = aatLog.get('adnDownloadTime', 0) if row['adnContentID'] == 0: vidDict['adnContentID'] = aatLog.get('adnContentID', 0) vidDict['cellSysRxBytes'] = row['cellSysRxBytes'] + aatLog.get('trafficSystemMoRxBytes', 0) vidDict['wfSysRxBytes'] = row['wfSysRxBytes'] + aatLog.get('trafficSystemWFRxBytes', 0) # process attributes depending on log-order if aatLog['tTM'] > row['maxTTM']: #The log is the last of this playSession if len(cellid) > 6: vidDict['cellIdEnd'] = cellid vidDict['endLogType'] = logType vidDict['vidnetEndTime'] = aatLog.get('agentLogEndTime', 0) vidDict['vidnetDuration'] = vidDict['vidnetEndTime'] - row['vidnetStartTime'] if aatLog.get('playPlayingTime', 0) > row['playTime']: vidDict['playTime'] = aatLog['playPlayingTime'] if aatLog.get('playSeekCount', 0) > row['seekCnt']: vidDict['seekCnt'] = aatLog['playSeekCount'] if aatLog.get('playSeekForwardCount', 0) > row['ffCnt']: vidDict['ffCnt'] = aatLog['playSeekForwardCount'] if aatLog.get('playSeekRewindCount', 0) > row['rwCnt']: vidDict['rwCnt'] = aatLog['playSeekRewindCount'] if aatLog.has_key('netConnectedNetworkCount'): if row['connectedNetCnt'] < aatLog['netConnectedNetworkCount']: vidDict['connectedNetCnt']=aatLog['netConnectedNetworkCount'] elif aatLog.has_key('netConnectivityCount'): if row['connectedNetCnt'] < aatLog['netConnectivityCount']: vidDict['connectedNetCnt']=aatLog['netConnectivityCount'] if psmode in [1, 4, 5]: vidDict['pauseCnt'] = aatLog.get('playBufferingCount', 0) vidDict['resumeCnt'] = aatLog.get('playResumeCount', 0) if aatLog.get('playAccBufferingTime', 0) > row['pauseTime']: vidDict['pauseTime'] = aatLog.get('playAccBufferingTime', 0) if aatLog.get('playMaxBufferingTime', 0) > row['maxPauseTime']: vidDict['maxPauseTime'] = aatLog.get('playMaxBufferingTime', 0) if aatLog.get('trafficAgentMoBytes', 0) > row['cellRxBytes']: vidDict['cellRxBytes'] = aatLog['trafficAgentMoBytes'] if aatLog.get('trafficAgentWFBytes', 0) > row['wfRxBytes']: vidDict['wfRxBytes'] = aatLog['trafficAgentWFBytes'] vidDict['cellAvgTP'] = round(aatLog.get('trafficAgentMoAveBW',0), 4) vidDict['wfAvgTP'] = round(aatLog.get('trafficAgentWFAveBW',0), 4) if vidDict['cellAvgTP'] > 0: vidDict['cellDuration'] = int((aatLog.get('trafficAgentMoBytes', 0)*8) / (aatLog['trafficAgentMoAveBW']*1000000)) if vidDict['wfAvgTP'] > 0: vidDict['wfDuration'] = int((aatLog.get('trafficAgentWFBytes', 0)*8) / (aatLog['trafficAgentWFAveBW']*1000000)) vidDict['batteryEnd'] = batteryEnd #get appSessionID for vidsession strSQL = """SELECT sessionID FROM appsession WHERE androidID = '%s' and pkgnm = '%s' and sID = %d and startTime < %d and startTime > 0 and ((endTime+5) > %d or statAppss > '0') ORDER BY sessionID DESC LIMIT 1""" % (aatLog['deviceID'], aatLog['pkgName'], aatLog['sID'], aatLog['agentLogEndTime'], aatLog['agentLogEndTime']) ret = waveCursor.execute(strSQL) if ret > 0: aarow = waveCursor.fetchone() if aarow['sessionID'] > '': vidDict['appSessionIDEnd'] = aarow['sessionID'] #vidsession_log values if aatLog.get('playPlayingTime', 0) > row['playTime']: vidLogDict['playTime'] = aatLog.get('playPlayingTime', 0) - row['playTime'] else: vidLogDict['playTime'] = 0 if aatLog.get('playAccBufferingTime', 0) > row['pauseTime']: vidLogDict['pauseTime'] = aatLog.get('playAccBufferingTime', 0) - row['pauseTime'] else: vidLogDict['pauseTime'] = 0 if aatLog.get('playPreparingTime', 0) > row['elapsedTime']: vidLogDict['elapsedTime'] = aatLog.get('playPreparingTime', 0) - row['elapsedTime'] else: vidLogDict['elapsedTime'] = 0 if aatLog.get('trafficAgentMoBytes', 0) > row['cellRxBytes']: vidLogDict['cellRxBytes'] = aatLog.get('trafficAgentMoBytes', 0) - row['cellRxBytes'] else: vidLogDict['cellRxBytes'] = 0 if aatLog.get('trafficAgentWFBytes', 0) > row['wfRxBytes']: vidLogDict['wfRxBytes'] = aatLog.get('trafficAgentWFBytes', 0) - row['wfRxBytes'] else: vidLogDict['wfRxBytes'] = 0 if vidDict['cellAvgTP'] > 0 and vidDict['cellDuration'] > row['cellDuration']: vidLogDict['cellDuration'] = vidDict['cellDuration'] - row['cellDuration'] else: vidLogDict['cellDuration'] = 0 if vidDict['wfAvgTP'] > 0 and vidDict['wfDuration'] > row['wfDuration']: vidLogDict['wfDuration'] = vidDict['wfDuration'] - row['wfDuration'] else: vidLogDict['wfDuration'] = 0 elif row['bpsid'] == '': # The log is the first of this playSession if len(cellid) > 6: vidDict['cellIdSt'] = cellid vidDict['startLogType'] = logType vidDict['vidnetStartTime'] = aatLog.get('agentLogStartTime', 0) vidDict['vidnetDuration'] = row['vidnetEndTime'] - vidDict['vidnetStartTime'] vidDict['batteryStart'] = batteryStart if appSessionId > '': vidDict['appSessionIDSt'] = appSessionId #vidsession_log values vidLogDict['playTime'] = aatLog.get('playPlayingTime', 0) vidLogDict['pauseTime'] = aatLog.get('playAccBufferingTime', 0) vidLogDict['elapsedTime'] = aatLog.get('playPreparingTime', 0) vidLogDict['cellRxBytes'] = aatLog.get('trafficAgentMoBytes', 0) vidLogDict['wfRxBytes'] = aatLog.get('trafficAgentWFBytes', 0) if round(aatLog.get('trafficAgentMoAveBW',0), 4) > 0: vidLogDict['cellDuration'] = int((aatLog.get('trafficAgentMoBytes', 0)*8) / (aatLog['trafficAgentMoAveBW']*1000000)) else: vidLogDict['cellDuration'] = 0 if round(aatLog.get('trafficAgentWFAveBW',0), 4) > 0: vidLogDict['wfDuration'] = int((aatLog.get('trafficAgentWFBytes', 0)*8) / (aatLog['trafficAgentWFAveBW']*1000000)) else: vidLogDict['wfDuration'] = 0 updateVidLog(waveCursor, vidLogDict, row) else: # The log is middle of this playSession if aatLog.get('playPlayingTime', 0) > row['mPlayTime']: vidLogDict['playTime'] = aatLog.get('playPlayingTime', 0) - row['mPlayTime'] else: vidLogDict['playTime'] = 0 if aatLog.get('playAccBufferingTime', 0) > row['mPauseTime']: vidLogDict['pauseTime'] = aatLog.get('playAccBufferingTime', 0) - row['mPauseTime'] else: vidLogDict['pauseTime'] = 0 if aatLog.get('playPreparingTime', 0) > row['mElapsedTime']: vidLogDict['elapsedTime'] = aatLog.get('playPreparingTime', 0) - row['mElapsedTime'] else: vidLogDict['elapsedTime'] = 0 if aatLog.get('trafficAgentMoBytes', 0) > row['mCellBytes']: vidLogDict['cellRxBytes'] = aatLog.get('trafficAgentMoBytes', 0) - row['mCellBytes'] else: vidLogDict['cellRxBytes'] = 0 if aatLog.get('trafficAgentWFBytes', 0) > row['mWFBytes']: vidLogDict['wfRxBytes'] = aatLog.get('trafficAgentWFBytes', 0) - row['mWFBytes'] else: vidLogDict['wfRxBytes'] = 0 vidLogDict['cellDuration'] = 0 vidLogDict['wfDuration'] = 0 if round(aatLog.get('trafficAgentMoAveBW',0), 4) > 0: tempdur = int((aatLog.get('trafficAgentMoBytes', 0)*8) / (aatLog['trafficAgentMoAveBW']*1000000)) if tempdur > row['mCellDur']: vidLogDict['cellDuration'] = tempdur - int(row['mCellDur']) if round(aatLog.get('trafficAgentWFAveBW',0), 4) > 0: tempdur = int((aatLog.get('trafficAgentWFBytes', 0)*8) / (aatLog['trafficAgentWFAveBW']*1000000)) if tempdur > row['mWFDur']: vidLogDict['wfDuration'] = tempdur - int(row['mWFDur']) updateVidLog(waveCursor, vidLogDict, row) # process independent attributes not depending on log-order if psmode in [2, 3]: if logType == 6: vidDict['pauseCnt'] = row['pauseCnt'] + 1 elif logType == 7: vidDict['resumeCnt'] = row['resumeCnt'] + 1 if logType in [5, 8]: if netType == '0': #WIFI vidDict['netW2CTransferCnt'] = row['netW2CTransferCnt'] + 1 elif netType == '1': #mobile vidDict['netC2WTransferCnt'] = row['netC2WTransferCnt'] + 1 if row['batteryValid'] == '1' and batteryValid == 0: vidDict['batteryValid'] = '0' if aatLog.get('netCellState', -1) > 0 and row['netAllowCell'] == '1': #The log not allow cell, as-is fully allowed. vidDict['netAllowCell'] = '2' elif aatLog.get('netCellState', -1) == 0 and row['netAllowCell'] == '0': #The log allow cell, as-is not allowed at all. vidDict['netAllowCell'] = '2' vidDict['bbCount'] = row['bbCount'] + aatLog.get('bbCount', 0) if row['elapsedTime'] == 0 and aatLog.get('playPreparingTime', 0) > 0: vidDict['elapsedTime'] = aatLog['playPreparingTime'] elif row['mLogType'] == 10 and row['mBufferState'] == '2' and aatLog.get('playPreparingTime', 0) > 0: vidDict['elapsedTime'] = row['elapsedTime'] + aatLog['playPreparingTime'] #insert tables vidDict['playSessionID'] = row['playSessionID'] updateVidnet(waveCursor, vidDict) insertVidnetLog(waveCursor, vidLogDict) except Exception, e: log.error("vidupdate %s" % e) log.error(aatLog) log.error(vidDict) raise e
def vidcreate(waveCursor, aatLog): try: vidDict = {} vidLogDict = {} #get some values to use cellid = "%s_%s_%s" % (aatLog.get('confOperator', ''), aatLog.get('netCID', ''), aatLog.get('netLAC', '')) psmode = int(aatLog['playServiceMode']) logType = aatLog.get('agentLogType', -1) if aatLog.get('netActiveNetwork', '').find('WIFI') >= 0: netType = '0' elif aatLog.get('netActiveNetwork', '').find('mobile') >= 0: netType = '1' else: netType = '2' vidDict['playSessionID'] = aatLog['playSessionId'] vidDict['androidID'] = aatLog.get('deviceID', '') vidDict['vID'] = aatLog.get('vID', '') vidDict['sID'] = aatLog.get('sID', 0) vidDict['verCode'] = aatLog.get('verCode', 0) vidDict['osVer'] = aatLog.get('osVer', '') vidDict['brand'] = aatLog.get('brand', '') vidDict['model'] = aatLog.get('model', '') vidDict['cellIdSt'] = cellid vidDict['cellIdEnd'] = cellid vidDict['bMao'] = int(aatLog.get('agentAatOnOff', -1)) vidDict['bAnsAllow'] = int(aatLog.get('agentAllowAns', -1)) vidDict['bCellAllow'] = int(aatLog.get('agentAllowMobile', -1)) vidDict['ansMode'] = aatLog.get('agentAnsMode', -1) vidDict['agentUserSetup'] = aatLog.get('agentUserSetup', '') #vidDict['ansMode'] = aatLog.get('agentAnsMode', -1) vidDict['hostName'] = aatLog.get('playHost', '') vidDict['originName'] = aatLog.get('playOrigin', '') vidDict['contentID'] = aatLog.get('playContentId', '') vidDict['playServiceMode'] = psmode if psmode == 1: vidDict['contentSize'] = aatLog.get('vodContentSize', 0) elif psmode == 4: vidDict['contentSize'] = aatLog.get('audContentSize', 0) elif psmode == 5: vidDict['contentSize'] = aatLog.get('adnContentSize', 0) else: vidDict['contentSize'] = 0 if psmode == 1: vidDict['contentDuration'] = aatLog.get('vodContentDuration', 0) elif psmode == 4: vidDict['contentDuration'] = aatLog.get('audContentDuration', 0) elif psmode == 5: vidDict['contentDuration'] = aatLog.get('adnDownloadTime', 0) else: vidDict['contentDuration'] = 0 if psmode in [2,3]: vidDict['contentBitrate'] = aatLog.get('liveCurrentTSBitrate', 0) else: vidDict['contentBitrate'] = 0 #vidDict['channelName'] = aatLog.get('playTitle', '').encode('utf-8') vidDict['channelName'] = aatLog.get('playTitle', '') vidDict['pkgnm'] = aatLog.get('pkgName', '') vidDict['apppkgnm'] = "" vidDict['appvercd'] = "" if(aatLog.has_key('playAppPackageName')): appPkgs = aatLog['playAppPackageName'].split('/') if len(appPkgs) >= 2: vidDict['apppkgnm'] = appPkgs[0] vidDict['appvercd'] = appPkgs[1] if aatLog.has_key('netConnectedNetworkCount'): vidDict['connectedNetCnt']=aatLog['netConnectedNetworkCount'] elif aatLog.has_key('netConnectivityCount'): vidDict['connectedNetCnt']=aatLog['netConnectivityCount'] else: vidDict['connectedNetCnt']=0 vidDict['abrBitrateList'] = aatLog.get('playBitrateList', '') vidDict['abrUserSelBR'] = aatLog.get('userSelectBitrate', '') if psmode == 5: vidDict['vidnetType'] = aatLog.get('adnStartCode', 0) vidDict['adnMode'] = aatLog.get('adnMode', '') vidDict['adnRangeStart'] = aatLog.get('adnContentRangeStart', 0) vidDict['adnDownSize'] = aatLog.get('adnDownloadSize', 0) vidDict['adnContentID'] = aatLog.get('adnContentID', 0) vidDict['startLogType'] = logType vidDict['endLogType'] = logType vidDict['vidnetStartTime'] = aatLog.get('agentLogStartTime', 0) vidDict['vidnetEndTime'] = aatLog.get('agentLogEndTime', 0) vidDict['vidnetDuration'] = vidDict['vidnetEndTime'] - vidDict['vidnetStartTime'] # process independent attributes not depending on log-order vidDict['pauseCnt'] = 0 vidDict['resumeCnt'] = 0 vidDict['netW2CTransferCnt'] = 0 vidDict['netC2WTransferCnt'] = 0 if psmode in [2, 3]: if logType == 6: vidDict['pauseCnt'] = 1 elif logType == 7: vidDict['resumeCnt'] = 1 elif psmode in [1, 4, 5]: vidDict['pauseCnt'] = aatLog.get('playBufferingCount', 0) vidDict['resumeCnt'] = aatLog.get('playResumeCount', 0) if logType in [5, 8]: if netType == '0': #WIFI vidDict['netW2CTransferCnt'] = 1 elif netType == '1': #mobile vidDict['netC2WTransferCnt'] = 1 vidDict['playTime'] = aatLog.get('playPlayingTime', 0) vidDict['seekCnt'] = aatLog.get('playSeekCount', 0) vidDict['ffCnt'] = aatLog.get('playSeekForwardCount', 0) vidDict['rwCnt'] = aatLog.get('playSeekRewindCount', 0) vidDict['pauseTime'] = aatLog.get('playAccBufferingTime', 0) vidDict['maxPauseTime'] = aatLog.get('playMaxBufferingTime', 0) vidDict['cellRxBytes'] = aatLog.get('trafficAgentMoBytes', 0) vidDict['wfRxBytes'] = aatLog.get('trafficAgentWFBytes', 0) vidDict['cellAvgTP'] = round(aatLog.get('trafficAgentMoAveBW',0), 4) vidDict['wfAvgTP'] = round(aatLog.get('trafficAgentWFAveBW',0), 4) vidDict['cellDuration'] = 0 vidDict['wfDuration'] = 0 vidDict['cellSysRxBytes'] = aatLog.get('trafficSystemMoRxBytes', 0) vidDict['wfSysRxBytes'] = aatLog.get('trafficSystemWFRxBytes', 0) if vidDict['cellAvgTP'] > 0: vidDict['cellDuration'] = int((aatLog.get('trafficAgentMoBytes', 0)*8) / (aatLog['trafficAgentMoAveBW']*1000000)) if vidDict['wfAvgTP'] > 0: vidDict['wfDuration'] = int((aatLog.get('trafficAgentWFBytes', 0)*8) / (aatLog['trafficAgentWFAveBW']*1000000)) batteryStart = 0 batteryEnd = 0 batteryValid = '0' if aatLog.has_key('batteryInfo'): btList = aatLog['batteryInfo'].split('|') if len(btList) == 2: if len(btList[0].split('/')) >= 5 and len(btList[1].split('/')) >= 5: nTotLevel = float(btList[0].split('/')[3]) nBatLevel = float(btList[0].split('/')[4]) batteryStart = (nBatLevel/nTotLevel)*100 nTotLevel = float(btList[1].split('/')[3]) nBatLevel = float(btList[1].split('/')[4]) batteryEnd = (nBatLevel/nTotLevel)*100 if btList[1].split('/')[1] == 'DISCHARGING': #All batteryInfo reporting log must be 'DISCHARGING' except first. batteryValid = 1 else: batteryValid = 0 elif len(btList) == 1: if len(btList[0].split('/')) >= 5: nTotLevel = float(btList[0].split('/')[3]) nBatLevel = float(btList[0].split('/')[4]) batteryStart = (nBatLevel/nTotLevel)*100 batteryEnd = batteryStart batteryValid = 0 vidDict['batteryStart'] = batteryStart vidDict['batteryEnd'] = batteryEnd vidDict['batteryValid'] = str(batteryValid) if aatLog.get('netCellState', -1) > 0: vidDict['netAllowCell'] = '0' elif aatLog.get('netCellState', -1) == 0: vidDict['netAllowCell'] = '1' vidDict['bbCount'] = aatLog.get('bbCount', 0) vidDict['elapsedTime'] = aatLog.get('playPreparingTime', 0) #get appSessionID vidDict['appSessionIDSt'] = '' vidDict['appSessionIDEnd'] = '' strSQL = """SELECT MAX(1) as ord, MAX(sessionID) as sessionID FROM appsession WHERE androidID = '%s' and pkgnm = '%s' and sID = %d and (startTime - 5) <= %d and startTime > 0 and (endTime > %d or statAppss > '0') UNION ALL SELECT MAX(2), MAX(sessionID) FROM appsession WHERE androidID = '%s' and pkgnm = '%s' and sID = %d and startTime < %d and startTime > 0 and ((endTime + 5) > %d or statAppss > '0') """ % (aatLog['deviceID'], aatLog['pkgName'], aatLog['sID'], aatLog['agentLogStartTime'], aatLog['agentLogStartTime'], aatLog['deviceID'], aatLog['pkgName'], aatLog['sID'], aatLog['agentLogEndTime'], aatLog['agentLogEndTime']) ret = waveCursor.execute(strSQL) if ret > 0: aarows = waveCursor.fetchall() for r in aarows: if r['sessionID'] > '' and r['sessionID'] <> None: if r['ord'] == 1: vidDict['appSessionIDSt'] = r['sessionID'] elif r['ord'] == 2: vidDict['appSessionIDEnd'] = r['sessionID'] #vidsession_log values getVidLogStatic(vidLogDict, aatLog, vidDict['appSessionIDSt'], netType) vidLogDict['playTime'] = aatLog.get('playPlayingTime', 0) vidLogDict['pauseTime'] = aatLog.get('playAccBufferingTime', 0) vidLogDict['elapsedTime'] = aatLog.get('playPreparingTime', 0) vidLogDict['cellRxBytes'] = aatLog.get('trafficAgentMoBytes', 0) vidLogDict['wfRxBytes'] = aatLog.get('trafficAgentWFBytes', 0) vidLogDict['cellSysRxBytes'] = aatLog.get('trafficSystemMoRxBytes', 0) vidLogDict['wfSysRxBytes'] = aatLog.get('trafficSystemWFRxBytes', 0) vidLogDict['cellDuration'] = 0 vidLogDict['wfDuration'] = 0 if round(aatLog.get('trafficAgentMoAveBW',0), 4) > 0: vidLogDict['cellDuration'] = int((aatLog.get('trafficAgentMoBytes', 0)*8) / (aatLog['trafficAgentMoAveBW']*1000000)) if round(aatLog.get('trafficAgentWFAveBW',0), 4) > 0: vidLogDict['wfDuration'] = int((aatLog.get('trafficAgentWFBytes', 0)*8) / (aatLog['trafficAgentWFAveBW']*1000000)) #insert tables insertVidnet(waveCursor, vidDict) insertVidnetLog(waveCursor, vidLogDict) except Exception, e: log.error("vidcreate %s" % e) log.error(aatLog) raise e