def __init__(self, parent):
tk.Frame.__init__(self, parent)
self.canvas_frame = tk.Frame(self)
self.canvas_frame.parent = self
self.alarms_canvas = tk.Canvas(self.canvas_frame)
self.alarms_canvas.parent = self.canvas_frame
self.alarms = tk.Frame(self.alarms_canvas)
self.alarms.parent = self.alarms_canvas
self.add_alarm_button = tk.Button(self,
text="Add Alarm",
command=self.add_alarm)
self.scrollbar = tk.Scrollbar(self.canvas_frame,
orient=tk.VERTICAL,
command=self.alarms_canvas.yview)
self.add_alarm_button.pack(side=tk.TOP, fill=tk.X, expand=0)
self.canvas_frame.pack(side=tk.TOP, fill=tk.BOTH, expand=1)
self.alarms_canvas.pack(side=tk.LEFT, fill=tk.BOTH, expand=1)
self.scrollbar.pack(side=tk.RIGHT, fill=tk.Y, expand=0)
# Creating long list of alarm frames. Values populated from database
self.db = SqlConnector()
self.alarm_frames = []
self.add_alarms()
self.thread = Thread(target=self.check_time, daemon=True)
self.thread.start()
self.alarms_canvas.bind("<Configure>", self.on_canvas_resize)
def query_daily_average_visitors():
'''Count the number of devices (non-randomized) detected within last 15
minutes'''
cmd = '''
SELECT AVG(unique_visitors) AS average_visitor_count
FROM
(
SELECT DATE(ts) day,
COUNT(DISTINCT mac_id) AS unique_visitors
FROM events
WHERE ts BETWEEN NOW() - INTERVAL 8 DAY AND NOW() - INTERVAL 1 DAY
GROUP BY day
) t1
;
'''
sql = SqlConnector()
sql.cursor.execute(cmd)
x = sql.cursor.fetchall()[0]['average_visitor_count']
if x is None:
value = 0
else:
value = x
sql.cursor.close()
return value
def __init__(self):
super(StudentForm, self).__init__()
uic.loadUi('liststudent.ui', self)
from sql_connector import SqlConnector
self.sql = SqlConnector()
self.setupParams()
self.setupEvents()
def query_mac_search(mac):
'''Query the Events table for unique mac addresses'''
cmd1 = '''
SELECT id mac_id
FROM mac_addresses
WHERE mac = %s;
'''
cmd2 = '''
SELECT COUNT(1) mac_count
FROM events
WHERE mac_id = %s;
'''
sql = SqlConnector()
sql.cursor.execute(cmd1, (mac,))
x = sql.cursor.fetchall()
if len(x) == 0:
return '0 entries'
else:
pass
mac_id = x[0]['mac_id']
sql.cursor.execute(cmd2, (mac_id,))
x = sql.cursor.fetchall()
mac_count = x[0]['mac_count']
sql.cursor.close()
mac_count = x[0]['mac_count']
if mac_count == 1:
mac_count = str(format(mac_count, ",d")) + ' entry'
else:
mac_count = str(format(mac_count, ",d")) + ' entries'
return mac_count
def get_connection(self, engine):
""" Get a specific engine object.
Args:
engine (str): Engine name.
Returns:
obj: Engine object.
"""
configuration = self._get_configuration(engine)
if engine == 'SQL':
return SqlConnector(configuration)
else:
raise ValueError(configuration)
def __init__(self, parent, id, time, repeat, sound, message, active):
self.db = SqlConnector() # Allows for access to database
self.id = id # Allows for database identification
self.time = time # Time for alarm to go off
self.repeat = repeat # Days of the week that alarm will go off, else One
self.sound = sound # Path to sound that will play when alarm goes off
self.active = active # Boolean value whether alarm will go off at time
self.message = message # Message that shows when alarm goes off
self.parent = parent
self.alarm_sound = None
tk.Frame.__init__(self, parent)
self.add_widgets()
self.configure(borderwidth=1, relief=tk.RAISED, background="black")
def query_current_devices(period=15):
'''Count the number of devices (non-randomized) detected within last 15
minutes'''
cmd = '''
SELECT COUNT(DISTINCT mac_id) current_device_count
FROM events
WHERE ts BETWEEN NOW() - INTERVAL %s MINUTE AND NOW();
'''
sql = SqlConnector()
sql.cursor.execute(cmd, (period,))
x = sql.cursor.fetchall()[0]['current_device_count']
sql.cursor.close()
return x
def query_unique_visitors(start_time='1999-12-31 23:59:59', end_time='2020-01-01 00:00:00'):
'''Query the Events table for unique mac addresses'''
cmd = '''
SELECT COUNT(DISTINCT mac_id) unique_visitors
FROM events
WHERE ts BETWEEN %s AND %s;
'''
sql = SqlConnector()
sql.cursor.execute(cmd, (start_time, end_time))
x = sql.cursor.fetchall()
sql.cursor.close()
unique_visitors = str(format(x[0]['unique_visitors'], ",d"))
return unique_visitors
def save_in_database(data):
db = SqlConnector(g_db1_name)
db2 = SqlConnector(g_db2_name)
total = len(data)
count = 0
for site in data:
count += 1
print("\033[92mSave in database: {0}/{1}\033[0m {2}".format(
count, total, site))
domain_id = db.load_domain_into_db(site)
domain_id2 = db2.load_domain_into_db(site)
for page in data[site]:
urlList = data[site][page]
page_id = db.load_page_into_db(page, domain_id)
page_id2 = db2.load_page_into_db(page, domain_id2)
for img in urlList:
if img.accessible == 0:
db2.load_method2_data(img.url, img.accessible,
img.content_length, img.domain,
page_id2, img.cookie)
db.load_method1_data(img.url, img.accessible, img.size,
img.domain, page_id, img.cookie)
else:
if img.content_type != None and img.content_type.find(
'image') != -1:
#store image URLs with any content_length
db2.load_method2_data(img.url, img.accessible,
img.content_length, img.domain,
page_id2, img.cookie)
#store image URLs with any size
db.load_method1_data(img.url, img.accessible, img.size,
img.domain, page_id, img.cookie)
def plot1(dbname, condition):
#providers of beacons - count of beacons
logname = open(u'plot1' + dbname + '.csv', 'w')
db = SqlConnector(dbname)
output = db.execute(
"SELECT image_domains.domain, count(images.id) \
as imgcount FROM images INNER JOIN image_domains ON \
image_domains.id = images.id_image_domains WHERE " +
condition + " group by image_domains.domain order by imgcount DESC;")
for item in output:
logname.write("{0};{1}\n".format(item[0], item[1]))
print(item)
logname.close()
def extract_twitter_pixels(dbname):
logp1 = open(u'twitter_pixels.txt', 'w')
db = SqlConnector(dbname)
output = db.execute(
"SELECT images.url, pages.url, images.width, images.height \
FROM images INNER JOIN pages ON \
pages.id = images.id_pages INNER JOIN image_domains ON \
images.id_image_domains = image_domains.id WHERE width<=1 \
and height<=1 and image_domains.domain = 'twitter.com';")
for item in output:
logp1.write("On page {0} w={1} h={2}\n\t {3}\n\n".format(
item[1], item[2], item[3], item[0]))
logp1.close()
def query_average_dwell(start_time, end_time):
'''Query the Average Dwell Time for all events within the time range'''
cmd = '''
SELECT AVG(dwell) dwell_time
FROM
(select count(1), mac_id, MIN(ts), TIMESTAMPDIFF(MINUTE, MIN(ts), MAX(TS)) dwell
FROM events
GROUP BY 2
#HAVING COUNT(1) > 1
HAVING dwell >= 1
LIMIT 10) t;
'''
sql = SqlConnector()
sql.cursor.execute(cmd)
x = sql.cursor.fetchall()
sql.cursor.close()
dwell_time = str(round(float(x[0]['dwell_time']), 1))
return dwell_time
def parse(text, f):
'''
Given text, parse the timestamp, MAC address, and signal strength.
Return as a list of entry objects.
'''
sql = SqlConnector()
file_date = re.search(r'data\_(\d{4}\-\d{2}-\d{2})', f).group(1)
lines = text.split('\n')
for line in lines:
if line == '':
continue
ts = ts_parser(line, file_date)
mac = mac_parser(line)
rssi = rssi_parser(line)
event = Event(ts, mac, rssi)
if event.is_random:
write_to_random_table(sql, event)
else:
write_to_events_table(sql, event)
sql.cnx.commit()
sql.cnx.close()
move_processed_file(f)
return None
def plot3(dbname, condition):
#users of beacons - % pages with at least 1 beacon
logname = open(u'plot3' + dbname + '.csv', 'w')
db = SqlConnector(dbname)
output = db.execute("SELECT domains.domain, count(distinct pages.id) \
as pagescount FROM images INNER JOIN pages ON \
pages.id = images.id_pages INNER JOIN domains ON \
pages.id_domains = domains.id group by domains.domain order by pagescount DESC;"
)
for item in output:
pages_count = item[1]
output = db.execute("SELECT count(distinct pages.id) \
as pagescount FROM images INNER JOIN pages ON \
pages.id = images.id_pages INNER JOIN domains ON \
pages.id_domains = domains.id WHERE domains.domain = '" +
item[0] + "' and " + condition + ";")
percentage = output[0][0] / item[1] * 100
print("{0} {1} {2} : {3}%\n".format(item[0], item[1], output[0][0],
percentage))
logname.write("{0};{1}\n".format(item[0], percentage))
logname.close()
def query_repeat_devices(period=15):
'''Count the number of devices (non-randomized) detected within last 15
minutes'''
cmd = '''
SELECT COUNT(1) repeat_device_count
FROM
(SELECT DISTINCT(mac_id)
FROM events
WHERE ts BETWEEN NOW() - INTERVAL %s MINUTE AND NOW()) t1
JOIN
(SELECT DISTINCT(mac_id)
FROM events
WHERE ts BETWEEN DATE(NOW()) - INTERVAL 31 DAY
AND DATE(NOW())) t2
ON t1.mac_id = t2.mac_id
;
'''
sql = SqlConnector()
sql.cursor.execute(cmd, (period,))
x = sql.cursor.fetchall()[0]['repeat_device_count']
sql.cursor.close()
return x
def extract_facebook_pixels(dbname):
logp1 = open(u'fb_pixel_pattern1.txt', 'w')
logp2 = open(u'fb_pixel_pattern2.txt', 'w')
logp3 = open(u'fb_pixel_pattern3.txt', 'w')
logp4 = open(u'fb_pixel_pattern4.txt', 'w')
logp5 = open(u'fb_pixel_pattern5.txt', 'w')
db = SqlConnector(dbname)
output = db.execute(
"SELECT images.url, pages.url, images.width, images.height \
FROM images INNER JOIN pages ON \
pages.id = images.id_pages INNER JOIN image_domains ON \
images.id_image_domains = image_domains.id WHERE width<=1 \
and height<=1 and image_domains.domain = 'facebook.com';")
fb_unique_pixels1 = []
fb_unique_pixels2 = []
fb_unique_pixels3 = []
fb_unique_pixels4 = []
fb_unique_pixels5 = []
fb_unique_pixels3 = []
count_pages_pattern1 = 0
count_pages_pattern2 = 0
count_pages_pattern3 = 0
count_pages_pattern4 = 0
count_pages_pattern5 = 0
for item in output:
if item[0].find("/tr/?") != -1:
count_pages_pattern1 += 1
fb_pixel = extract_pixel(item[0])
if fb_pixel != "null" and fb_pixel not in fb_unique_pixels1:
fb_unique_pixels1.append(fb_pixel)
logp1.write("On page {0} w={1} h={2}\n\t {3}\n\n".format(
item[1], item[2], item[3], item[0]))
elif item[0].find("/tr?") != -1:
count_pages_pattern2 += 1
fb_pixel = extract_pixel(item[0])
if fb_pixel != "null" and fb_pixel not in fb_unique_pixels2:
fb_unique_pixels2.append(fb_pixel)
logp2.write("On page {0} w={1} h={2}\n\t {3}\n\n".format(
item[1], item[2], item[3], item[0]))
elif item[0].find("brandlift.php?") != -1:
count_pages_pattern3 += 1
fb_pixel = extract_pixel(item[0])
if fb_pixel != "null" and fb_pixel not in fb_unique_pixels3:
fb_unique_pixels3.append(fb_pixel)
logp3.write("On page {0} w={1} h={2}\n\t {3}\n\n".format(
item[1], item[2], item[3], item[0]))
elif item[0].find("offsite_event.php?") != -1:
count_pages_pattern4 += 1
fb_pixel = extract_pixel(item[0])
if fb_pixel != "null" and fb_pixel not in fb_unique_pixels4:
fb_unique_pixels4.append(fb_pixel)
logp4.write("On page {0} w={1} h={2}\n\t {3}\n\n".format(
item[1], item[2], item[3], item[0]))
else:
count_pages_pattern5 += 1
fb_pixel = "null"
b = item[0].find("spacer.gif?")
if b != -1:
fb_pixel = item[0][b + 11:len(item[0])]
if fb_pixel != "null" and fb_pixel not in fb_unique_pixels5:
fb_unique_pixels5.append(fb_pixel)
logp5.write("On page {0} w={1} h={2}\n\t {3}\n\n".format(
item[1], item[2], item[3], item[0]))
p1 = len(fb_unique_pixels1)
p2 = len(fb_unique_pixels2)
p3 = len(fb_unique_pixels3)
p4 = len(fb_unique_pixels4)
p5 = len(fb_unique_pixels5)
print('Count of pages with fb pixels [pattern1]: {0}'.format(
count_pages_pattern1))
logp1.write('Count of pages with fb pixels [pattern1]: {0}\n'.format(
count_pages_pattern1))
print('Count of pages with fb pixels [pattern2]: {0}'.format(
count_pages_pattern2))
logp2.write('Count of pages with fb pixels [pattern2]: {0}\n'.format(
count_pages_pattern2))
print('Count of pages with fb pixels [pattern3]: {0}'.format(
count_pages_pattern3))
logp3.write('Count of pages with fb pixels [pattern3]: {0}\n'.format(
count_pages_pattern3))
print('Count of pages with fb pixels [pattern4]: {0}'.format(
count_pages_pattern4))
logp4.write('Count of pages with fb pixels [pattern4]: {0}\n'.format(
count_pages_pattern4))
print('Count of pages with fb pixels [pattern5]: {0}'.format(
count_pages_pattern5))
logp5.write('Count of pages with fb pixels [pattern5]: {0}\n'.format(
count_pages_pattern5))
print('Count of unique fb pixels [pattern1]: {0}'.format(p1))
logp1.write('Count of unique fb pixels [pattern1]: {0}\n'.format(p1))
print('Count of unique fb pixels [pattern2]: {0}'.format(p2))
logp2.write('Count of unique fb pixels [pattern2]: {0}\n'.format(p2))
print('Count of unique fb pixels [pattern3]: {0}'.format(p3))
logp3.write('Count of unique fb pixels [pattern3]: {0}\n'.format(p3))
print('Count of unique fb pixels [pattern4]: {0}'.format(p4))
logp4.write('Count of unique fb pixels [pattern4]: {0}\n'.format(p4))
print('Count of unique fb pixels [pattern5]: {0}'.format(p5))
logp5.write('Count of unique fb pixels [pattern5]: {0}\n'.format(p5))
print('Total count of uniqie fb pixels: {0}'.format(p1 + p2 + p3 + p4 +
p5))
print('Total count of pages with fb pixels: {0}'.format(
count_pages_pattern1 + count_pages_pattern2 + count_pages_pattern3 +
count_pages_pattern4 + count_pages_pattern5))
logp1.close()
logp2.close()
logp3.close()
logp4.close()
logp5.close()
from config import SQLConfig
from sqlalchemy import (Column, Integer, String, ForeignKey, Text, DateTime,
Time, Boolean)
from sqlalchemy.ext.declarative import declarative_base
from sql_connector import SqlConnector
Base = declarative_base()
#connector = SqlConnector('root', '10.132.0.3', 'clouddr', password='******')
connector = SqlConnector(SQLConfig.USER_ID,
SQLConfig.IP,
SQLConfig.DB,
password=SQLConfig.PASSWORD)
class Guests(Base):
__tablename__ = "Guests"
id = Column(String(50), primary_key=True)
first = Column(String(20), nullable=False)
last = Column(String(20), nullable=False)
def AllGuests():
session = connector.get_sql_session()
result = session.query(Guests).all()
session.close()
return result
def UpdateGuest(id, first, last):
session = connector.get_sql_session()