def eventpage():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
#Get Thresholds
lmax = conn.cursor().execute("SELECT max from thresholds WHERE tname = 'low'").fetchone()
med = conn.cursor().execute("SELECT min,max from thresholds WHERE tname = 'med'").fetchone()
hmin = conn.cursor().execute("SELECT min from thresholds WHERE tname = 'high'").fetchone()
rows = conn.cursor().execute("select a.weight, s.modname, s.timeStamp, s.username, s.msg from spicymango s join alerts a on s.id=a.id").fetchall()
conn.close()
json_alerts = {}
json_alerts['aaData'] = []
for row in rows:
if row[0] <= lmax[0]:
priority = "Low"
tdclass = "low"
if med[0] <= row[0] <= med[1]:
priority = "Medium"
tdclass = "medium"
if row[0] >= hmin[0]:
priority = "High"
tdclass = "high"
total_weight = "<span class=\'ticket "+tdclass+"\'>"+priority+"</span>"
json_alerts['aaData'].append([total_weight, row[0], row[1], row[2], row[3], row[4]])
response.set_header('Content-type', 'application/json')
return json.dumps(json_alerts, indent=4)
def eventpage():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
#Get Thresholds
lmax = conn.cursor().execute(
"SELECT max from thresholds WHERE tname = 'low'").fetchone()
med = conn.cursor().execute(
"SELECT min,max from thresholds WHERE tname = 'med'").fetchone(
)
hmin = conn.cursor().execute(
"SELECT min from thresholds WHERE tname = 'high'").fetchone()
alows = conn.cursor().execute(
"select count(id) from alerts where weight <= ?",
(lmax[0], )).fetchone()
amediums = conn.cursor().execute(
"select count(id) from alerts where weight between ? and ?",
(med[0], med[1])).fetchone()
ahighs = conn.cursor().execute(
"select count(id) from alerts where weight >= ?",
(hmin[0], )).fetchone()
conn.close()
return template('alerts',
total_alert_highs=ahighs[0],
total_alert_mediums=amediums[0],
total_alert_lows=alows[0])
else:
redirect('/login')
def dash():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
if request.query.get('type') == "recent":
event_id = request.query.get('eid')
event = conn.cursor().execute(
"SELECT s.modname, s.timeStamp, a.weight, s.username, s.hostname, s.ircchan, s.msg FROM spicymango s JOIN alerts a ON s.id = a.id WHERE s.id = ?",
(event_id, )).fetchone()
conn.close()
return "<tr><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td></tr>".format(
event[0], event[1], event[2], event[3], event[4], event[5],
event[6])
if request.query.get('type') == "topuser":
user = request.query.get('username')
events = conn.cursor().execute(
"SELECT s.modname, s.timeStamp, a.weight, s.hostname, s.ircchan, s.msg FROM spicymango s JOIN alerts a ON s.id = a.id WHERE s.username = ? ORDER BY s.timeStamp DESC LIMIT 10",
(user, )).fetchall()
conn.close()
results = ""
for event in events:
results = results + "<tr><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td></tr>".format(
event[0], event[1], event[2], event[3], event[4],
event[5])
return results
def deletekey():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
rid = request.forms.get('id')
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
conn.cursor().execute('DELETE from keywords WHERE id = ?', (rid))
conn.commit()
conn.close()
return 'ok'
def deletekey():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
rid = request.forms.get('id')
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
conn.cursor().execute('DELETE from keywords WHERE id = ?', (rid))
conn.commit()
conn.close()
return 'ok'
def addkey():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
rkeyword = request.forms.get('keyword')
rweight = request.forms.get('weight')
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
conn.cursor().execute('INSERT INTO keywords VALUES(NULL,?,?,0)', (rkeyword, rweight))
rid = conn.cursor().execute('SELECT last_insert_rowid()').fetchone()
conn.commit()
conn.close()
return str(rid[0])
def addkey():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
rkeyword = request.forms.get('keyword')
rweight = request.forms.get('weight')
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
conn.cursor().execute('INSERT INTO keywords VALUES(NULL,?,?,0)',
(rkeyword, rweight))
rid = conn.cursor().execute(
'SELECT last_insert_rowid()').fetchone()
conn.commit()
conn.close()
return str(rid[0])
def eventpage():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
rows = conn.cursor().execute("select modname, timeStamp, username, msg from spicymango order by timeStamp DESC").fetchall()
conn.close()
json_events = {}
json_events['aaData'] = []
for row in rows:
json_events['aaData'].append([row[0], row[1], row[2], row[3]])
response.set_header('Content-type', 'application/json')
return json.dumps(json_events, indent=4)
def eventpage():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
#Get Thresholds
lmax = conn.cursor().execute("SELECT max from thresholds WHERE tname = 'low'").fetchone()
med = conn.cursor().execute("SELECT min,max from thresholds WHERE tname = 'med'").fetchone()
hmin = conn.cursor().execute("SELECT min from thresholds WHERE tname = 'high'").fetchone()
alows = conn.cursor().execute("select count(id) from alerts where weight <= ?", (lmax[0],)).fetchone()
amediums = conn.cursor().execute("select count(id) from alerts where weight between ? and ?", (med[0],med[1])).fetchone()
ahighs = conn.cursor().execute("select count(id) from alerts where weight >= ?", (hmin[0],)).fetchone()
conn.close()
return template('alerts', total_alert_highs=ahighs[0], total_alert_mediums=amediums[0], total_alert_lows=alows[0])
else:
redirect('/login')
def tsave():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
lmax = (request.query.get('lmax'),)
mmin = request.query.get('mmin')
mmax = request.query.get('mmax')
hmin = (request.query.get('hmin'),)
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
conn.cursor().execute('UPDATE thresholds SET max = ? WHERE tname = \'low\'', lmax)
conn.cursor().execute('UPDATE thresholds SET min = ?, max = ? WHERE tname = \'med\'', (mmin, mmax))
conn.cursor().execute('UPDATE thresholds SET min = ? WHERE tname = \'high\'', hmin)
conn.commit()
conn.close()
return '1'
else:
return 'Error'
def keywordspage():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
lmax = conn.cursor().execute("select max from thresholds where tname = \'low\'").fetchone()
hmin = conn.cursor().execute("select min from thresholds where tname = \'high\'").fetchone()
sliderp = "[%s,%s]," % (lmax[0],hmin[0])
rows = conn.cursor().execute("select id, keyword, weight from keywords order by weight DESC").fetchall()
conn.close()
trRows = ""
for row in rows:
trRows = trRows + '<tr id=\'%s\'><td>%s</td><td>%s</td></tr>' % (row[0],row[1],row[2])
return template('set-keywords', dataRows=trRows, slider=sliderp)
else:
redirect('/login')
def dash():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
if request.query.get('type') == "recent":
event_id = request.query.get('eid')
event = conn.cursor().execute("SELECT s.modname, s.timeStamp, a.weight, s.username, s.hostname, s.ircchan, s.msg FROM spicymango s JOIN alerts a ON s.id = a.id WHERE s.id = ?",(event_id,)).fetchone()
conn.close()
return "<tr><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td></tr>".format(event[0],event[1],event[2],event[3],event[4],event[5],event[6])
if request.query.get('type') == "topuser":
user = request.query.get('username')
events = conn.cursor().execute("SELECT s.modname, s.timeStamp, a.weight, s.hostname, s.ircchan, s.msg FROM spicymango s JOIN alerts a ON s.id = a.id WHERE s.username = ? ORDER BY s.timeStamp DESC LIMIT 10",(user,)).fetchall()
conn.close()
results = ""
for event in events:
results = results + "<tr><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td><td>{!s}</td></tr>".format(event[0],event[1],event[2],event[3],event[4],event[5])
return results
def eventpage():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
rows = conn.cursor().execute(
"select modname, timeStamp, username, msg from spicymango order by timeStamp DESC"
).fetchall()
conn.close()
json_events = {}
json_events['aaData'] = []
for row in rows:
json_events['aaData'].append([row[0], row[1], row[2], row[3]])
response.set_header('Content-type', 'application/json')
return json.dumps(json_events, indent=4)
def pass_set():
import hashlib
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
cpass = hashlib.md5(request.forms.get('currentpass')).hexdigest()
npass = hashlib.md5(request.forms.get('newpass')).hexdigest()
database = "users.db"
conn = sqlite3.connect(database)
if conn.cursor().execute("SELECT COUNT(username) from users WHERE password = ?", (cpass,)).fetchone()[0] == 1:
conn.cursor().execute("UPDATE users SET password = ? WHERE password = ?", (npass,cpass))
conn.commit()
conn.close()
response.delete_cookie("loggedin")
return "1"
else:
conn.close()
return "2"
def updatekey():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
rid = request.forms.get('id')
rvalue = request.forms.get('value')
rcolumn = request.forms.get('columnName')
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
if rcolumn == "keyword":
conn.cursor().execute('UPDATE keywords SET keyword = ?, count = 0 WHERE id = ?', (rvalue, rid))
conn.commit()
conn.close()
elif rcolumn == "weight":
conn.cursor().execute('UPDATE keywords SET weight = ? WHERE id = ?', (rvalue, rid))
conn.commit()
conn.close()
else:
return "Error"
return rvalue
def tsave():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
lmax = (request.query.get('lmax'), )
mmin = request.query.get('mmin')
mmax = request.query.get('mmax')
hmin = (request.query.get('hmin'), )
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
conn.cursor().execute(
'UPDATE thresholds SET max = ? WHERE tname = \'low\'', lmax)
conn.cursor().execute(
'UPDATE thresholds SET min = ?, max = ? WHERE tname = \'med\'',
(mmin, mmax))
conn.cursor().execute(
'UPDATE thresholds SET min = ? WHERE tname = \'high\'', hmin)
conn.commit()
conn.close()
return '1'
else:
return 'Error'
def eventpage():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
#Get Thresholds
lmax = conn.cursor().execute(
"SELECT max from thresholds WHERE tname = 'low'").fetchone()
med = conn.cursor().execute(
"SELECT min,max from thresholds WHERE tname = 'med'").fetchone(
)
hmin = conn.cursor().execute(
"SELECT min from thresholds WHERE tname = 'high'").fetchone()
rows = conn.cursor().execute(
"select a.weight, s.modname, s.timeStamp, s.username, s.msg from spicymango s join alerts a on s.id=a.id"
).fetchall()
conn.close()
json_alerts = {}
json_alerts['aaData'] = []
for row in rows:
if row[0] <= lmax[0]:
priority = "Low"
tdclass = "low"
if med[0] <= row[0] <= med[1]:
priority = "Medium"
tdclass = "medium"
if row[0] >= hmin[0]:
priority = "High"
tdclass = "high"
total_weight = "<span class=\'ticket " + tdclass + "\'>" + priority + "</span>"
json_alerts['aaData'].append(
[total_weight, row[0], row[1], row[2], row[3], row[4]])
response.set_header('Content-type', 'application/json')
return json.dumps(json_alerts, indent=4)
def updatekey():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
rid = request.forms.get('id')
rvalue = request.forms.get('value')
rcolumn = request.forms.get('columnName')
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
if rcolumn == "keyword":
conn.cursor().execute(
'UPDATE keywords SET keyword = ?, count = 0 WHERE id = ?',
(rvalue, rid))
conn.commit()
conn.close()
elif rcolumn == "weight":
conn.cursor().execute(
'UPDATE keywords SET weight = ? WHERE id = ?',
(rvalue, rid))
conn.commit()
conn.close()
else:
return "Error"
return rvalue
def pass_set():
import hashlib
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
cpass = hashlib.md5(request.forms.get('currentpass')).hexdigest()
npass = hashlib.md5(request.forms.get('newpass')).hexdigest()
database = "users.db"
conn = sqlite3.connect(database)
if conn.cursor().execute(
"SELECT COUNT(username) from users WHERE password = ?",
(cpass, )).fetchone()[0] == 1:
conn.cursor().execute(
"UPDATE users SET password = ? WHERE password = ?",
(npass, cpass))
conn.commit()
conn.close()
response.delete_cookie("loggedin")
return "1"
else:
conn.close()
return "2"
def keywordspage():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
lmax = conn.cursor().execute(
"select max from thresholds where tname = \'low\'").fetchone()
hmin = conn.cursor().execute(
"select min from thresholds where tname = \'high\'").fetchone(
)
sliderp = "[%s,%s]," % (lmax[0], hmin[0])
rows = conn.cursor().execute(
"select id, keyword, weight from keywords order by weight DESC"
).fetchall()
conn.close()
trRows = ""
for row in rows:
trRows = trRows + '<tr id=\'%s\'><td>%s</td><td>%s</td></tr>' % (
row[0], row[1], row[2])
return template('set-keywords', dataRows=trRows, slider=sliderp)
else:
redirect('/login')
def set_pass():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
return template('set-password')
else:
redirect('/login')
def mainview():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
#Get Thresholds
lmax = conn.cursor().execute(
"SELECT max from thresholds WHERE tname = 'low'").fetchone()
med = conn.cursor().execute(
"SELECT min,max from thresholds WHERE tname = 'med'").fetchone(
)
hmin = conn.cursor().execute(
"SELECT min from thresholds WHERE tname = 'high'").fetchone()
events = conn.cursor().execute(
'select count(id) from spicymango').fetchone()
high_events = conn.cursor().execute(
'select count(id) from alerts where weight >= ?',
(hmin[0], )).fetchone()
medium_events = conn.cursor().execute(
'select count(id) from alerts where weight between ? and ?',
(med[0], med[1])).fetchone()
low_events = conn.cursor().execute(
'select count(id) from alerts where weight <= ?',
(lmax[0], )).fetchone()
chart_highs = conn.cursor().execute(
"select count(s.id), strftime('%H', s.timeStamp) from spicymango s join alerts a on s.id = a.id where a.weight >= ? and s.timeStamp >= datetime('now', 'localtime', '-12 hour') group by strftime('%H', s.timeStamp)",
(hmin[0], )).fetchall()
chart_mediums = conn.cursor().execute(
"select count(s.id), strftime('%H', s.timeStamp) from spicymango s join alerts a on s.id = a.id where a.weight between ? and ? and s.timeStamp >= datetime('now', 'localtime', '-12 hour') group by strftime('%H', s.timeStamp)",
(med[0], med[1])).fetchall()
chart_lows = conn.cursor().execute(
"select count(s.id), strftime('%H', s.timeStamp) from spicymango s join alerts a on s.id = a.id where a.weight <= ? and s.timeStamp >= datetime('now', 'localtime', '-12 hour') group by strftime('%H', s.timeStamp)",
(lmax[0], )).fetchall()
recent_alls = conn.cursor().execute(
"select a.weight, s.msg, s.timeStamp, s.id from spicymango s join alerts a on s.id=a.id order by timeStamp DESC limit 7"
).fetchall()
recent_highs = conn.cursor().execute(
"select s.msg, s.timeStamp, s.id from spicymango s join alerts a on s.id=a.id where a.weight >= ? order by timeStamp DESC limit 7",
(hmin[0], )).fetchall()
recent_mediums = conn.cursor().execute(
"select s.msg, s.timeStamp, s.id from spicymango s join alerts a on s.id=a.id where a.weight between ? and ? order by timeStamp DESC limit 7",
(med[0], med[1])).fetchall()
recent_lows = conn.cursor().execute(
"select s.msg, s.timeStamp, s.id from spicymango s join alerts a on s.id=a.id where a.weight <= ? order by timeStamp DESC limit 7",
(lmax[0], )).fetchall()
top_users = conn.cursor().execute(
"select s.username, count(s.username) from spicymango s join alerts a on s.id = a.id group by username order by count(username) DESC LIMIT 5"
).fetchall()
top_alerts = conn.cursor().execute(
"select s.msg, a.weight, s.id from spicymango s join alerts a on s.id = a.id order by a.weight DESC LIMIT 5"
).fetchall()
top_keywords = conn.cursor().execute(
"select keyword, count from keywords order by count DESC LIMIT 5"
).fetchall()
conn.close()
i = 0
last_12 = []
while i < 13:
d = datetime.datetime.now() - datetime.timedelta(hours=i)
hr = d.strftime("%H")
last_12.append(hr)
i += 1
last_12.reverse()
c_hours = ""
c_highs = ""
c_mediums = ""
c_lows = ""
for hour in last_12:
high_count = "0"
medium_count = "0"
low_count = "0"
for chigh in chart_highs:
if hour == chigh[1]:
high_count = str(chigh[0])
for cmedium in chart_mediums:
if hour == cmedium[1]:
medium_count = str(cmedium[0])
for clow in chart_lows:
if hour == clow[1]:
low_count = str(clow[0])
c_hours = c_hours + "<th>" + hour + ":00</th>"
c_highs = c_highs + "<td>" + high_count + "</td>"
c_mediums = c_mediums + "<td>" + medium_count + "</td>"
c_lows = c_lows + "<td>" + low_count + "</td>"
r_all = ""
r_high = ""
r_medium = ""
r_low = ""
for rall in recent_alls:
if rall[0] >= hmin[0]:
priority = "high"
priority_label = "High"
if med[0] <= rall[0] <= med[1]:
priority = "medium"
priority_label = "Medium"
if rall[0] <= lmax[0]:
priority = "low"
priority_label = "Low"
r_all = r_all + "<tr><td><span class='ticket {!s}'>{!s}</span></td><td class='full'><a id='{!s}' href='javascript:void(0)'>{!s}</a></td><td class='who'>{!s}</td></tr>".format(
priority, priority_label, rall[3], rall[1], rall[2])
for rhigh in recent_highs:
r_high = r_high + "<tr><td><span class='ticket high'>High</span></td><td class='full'><a id='{!s}' href='javascript:void(0)'>{!s}</a></td><td class='who'>{!s}</td></tr>".format(
rhigh[2], rhigh[0], rhigh[1])
for rmedium in recent_mediums:
r_medium = r_medium + "<tr><td><span class='ticket medium'>Medium</span></td><td class='full'><a id='{!s}' href='javascript:void(0)'>{!s}</a></td><td class='who'>{!s}</td></tr>".format(
rmedium[2], rmedium[0], rmedium[1])
for rlow in recent_lows:
r_low = r_low + "<tr><td><span class='ticket low'>Low</span></td><td class='full'><a id='{!s}' href='javascript:void(0)'>{!s}</a></td><td class='who'>{!s}</td></tr>".format(
rlow[2], rlow[0], rlow[1])
return template('webview',
eventcount=events[0],
highs=high_events[0],
mediums=medium_events[0],
lows=low_events[0],
chart_hours=c_hours,
chart_highs=c_highs,
chart_mediums=c_mediums,
chart_lows=c_lows,
recent_all=r_all,
recent_highs=r_high,
recent_mediums=r_medium,
recent_lows=r_low,
topusers=top_users,
topalerts=top_alerts,
topkeywords=top_keywords)
else:
redirect('/login')
def set_pass():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
return template('set-password')
else:
redirect('/login')
def eventpage():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
return template('events')
else:
redirect('/login')
def eventpage():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
return template('events')
else:
redirect('/login')
def mainview():
username = request.get_cookie("loggedin", secret='sm2345-45634')
if username:
database = check_config("OUTPUT_SQLITE3_DB_PATH=")
conn = sqlite3.connect(database)
#Get Thresholds
lmax = conn.cursor().execute("SELECT max from thresholds WHERE tname = 'low'").fetchone()
med = conn.cursor().execute("SELECT min,max from thresholds WHERE tname = 'med'").fetchone()
hmin = conn.cursor().execute("SELECT min from thresholds WHERE tname = 'high'").fetchone()
events = conn.cursor().execute('select count(id) from spicymango').fetchone()
high_events = conn.cursor().execute('select count(id) from alerts where weight >= ?', (hmin[0],)).fetchone()
medium_events = conn.cursor().execute('select count(id) from alerts where weight between ? and ?', (med[0],med[1])).fetchone()
low_events = conn.cursor().execute('select count(id) from alerts where weight <= ?', (lmax[0],)).fetchone()
chart_highs = conn.cursor().execute("select count(s.id), strftime('%H', s.timeStamp) from spicymango s join alerts a on s.id = a.id where a.weight >= ? and s.timeStamp >= datetime('now', 'localtime', '-12 hour') group by strftime('%H', s.timeStamp)", (hmin[0],)).fetchall()
chart_mediums = conn.cursor().execute("select count(s.id), strftime('%H', s.timeStamp) from spicymango s join alerts a on s.id = a.id where a.weight between ? and ? and s.timeStamp >= datetime('now', 'localtime', '-12 hour') group by strftime('%H', s.timeStamp)", (med[0],med[1])).fetchall()
chart_lows = conn.cursor().execute("select count(s.id), strftime('%H', s.timeStamp) from spicymango s join alerts a on s.id = a.id where a.weight <= ? and s.timeStamp >= datetime('now', 'localtime', '-12 hour') group by strftime('%H', s.timeStamp)", (lmax[0],)).fetchall()
recent_alls = conn.cursor().execute("select a.weight, s.msg, s.timeStamp, s.id from spicymango s join alerts a on s.id=a.id order by timeStamp DESC limit 7").fetchall()
recent_highs = conn.cursor().execute("select s.msg, s.timeStamp, s.id from spicymango s join alerts a on s.id=a.id where a.weight >= ? order by timeStamp DESC limit 7", (hmin[0],)).fetchall()
recent_mediums = conn.cursor().execute("select s.msg, s.timeStamp, s.id from spicymango s join alerts a on s.id=a.id where a.weight between ? and ? order by timeStamp DESC limit 7", (med[0],med[1])).fetchall()
recent_lows = conn.cursor().execute("select s.msg, s.timeStamp, s.id from spicymango s join alerts a on s.id=a.id where a.weight <= ? order by timeStamp DESC limit 7", (lmax[0],)).fetchall()
top_users = conn.cursor().execute("select s.username, count(s.username) from spicymango s join alerts a on s.id = a.id group by username order by count(username) DESC LIMIT 5").fetchall()
top_alerts = conn.cursor().execute("select s.msg, a.weight, s.id from spicymango s join alerts a on s.id = a.id order by a.weight DESC LIMIT 5").fetchall()
top_keywords = conn.cursor().execute("select keyword, count from keywords order by count DESC LIMIT 5").fetchall()
conn.close()
i = 0
last_12 = []
while i < 13:
d = datetime.datetime.now() - datetime.timedelta(hours=i)
hr = d.strftime("%H")
last_12.append(hr)
i += 1
last_12.reverse()
c_hours = ""
c_highs = ""
c_mediums = ""
c_lows = ""
for hour in last_12:
high_count = "0"
medium_count = "0"
low_count = "0"
for chigh in chart_highs:
if hour == chigh[1]:
high_count = str(chigh[0])
for cmedium in chart_mediums:
if hour == cmedium[1]:
medium_count = str(cmedium[0])
for clow in chart_lows:
if hour == clow[1]:
low_count = str(clow[0])
c_hours = c_hours + "<th>"+hour+":00</th>"
c_highs = c_highs + "<td>"+high_count+"</td>"
c_mediums = c_mediums + "<td>"+medium_count+"</td>"
c_lows = c_lows + "<td>"+low_count+"</td>"
r_all = ""
r_high = ""
r_medium = ""
r_low = ""
for rall in recent_alls:
if rall[0] >= hmin[0]:
priority = "high"
priority_label = "High"
if med[0] <= rall[0] <= med[1]:
priority = "medium"
priority_label = "Medium"
if rall[0] <= lmax[0]:
priority = "low"
priority_label = "Low"
r_all = r_all + "<tr><td><span class='ticket {!s}'>{!s}</span></td><td class='full'><a id='{!s}' href='javascript:void(0)'>{!s}</a></td><td class='who'>{!s}</td></tr>".format(priority, priority_label, rall[3], rall[1], rall[2])
for rhigh in recent_highs:
r_high = r_high + "<tr><td><span class='ticket high'>High</span></td><td class='full'><a id='{!s}' href='javascript:void(0)'>{!s}</a></td><td class='who'>{!s}</td></tr>".format(rhigh[2],rhigh[0], rhigh[1])
for rmedium in recent_mediums:
r_medium = r_medium + "<tr><td><span class='ticket medium'>Medium</span></td><td class='full'><a id='{!s}' href='javascript:void(0)'>{!s}</a></td><td class='who'>{!s}</td></tr>".format(rmedium[2], rmedium[0], rmedium[1])
for rlow in recent_lows:
r_low = r_low + "<tr><td><span class='ticket low'>Low</span></td><td class='full'><a id='{!s}' href='javascript:void(0)'>{!s}</a></td><td class='who'>{!s}</td></tr>".format(rlow[2], rlow[0], rlow[1])
return template('webview', eventcount=events[0], highs=high_events[0], mediums=medium_events[0], lows=low_events[0], chart_hours=c_hours, chart_highs=c_highs, chart_mediums=c_mediums, chart_lows=c_lows, recent_all=r_all, recent_highs=r_high, recent_mediums=r_medium, recent_lows=r_low, topusers=top_users, topalerts=top_alerts, topkeywords=top_keywords)
else:
redirect('/login')
