Ejemplo n.º 1
0
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
Ejemplo n.º 3
0
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
Ejemplo n.º 5
0
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