In [ ]:
import struct, socket
import numpy as np
import csv, json
import os
import urllib2
import datetime
import operator
import itertools
try:
import ipywidgets as widgets # For jupyter/ipython >= 1.4
except ImportError:
from IPython.html import widgets
from IPython.display import display, HTML, clear_output, Javascript
with open('/etc/spot.conf') as conf:
for line in conf.readlines():
if "DBNAME=" in line: DBNAME = line.split("=")[1].strip('\n').replace("'","");
elif "IMPALA_DEM=" in line: IMPALA_DEM = line.split("=")[1].strip('\n').replace("'","");
path = os.getcwd().split("/")
t_date = path[len(path)-1]
dpath = '/'.join(['data' if var == 'ipynb' else var for var in path]) + '/'
t_date = path[len(path)-1]
sconnect = dpath + 'dns_scores.csv'
threat_f = dpath + "threats.csv"
anchor = ''
anchor_type = ''
top_results = 20
details_limit = 1000
In [ ]:
# Widget styles and initialization
topBox = widgets.Box()
bottomBox = widgets.Box()
mainBoxes_css = (
(None, 'width', '90%'),
(None, 'margin', '0 auto'),
)
topBox._css = mainBoxes_css
bottomBox._css = mainBoxes_css
threatBox = widgets.HBox(width='100%', height='auto')
threat_title = widgets.HTML(height='25px', width='100%')
threat_list_container = widgets.Box(width='80%', height='100%')
threat_button_container = widgets.Box(width='20%', height='100%')
susp_select = widgets.Select(height='100%', width='99%')
search_btn = widgets.Button(description='Search',height='100%', width='65px')
search_btn.button_style = 'primary'
susp_select._css = (
(None, 'height', '90%'),
(None, 'width', '95%'),
('select', 'overflow-x', 'auto'),
('select', 'margin', 0)
)
resultSummaryBox = widgets.Box()
result_title = widgets.HTML(width='100%')
result_summary_box = widgets.HBox(width='100%')
result_summary_container = widgets.Box(width='80%')
result_button_container = widgets.Box(width='20%')
result_summary_box.children = [result_title, result_summary_container, result_button_container]
resultTableBox = widgets.Box()
result_html_title = widgets.HTML(height='25px', width='100%')
result_html_box = widgets.Box() #this one has the scroll
result_html = widgets.HTML(width='100%')
result_box_css = (
(None, 'overflow', 'hidden'),
(None, 'width', '100%'),
)
resultSummaryBox._css = result_box_css
resultTableBox._css = result_box_css
result_html_box._css = (
(None, 'overflow','auto'),
(None, 'max-height', '300px'),
)
threat_button_container._css = (
(None, 'padding-top', '30px'),
)
topBox.children = [threatBox]
bottomBox.children = [resultSummaryBox,resultTableBox]
threat_list_container.children = [threat_title,susp_select]
threat_button_container.children = [search_btn]
threatBox.children = [threat_list_container, threat_button_container]
Interface
In [ ]:
yy = t_date[0:4]
mm = t_date[4:6]
dd = t_date[6:8]
def fill_list(list_control,source):
susp_select.options = list_control
susp_select.selected_label = list_control[0][0]
def data_loader():
ips_query = {}
ip_sev={}
dns_sev={}
c_ips=[]
c_dns=[]
if os.path.isfile(threat_f) and not file_is_empty(threat_f):
with open(threat_f, 'r') as th:
t_read = csv.reader(th, delimiter='|')
t_read.next()
for row in t_read:
if row[0] != '' : c_ips.append(row[0])
if row[1] != '' : c_dns.append(row[1])
with open(sconnect, 'r') as f:
reader = csv.DictReader(f, delimiter=',')
for row in reader:
if row['ip_dst'] not in ips_query and row['ip_dst'] not in c_ips and row['ip_sev'] == '1':
ips_query[row['ip_dst']]='i'
if row['dns_qry_name'] not in ips_query and row['dns_qry_name'] not in c_dns and row['dns_sev'] == '1':
ips_query[row['dns_qry_name']]='q'
if row['ip_dst'] not in ip_sev:
ip_sev[row['ip_dst']] = row['score']
if row['dns_qry_name'] not in dns_sev:
dns_sev[row['dns_qry_name']] =row['score']
threat_title.value ="<h4>Suspicious DNS</h4>"
if len(ips_query) == 0:
display(Javascript("$('.widget-area > .widget-subarea > *').remove();"))
display(widgets.HTML(value="There are not high risk results.", width='90%'),)
else:
sorted_dict = sorted(ips_query.items(), key=operator.itemgetter(0))
fill_list(sorted_dict,susp_select)
def start_investigation():
display(Javascript("$('.widget-area > .widget-subarea > *').remove();"))
data_loader()
if susp_select.options:
display_controls()
def display_controls():
susp_select
display(topBox)
def search_ip(b):
global anchor
global anchor_type
anchor = ''
anchor_type = ''
anchor = susp_select.selected_label
anchor_type = susp_select.value
removeWidget(2)
removeWidget(1)
clear_output()
global ir_f
ir_f = dpath + 'threat-dendro-' + anchor + ".csv"
table = "<table><th>IP</th><th>QUERY</th><th>TOTAL</th>"
if not os.path.isfile(ir_f) or (os.path.isfile(ir_f) and file_is_empty(ir_f)):
if anchor_type == 'i':
imp_query = "\" SELECT COUNT(dns_qry_name) as total, dns_qry_name, ip_dst, 0 as sev FROM {0}.dns \
WHERE y={1} AND m={2} AND d={3} AND ip_dst='{4}' GROUP BY dns_qry_name, ip_dst \
ORDER BY total DESC LIMIT {5}\""
elif anchor_type == 'q':
imp_query = "\" SELECT COUNT(ip_dst) as total, dns_qry_name, ip_dst, 0 as sev FROM {0}.dns \
WHERE y={1} AND m={2} AND d={3} AND dns_qry_name='{4}'\
GROUP BY ip_dst, dns_qry_name ORDER BY total DESC LIMIT {5}\""
imp_query=imp_query.format(DBNAME, yy, mm, dd, anchor, details_limit)
!impala-shell -i $IMPALA_DEM --quiet -q "INVALIDATE METADATA"
!impala-shell -i $IMPALA_DEM --quiet --print_header -B --output_delimiter=',' -q $imp_query -o $ir_f
clear_output()
# total, dns_qry_name, ip_dst, sev
with open(ir_f, 'r') as f:
try:
reader = itertools.islice(csv.reader(f, delimiter=','), top_results)
if reader!= '':
reader.next()
for row in reader:
table += "<tr><td class='spot-text-wrapper' data-toggle='tooltip'>"+row[2]+"</td>\
<td class='spot-text-wrapper' data-toggle='tooltip'>"+row[1]+"</td>\
<td align='center'>"+str(row[0])+"</td></tr>"
table += "</table>"
result_html_title.value='<h4>Displaying top {0} search results</h4>'.format(top_results)
except:
table = "<table></table>"
result_html_title.value='<h4>No results were found.</h4>'
result_html.value=table
result_html_box.children = [result_html]
display_threat_box(anchor)
resultTableBox.children = [result_html_title, result_html_box]
display(bottomBox)
search_btn.on_click(search_ip)
def display_threat_box(ip):
result_title.value="<h4 class='spot-text-wrapper spot-text-xlg' data-toggle='tooltip'>Threat summary for " + anchor +"</h4>"
tc_txt_title = widgets.Text(value='', placeholder='Threat Title', width='100%')
tc_txa_summary = widgets.Textarea(value='', height=100, width='95%')
tc_btn_save = widgets.Button(description='Save', width='65px', layout='width:100%')
tc_btn_save.button_style = 'primary'
tc_txt_title._css = (
(None, 'width', '95%'),
)
result_summary_container.children = [tc_txt_title, tc_txa_summary]
result_button_container.children=[tc_btn_save]
result_summary_box.children = [result_summary_container, result_button_container]
resultSummaryBox.children = [result_title,result_summary_box]
def save_threat_summary(b):
global anchor
anchor_ip =''
anchor_dns =''
if anchor != '':
if anchor_type == 'i':
anchor_ip = anchor
elif anchor_type == 'q':
anchor_dns = anchor
if not os.path.exists(threat_f):
with open(threat_f, 'w') as comment:
comment.write('ip_dst|dns_qry_name|title|summary\n')
with open(threat_f, 'a') as comment:
comment.write(anchor_ip + '|' + anchor_dns + '|' + tc_txt_title.value + '|' +
tc_txa_summary.value.replace('\n', '\\n') + '\n')
display(Javascript("$(\"option[data-value='" + anchor +"']\").remove();"))
display(Javascript("$('.widget-area > .widget-subarea > .widget-box:gt(0)').remove();"))
response = "Summary successfully saved"
else:
response = "No data selected"
susp_select.selected_label = susp_select.options[0][0]
display(widgets.Box((widgets.HTML(value=response, width='100%'),)))
data_loader()
tc_btn_save.on_click(save_threat_summary)
def file_is_empty(path):
return os.stat(path).st_size==0
def removeWidget(index):
js_command = "$('.widget-area > .widget-subarea > .widget-box:eq({0})').remove();".format(index)
display(Javascript(js_command))
In [ ]:
start_investigation()