def get_average_response_time_counts(request):
out = { 'results':{},'error':{},'session':{}, 'success': False }
if is_auth(request,True):
start_date = request.GET.get('start_date')
end_date = request.GET.get('end_date')
if check_report_date(start_date) and check_report_date(end_date):
start_parts = start_date.split("/")
end_parts = end_date.split("/")
start_date = start_parts[2] + "-" + start_parts[0] + "-" + start_parts[1]
end_date = end_parts[2] + "-" + end_parts[0] + "-" + end_parts[1]
key = start_parts[0] + start_parts[1] + start_parts[2] + end_parts[0] + end_parts[1] + end_parts[2]
con = connect_to_mysql("128.164.80.81","dragonslayer","slayer","dragonslayer")
cursor = con.cursor ()
stmt = "select AVG(TIME_TO_SEC(TIMEDIFF(tdstamp, discovered))) * 0.000277777778 as delta from gwcases where date(tdstamp) BETWEEN '" + start_date + "' AND '" + end_date + "' AND report_category > 1 AND TIME_TO_SEC(TIMEDIFF(tdstamp,discovered)) > 0"
cursor.execute(stmt)
row = cursor.fetchone()
avg_resp = round(row[0],2)
tmp = { 'name':"Average Response",
'value':str(avg_resp),
'key':key
}
out['results'] = tmp
out['success'] = True
mimetype = 'application/javascript'
return HttpResponse(json.dumps(out),mimetype)
else:
out['error'] = "Attempting to travel time with the dates you supplied. Check back last week."
return render_to_response("error.html",out,context_instance=RequestContext(request))
else:
time.sleep(30)
def get_compromise_counts(request):
out = { 'results':{},'error':{},'session':{}, 'success': False }
if is_auth(request,True):
start_date = request.GET.get('start_date')
end_date = request.GET.get('end_date')
if check_report_date(start_date) and check_report_date(end_date):
start_parts = start_date.split("/")
end_parts = end_date.split("/")
start_date = start_parts[2] + "-" + start_parts[0] + "-" + start_parts[1]
end_date = end_parts[2] + "-" + end_parts[0] + "-" + end_parts[1]
key = start_parts[0] + start_parts[1] + start_parts[2] + end_parts[0] + end_parts[1] + end_parts[2]
# con = connect_to_mysql(settings.MYSQL_HOST,settings.MYSQL_USER,settings.MYSQL_PASS,settings.MYSQL_DB)
con = connect_to_mysql("128.164.80.81","dragonslayer","slayer","dragonslayer")
cursor = con.cursor ()
stmt = "SELECT COUNT(*) as count FROM gwcases WHERE DATE(tdstamp) BETWEEN '" + start_date + "' AND '" + end_date + "' AND report_category > 1"
cursor.execute(stmt)
row = cursor.fetchone()
total_count = row[0]
stmt = "SELECT COUNT(*) as count FROM gwcases WHERE DATE(tdstamp) BETWEEN '" + start_date + "' AND '" + end_date + "' AND report_category = 20"
cursor.execute(stmt)
row = cursor.fetchone()
student_count = row[0]
stmt = "SELECT COUNT(*) as count FROM gwcases WHERE DATE(tdstamp) BETWEEN '" + start_date + "' AND '" + end_date + "' AND report_category >= 100"
cursor.execute(stmt)
row = cursor.fetchone()
normal_count = row[0]
stmt = "SELECT COUNT(*) as count FROM gwcases WHERE DATE(tdstamp) BETWEEN '" + start_date + "' AND '" + end_date + "' AND report_category > 200"
cursor.execute(stmt)
row = cursor.fetchone()
vip_count = row[0]
tmp = { 'total_count':total_count,
'student_count':student_count,
'staff_faculty_count':normal_count,
'patchlink_count':0,
'non_patchlink_count':0,
'email_count':0,
'key':key
}
out['results'] = tmp
out['success'] = True
mimetype = 'application/javascript'
return HttpResponse(json.dumps(out),mimetype)
else:
out['error'] = "Attempting to travel time with the dates you supplied. Check back last week."
return render_to_response("error.html",out,context_instance=RequestContext(request))
else:
time.sleep(30)
def get_normal_graph_counts(request):
out = { 'results':{},'error':{},'session':{}, 'success': False }
if is_auth(request,True):
objs = []
start_date = request.GET.get('start_date')
end_date = request.GET.get('end_date')
if check_report_date(start_date) and check_report_date(end_date):
start_parts = start_date.split("/")
end_parts = end_date.split("/")
start_date = start_parts[2] + "-" + start_parts[0] + "-" + start_parts[1]
end_date = end_parts[2] + "-" + end_parts[0] + "-" + end_parts[1]
key = start_parts[0] + start_parts[1] + start_parts[2] + end_parts[0] + end_parts[1] + end_parts[2]
con = connect_to_mysql("128.164.80.81","dragonslayer","slayer","dragonslayer")
cursor = con.cursor ()
stmt = "SELECT COUNT(*) as count FROM gwcases WHERE DATE(tdstamp) BETWEEN '" + start_date + "' AND '" + end_date + "' AND report_category = 20"
cursor.execute(stmt)
row = cursor.fetchone()
student_count = row[0]
stmt = "SELECT COUNT(*) as count FROM gwcases WHERE DATE(tdstamp) BETWEEN '" + start_date + "' AND '" + end_date + "' AND report_category >= 100"
cursor.execute(stmt)
row = cursor.fetchone()
normal_count = row[0]
obj = { 'name':'Faculty/Staff','value':normal_count,'key':key }
objs.append(obj)
obj = { 'name':'Student','value':student_count,'key':key }
objs.append(obj)
out['results'] = objs
out['success'] = True
mimetype = 'application/javascript'
return HttpResponse(json.dumps(out),mimetype)
else:
out['error'] = "Attempting to travel time with the dates you supplied. Check back last week."
return render_to_response("error.html",out,context_instance=RequestContext(request))
else:
time.sleep(30)
def get_compromise_details(request):
out = { 'results':{},'error':{},'session':{}, 'success': False }
if is_auth(request,True):
objs = []
start_date = request.GET.get('start_date')
end_date = request.GET.get('end_date')
if check_report_date(start_date) and check_report_date(end_date):
start_parts = start_date.split("/")
end_parts = end_date.split("/")
start_date = start_parts[2] + "-" + start_parts[0] + "-" + start_parts[1]
end_date = end_parts[2] + "-" + end_parts[0] + "-" + end_parts[1]
key = start_parts[0] + start_parts[1] + start_parts[2] + end_parts[0] + end_parts[1] + end_parts[2]
con = connect_to_mysql("128.164.80.81","dragonslayer","slayer","dragonslayer")
cursor = con.cursor ()
stmt = "SELECT INET_NTOA(victim) as victim, network, discovered, notes FROM gwcases WHERE (report_category >= 100) AND DATE(tdstamp) BETWEEN '" + start_date + "' AND '" + end_date + "' GROUP BY victim ORDER BY tdstamp, victim"
cursor.execute(stmt)
result_set = cursor.fetchall ()
for row in result_set:
obj = {
'ip_address':str(row[0]),
'school_department':str(row[1]),
'time_of_compromise':str(row[2]),
'compromise_notes':str(row[3]),
'original_notes':str(row[3]),
'key':key
}
objs.append(obj)
out['results'] = objs
out['success'] = True
mimetype = 'application/javascript'
return HttpResponse(json.dumps(out),mimetype)
else:
out['error'] = "Attempting to travel time with the dates you supplied. Check back last week."
return render_to_response("error.html",out,context_instance=RequestContext(request))
else:
time.sleep(30)
def get_compromise_types(request):
out = { 'results':{},'error':{},'session':{}, 'success': False }
if is_auth(request,True):
objs = []
start_date = request.GET.get('start_date')
end_date = request.GET.get('end_date')
if check_report_date(start_date) and check_report_date(end_date):
start_parts = start_date.split("/")
end_parts = end_date.split("/")
start_date = start_parts[2] + "-" + start_parts[0] + "-" + start_parts[1]
end_date = end_parts[2] + "-" + end_parts[0] + "-" + end_parts[1]
key = start_parts[0] + start_parts[1] + start_parts[2] + end_parts[0] + end_parts[1] + end_parts[2]
con = connect_to_mysql("128.164.80.81","dragonslayer","slayer","dragonslayer")
cursor = con.cursor ()
stmt = "select primary_detection, count(*) as count from gwcases WHERE DATE(tdstamp) BETWEEN '" + start_date + "' AND '" + end_date + "' AND report_category > 0 and report_category != 42 and primary_detection != '' group by primary_detection"
cursor.execute(stmt)
result_set = cursor.fetchall ()
for row in result_set:
obj = {
'name':str(row[0]),
'value':int(row[1]),
'key':key
}
objs.append(obj)
out['results'] = objs
out['success'] = True
mimetype = 'application/javascript'
return HttpResponse(json.dumps(out),mimetype)
else:
out['error'] = "Attempting to travel time with the dates you supplied. Check back last week."
return render_to_response("error.html",out,context_instance=RequestContext(request))
else:
time.sleep(30)
def get_historical_compromises(request):
out = { 'results':{},'error':{},'session':{}, 'success': False }
if is_auth(request,True):
objs = []
start_date = request.GET.get('start_date')
end_date = request.GET.get('end_date')
if check_report_date(start_date) and check_report_date(end_date):
start_parts = start_date.split("/")
end_parts = end_date.split("/")
start_date = start_parts[2] + "-" + start_parts[0] + "-" + start_parts[1] #year-month-day
end_date = end_parts[2] + "-" + end_parts[0] + "-" + end_parts[1]
key = start_parts[0] + start_parts[1] + start_parts[2] + end_parts[0] + end_parts[1] + end_parts[2]
import datetime
from datetime import timedelta
s = datetime.date(int(start_parts[2]),int(start_parts[0].lstrip('0')),int(start_parts[1].lstrip('0')))
e = datetime.date(int(end_parts[2]),int(end_parts[0].lstrip('0')),int(end_parts[1].lstrip('0')))
for n in range((e - s).days +1):
cur_d = s + timedelta(n)
current_date = cur_d.strftime("%Y-%m-%d")
con = connect_to_mysql("128.164.80.81","dragonslayer","slayer","dragonslayer")
cursor = con.cursor ()
stmt = "SELECT COUNT(id) as c, date(tdstamp) as d from gwcases where DATE(tdstamp) = '" + current_date + "' AND (report_category > 42 OR report_category = 20) AND (report_category != 205 AND report_category != 25) group by day(tdstamp) ORDER BY id, day(tdstamp)"
cursor.execute(stmt)
row = cursor.fetchone()
if row != None:
current_count = row[0]
else:
current_count = 0
pre_d = s + timedelta(n) - timedelta(days=365)
previous_date = pre_d.strftime("%Y-%m-%d")
stmt = "SELECT COUNT(id) as c, date(tdstamp) as d from gwcases where DATE(tdstamp) = '" + previous_date + "' AND (report_category > 42 OR report_category = 20) AND (report_category != 205 AND report_category != 25) group by day(tdstamp) ORDER BY id, day(tdstamp)"
cursor.execute(stmt)
row = cursor.fetchone()
if row != None:
previous_count = row[0]
else:
previous_count = 0
obj = {
'current_year':str(cur_d.strftime("%Y")),
'previous_year':str(pre_d.strftime("%Y")),
'current_count':int(current_count),
'previous_count':int(previous_count),
'date':str(cur_d.strftime("%m/%d")),
'key':key
}
objs.append(obj)
out['results'] = objs
out['success'] = True
mimetype = 'application/javascript'
return HttpResponse(json.dumps(out),mimetype)
else:
out['error'] = "Attempting to travel time with the dates you supplied. Check back last week."
return render_to_response("error.html",out,context_instance=RequestContext(request))
else:
time.sleep(30)
