def write_isp_count(): filename = settings.PROJECT_ROOT + '/summary/device_data/isp_count' isps = Devicedetails.objects.all().distinct('geoip_isp').exclude( geoip_isp='').exclude(geoip_isp='unknown').values('geoip_isp') file = open(filename, 'w') earliest = datetime_helper.get_daterange_start(7) cursor = get_dict_cursor() for isp in isps: params = [] params.append(isp['geoip_isp']) SQL1 = "SELECT \ COUNT(*) as d_count \ FROM devicedetails \ WHERE geoip_isp=%s;" SQL2 = "SELECT \ COUNT(DISTINCT devicedetails.deviceid) as a_count \ FROM devicedetails join m_bitrate on devicedetails.deviceid=m_bitrate.deviceid \ WHERE geoip_isp=%s AND m_bitrate.eventstamp>%s;" cursor.execute(SQL1, params) rec = cursor.fetchone() device_count = rec['d_count'] params.append(earliest) cursor.execute(SQL2, params) rec = cursor.fetchone() active_count = rec['a_count'] line = isp['geoip_isp'].encode('utf-8') + '|' + str( device_count) + '|' + str(active_count) + '\n' file.write(line) cursor.close() file.close() return
def write_isp_count(): filename = settings.PROJECT_ROOT + '/summary/device_data/isp_count' isps = Devicedetails.objects.all().distinct('geoip_isp').exclude(geoip_isp='').exclude(geoip_isp='unknown').values('geoip_isp') file = open(filename, 'w') earliest=datetime_helper.get_daterange_start(7) cursor = get_dict_cursor() for isp in isps: params=[] params.append(isp['geoip_isp']) SQL1 = "SELECT \ COUNT(*) as d_count \ FROM devicedetails \ WHERE geoip_isp=%s;" SQL2 = "SELECT \ COUNT(DISTINCT devicedetails.deviceid) as a_count \ FROM devicedetails join m_bitrate on devicedetails.deviceid=m_bitrate.deviceid \ WHERE geoip_isp=%s AND m_bitrate.eventstamp>%s;" cursor.execute(SQL1,params) rec = cursor.fetchone() device_count = rec['d_count'] params.append(earliest) cursor.execute(SQL2,params) rec = cursor.fetchone() active_count = rec['a_count'] line = isp['geoip_isp'].encode('utf-8') + '|' + str(device_count) + '|' + str(active_count) + '\n' file.write(line) cursor.close() file.close() return
def write_coord_data(): filename = settings.PROJECT_ROOT + '/summary/measurements/map/coord_data' f = open(filename, 'w') devices = Devicedetails.objects.all() servers = IpResolver.objects.all() active_thresh = datetime_helper.get_daterange_start(7) for d in devices: try: id = d.deviceid if d.ip == "" or d.ip == None: continue loc = geoip_helper.get_location_by_ip(d.ip) if loc == None: continue lat = str(randomize_coordinate(loc['latitude'])) lon = str(randomize_coordinate(loc['longitude'])) hash = d.hashkey isp = d.geoip_isp active = 0 server = 0 if hash == "": continue recent_measurement_count = MBitrate.objects.filter( deviceid=id, eventstamp__gte=active_thresh).count() if recent_measurement_count > 0: active = 1 line = hash + '|' + lat + '|' + lon + '|' + isp + '|' + str( active) + '|' + str(server) + '\n' f.write(line) except: continue for s in servers: try: isp = "" active = 1 server = 1 loc = geoip_helper.get_location_by_ip(s.ip) if loc == None: continue lat = loc['latitude'] lon = loc['longitude'] hash = "" line = hash + '|' + lat + '|' + lon + '|' + isp + '|' + str( active) + '|' + str(server) + '\n' f.write(line) except: continue f.close() return
def write_coord_data(): filename = settings.PROJECT_ROOT + '/summary/measurements/map/coord_data' f = open(filename, 'w') devices = Devicedetails.objects.all() servers = IpResolver.objects.all() active_thresh = datetime_helper.get_daterange_start(7) for d in devices: try: id = d.deviceid if d.ip=="" or d.ip==None: continue loc = geoip_helper.get_location_by_ip(d.ip) if loc==None: continue lat = str(randomize_coordinate(loc['latitude'])) lon = str(randomize_coordinate(loc['longitude'])) hash = d.hashkey isp = d.geoip_isp active = 0 server = 0 if hash=="": continue recent_measurement_count = MBitrate.objects.filter(deviceid=id,eventstamp__gte=active_thresh).count() if recent_measurement_count>0: active=1 line = hash + '|' + lat + '|' + lon + '|' + isp + '|' + str(active) + '|' + str(server) + '\n' f.write(line) except: continue for s in servers: try: isp = "" active = 1 server = 1 loc = geoip_helper.get_location_by_ip(s.ip) if loc==None: continue lat = loc['latitude'] lon = loc['longitude'] hash = "" line = hash + '|' + lat + '|' + lon + '|' + isp + '|' + str(active) + '|' + str(server) + '\n' f.write(line) except: continue f.close() return
def write_devices(): devices = Devicedetails.objects.all() filename = settings.PROJECT_ROOT + '/summary/device_data/devices' cursor = get_dict_cursor() file = open(filename, 'w') for d in devices: SQL = '' last = '' params = [] active = 0 params.append(d.deviceid) active_thresh = datetime_helper.get_daterange_start(7) recent_measurement_count = MBitrate.objects.filter( deviceid=d.deviceid, eventstamp__gte=active_thresh).count() if recent_measurement_count > 0: active = 1 SQL = "SELECT \ deviceid, eventstamp, geoip_city, geoip_country, geoip_isp \ FROM devicedetails \ WHERE deviceid=%s" cursor.execute(SQL, params) rec = cursor.fetchone() id = d.deviceid if id == None: id = 'none' eventstamp = datetime_helper.datetime_to_JSON(rec['eventstamp']) city = rec['geoip_city'] if city == None: city = 'none' country = rec['geoip_country'] if country == None: country = 'none' isp = rec['geoip_isp'] if isp == None: isp = 'none' line = id + '|' + str(eventstamp) + '|' + city.encode( 'utf-8') + '|' + country.encode('utf-8') + '|' + isp.encode( 'utf-8') + '|' + str(active) + '\n' file.write(line) file.close() cursor.close() return
def write_devices(): devices = Devicedetails.objects.all() filename = settings.PROJECT_ROOT + '/summary/device_data/devices' cursor = get_dict_cursor() file = open(filename, 'w') for d in devices: SQL = '' last = '' params = [] active = 0 params.append(d.deviceid) active_thresh = datetime_helper.get_daterange_start(7) recent_measurement_count = MBitrate.objects.filter(deviceid=d.deviceid,eventstamp__gte=active_thresh).count() if recent_measurement_count>0: active=1 SQL = "SELECT \ deviceid, eventstamp, geoip_city, geoip_country, geoip_isp \ FROM devicedetails \ WHERE deviceid=%s" cursor.execute(SQL,params) rec = cursor.fetchone() id = d.deviceid if id==None: id='none' eventstamp = datetime_helper.datetime_to_JSON(rec['eventstamp']) city = rec['geoip_city'] if city==None: city='none' country = rec['geoip_country'] if country==None: country='none' isp = rec['geoip_isp'] if isp==None: isp='none' line = id + '|' + str(eventstamp) + '|' + city.encode('utf-8') + '|' + country.encode('utf-8') + '|' + isp.encode('utf-8') + '|' + str(active) + '\n' file.write(line) file.close() cursor.close() return