def get_by_hash(self, object_hash, mode=0):
# if mode == 2:
# malwares = Capture.query().filter_by(hash=object_hash).order_by(text('capture_id desc')).all()
# else:
# malwares = Capture.query.filter_by(hash=object_hash).order_by(text('capture_id desc')).all()
# print('~~~~~~ mode', mode)
if mode == 2:
cmd = db.select([
Capture.capture_id, Capture.file_name, Capture.file_size,
Capture.hash, Capture.date_received, Capture.time_received,
Capture.protocol, Capture.source_ip, Capture.destination_ip,
Capture.detected_by
]).where(Capture.hash == object_hash).order_by(
db.desc(Capture.capture_id))
print('cmd', cmd)
else:
cmd = db.select([
Capture.capture_id, Capture.file_name, Capture.file_size,
Capture.hash, Capture.date_received, Capture.time_received,
Capture.protocol, Capture.source_ip, Capture.destination_ip,
Capture.detected_by
]).where(Capture.hash == object_hash).order_by(
db.desc(Capture.capture_id))
engine = db.create_engine(Config.SQLALCHEMY_DATABASE_URI, {})
connection = engine.connect()
malwares = connection.execute(cmd).fetchall()
return malwares
def count_total(self):
query = db.func.count(Capture.capture_id)
engine = db.create_engine(Config.SQLALCHEMY_DATABASE_URI, {})
connection = engine.connect()
total = int(connection.execute(query).scalar())
# print('~~total', total)
return total
def stat_by_date(self, days, split):
today = datetime.datetime.now()
dist = datetime.timedelta(days=days)
d_start = today - dist
print('[/url][stat_by_date] days', days, 'split', split)
print('[/url][stat_by_date] d_start', d_start)
stat_by_date_tot_cmd = db.select([
db.func.count(db.distinct(Url.url)).label('total_url'),
db.func.count(Url.url_capture_id).label('total'),
db.func.FROM_UNIXTIME(db.func.FLOOR(db.func.UNIX_TIMESTAMP(
db.func.timestamp(Url.date_requested, Url.time_requested)
)/split)*split).label('time')
]).group_by(
'time'
).where(db.and_(
Url.url.notlike('%msftncsi%'),
Url.date_requested.isnot(None),
Url.date_requested >= d_start,
Url.date_requested <= today
)).order_by(db.asc('time'))
engine = db.create_engine(Config.SQLALCHEMY_DATABASE_URI, {})
connection = engine.connect()
stat_date_tot = connection.execute(stat_by_date_tot_cmd).fetchall()
stat_by_date_data_req = []
stat_by_date_data_url = []
stat_by_date_cat = []
for k, r in enumerate(stat_date_tot):
# print(r)
stat_by_date_data_url.append(r[0])
stat_by_date_cat.append(r[2].strftime('%d/%m, %H:%M'))
stat_by_date_data_req.append(r[1])
stat_by_date = {
'series': [{
'name': 'Total URLs',
# 'type': 'column',
'type': 'area',
'data': stat_by_date_data_url
}, {
'name': 'Total requests',
'type': 'line',
'data': stat_by_date_data_req
}],
'cat': stat_by_date_cat
}
# print('stat_by_date', stat_by_date)
return stat_by_date
def count_all(self, cmd):
engine = db.create_engine(Config.SQLALCHEMY_DATABASE_URI, {})
connection = engine.connect()
cmd = 'select count(capture_id) from ' + cmd.split('from')[1]
# print('[count_all] cmd', cmd)
total = connection.execute(cmd).scalar()
if total is None:
return 0
total = int(total)
# print('~~total', total)
return total
def get(self, cmd, page=0):
page_size = 30
start = page * page_size
end = (page + 1) * page_size
cmd = cmd + " limit " + str(start) + ", " + str(end)
print('** [get] cmd', cmd)
engine = db.create_engine(Config.SQLALCHEMY_DATABASE_URI, {})
connection = engine.connect()
notis = connection.execute(cmd).fetchall()
return notis
def stat(self):
top_url_cmd = db.select([db.func.count(Url.url_capture_id).label('total'), Url.url]).where(db.and_(Url.url.isnot(None), Url.url.notlike('%msftncsi%'))).group_by(Url.url).order_by(db.desc('total')).limit(5)
engine = db.create_engine(Config.SQLALCHEMY_DATABASE_URI, {})
connection = engine.connect()
top_url = connection.execute(top_url_cmd).fetchall()
top_url_data = []
for r in top_url:
top_url_data.append({'url': r[1], 'total': r[0]})
stat_data = {
'top_url': top_url_data
}
return stat_data
def check(self, urls, source_ips):
is_malicious_urls = []
if len(urls) > 0:
is_malicious_urls = urlclassifier.classifier(urls).tolist()
for i in range(len(urls)):
if is_malicious_urls[i] == 1:
data = {
'url': urls[i],
'is_malicious': is_malicious_urls[i],
'source_ip': source_ips[i]
}
self.create(data=data)
add_total_url_cmd = "insert into total_urls (date, time, total) values('{}', '{}', '{}')".format(str(datetime.datetime.now().strftime('%Y-%m-%d')), str(datetime.datetime.now().strftime('%H:%M:%S')), len(urls))
engine = db.create_engine(Config.SQLALCHEMY_DATABASE_URI, {})
connection = engine.connect()
connection.execute(add_total_url_cmd)
return urls, is_malicious_urls
def check():
print('[check] **** CALL check')
t_engine = db.create_engine(Config.SQLALCHEMY_DATABASE_URI, {})
t_connection = t_engine.connect()
controllerCapture = ControllerCapture()
controllerNoti = ControllerNoti()
cmd = "select * from capture where detected_by is null and file_path is not null and task_id is not null order by capture_id asc limit 0,{}".format(
cf.process_batch_size)
# if True:
while True:
# print('[check] ~~ cf.is_processing', cf.is_processing)
# if cf.__tasks_to_run_detector__.empty():
# print('[check] No task in [cf.__tasks_to_run_detector__] queue. Sleep 1s then check again')
# time.sleep(10)
# else:
# filepaths, task_ids, captures_unprocessed = cf.__tasks_to_run_detector__.get()
# Process by batch.
# Load a batch of {batch_size} files unprocessed in database
if cf.is_processing:
print(
'[check] Some task is processing. Sleep 10s then check again')
time.sleep(10)
else:
print('[check] Run process')
# load unprocessed from database
# captures_unprocessed_proxy = t_connection.execute(cmd).fetchall()
# # captures_unprocessed = Capture.query.filter(db.and_(Capture.detected_by == None, Capture.file_path != None, Capture.task_id != None)).order_by(Capture.capture_id.asc()).fetchall()
# print('[check] captures_unprocessed_proxy', captures_unprocessed)
captures_unprocessed = t_connection.execute(cmd).fetchall()
print('[check] captures_unprocessed', captures_unprocessed)
# t_connection.execute(capture.update().where(capture.capture_id == ).values(foo="bar"))
# if found unprocessed task
# if captures_unprocessed_proxy is not None and len(captures_unprocessed_proxy) > 0:
if captures_unprocessed is not None and len(
captures_unprocessed) > 0:
cf.is_processing = True # lock
filepaths = []
task_ids = []
# for capture_unprocessed_proxy in captures_unprocessed_proxy:
# capture_unprocessed = dict(capture_unprocessed_proxy.items())
print('[check] *** captures_unprocessed', captures_unprocessed)
for capture_unprocessed in captures_unprocessed:
print('[check] #', 'capture_unprocessed',
capture_unprocessed)
# filepaths, task_ids, task_data = data
filepaths.append(capture_unprocessed.file_path)
task_ids.append(capture_unprocessed.task_id)
print('[fcn_check] *** Working on ', task_ids, 'filepaths',
filepaths, 'captures_unprocessed', captures_unprocessed)
# print('[fcn_check] task_ids', task_ids, 'filepaths', filepaths)
# Run detector core
# to get report
# and cukoo/virustotal result
# task_ids, resp, scan_time = cf.detector.run(filepaths, task_ids)
resp, scan_time = cf.detector.run(filepaths, task_ids)
# start a thread for other detectors
# t1 = threading.Thread(target=detector.run_han, args=(task_ids))
# t1.start()
# print('[fcn_check] resp', resp)
# with concurrent.futures.ThreadPoolExecutor() as executor:
# future_han = executor.submit(detector.run_han, task_ids, resp)
# resp_all, scan_time = future_han.result()
resp_all, scan_time = cf.detector.run_han(task_ids, resp)
print('[check] *** HAN return ', resp_all, scan_time)
# future_ngram = executor.submit(detector.run_ngram, filepaths, task_ids, resp_all)
# resp_all, scan_time = future_ngram.result()
# print('** NGRAM return ', resp_all, scan_time)
links = []
captures_data_new = []
filenames = []
i = 0
for capture_unprocessed in captures_unprocessed:
tmp = {}
task_id = task_ids[i]
filepath = filepaths[i]
# filename = filenames[i]
filename = filepath.split('/')[-1]
res = resp_all[0][task_id]
engines_detected = resp_all[1][task_id]
detector_output = resp_all[2][task_id]
# print('res', res)
# if res['is_malware'] == 1:
# print('i=', i, tmp)
tmp['file_name'] = filename
tmp['file_size'] = os.path.getsize(filepath)
tmp['file_extension'] = filepath.split('.')[-1]
# tmp['file_path'] = filepath
tmp['report_path'] = res['report_path']
tmp['report_id'] = res['report_id']
tmp['hash'] = res['hash_value']
tmp['md5'] = res['md5']
tmp['sha1'] = res['sha1']
tmp['sha256'] = res['sha256']
tmp['sha512'] = res['sha512']
tmp['ssdeep'] = res['ssdeep']
# tmp['source_ip'] = request.remote_addr
tmp['detected_by'] = ','.join(engines_detected)
tmp['detector_output'] = json.dumps(detector_output)
tmp['scan_time'] = scan_time
if 'date_received' not in tmp:
tmp['date_received'] = time.strftime('%Y-%m-%d')
if 'time_received' not in tmp:
tmp['time_received'] = time.strftime('%H:%M:%S')
if 'destination_ip' not in tmp:
tmp['destination_ip'] = ''
filenames.append(filename)
captures_data_new.append(tmp)
links.append(str(capture_unprocessed.capture_id))
# update database
# controllerCapture._parse_malware(data=tmp, capture_id=capture_unprocessed.capture_id)
# db.session.commit()
controllerCapture.update(
object_id=capture_unprocessed.capture_id, data=tmp)
i += 1
# cf.__tasks_done__.put((captures_data_new, captures_unprocessed))
# cf.is_processing = False # done processing
# cf.__tasks_to_run_detector__.task_done()
cf.is_processing = False
# add notification
noti_data = {
'user_id':
2,
'message':
'Xử lý thành công các files {}. Xem chi tiết tại: <<{}>>'.
format(', '.join(filenames), '|'.join(links))
}
controllerNoti.create(data=noti_data)
print('[check] Process done. Sleep 10s')
time.sleep(10)
def stat(self):
# malwares_num = Capture.query.filter(Capture.detected_by != '').with_entities(Capture.hash).count() # + 1324#distinct().count()
malwares_num = Capture.query.filter(
db.or_(Capture.detected_by.like('%virustotal%'),
Capture.detected_by.like('%static%'),
Capture.detected_by.like('%HAN_sec%'))).with_entities(
Capture.hash).count()
# warnings_num = Capture.query.filter(db.and_(Capture.detected_by != '', Capture.detected_by.notlike('%virustotal%'), Capture.detected_by.notlike('%static%'), Capture.detected_by.notlike('%HAN_sec%'))).with_entities(Capture.hash).count() # + 1324#distinct().count()
# benigns_num = Capture.query.filter(Capture.detected_by == '').with_entities(Capture.hash).count() # + 1324#distinct().count()
warnings_num = Capture.query.filter(
Capture.detected_by.like('%cuckoo%')).with_entities(
Capture.hash).count()
benigns_num = Capture.query.filter(
db.and_(Capture.detected_by.notlike('%virustotal%'),
Capture.detected_by.notlike('%static%'),
Capture.detected_by.notlike('%HAN_sec%'),
Capture.detected_by.notlike('%cuckoo%'))).with_entities(
Capture.hash).count()
ip_malwares_num = Capture.query.filter(
Capture.detected_by != '').with_entities(
Capture.source_ip).distinct().count()
# captured protocol
captured_protocol_cmd = db.select([
Capture.protocol,
db.func.count(Capture.capture_id).label('count_protocol')
]).group_by(Capture.protocol)
print('captured_protocol_cmd', captured_protocol_cmd)
# attacked protocol
# top_ip_mal = Capture.query.filter(Capture.detected_by != '').with_entities(Capture.source_ip).distinct()
top_ip_mal_cmd = db.select([
db.func.sum(
db.case([(db.or_(Capture.detected_by.like('%virustotal%'),
Capture.detected_by.like('%static%'),
Capture.detected_by.like('%HAN_sec%')), 1)],
else_=0)).label('malwares_found'), Capture.source_ip
]).where(Capture.source_ip.isnot(None)).group_by(
Capture.source_ip).order_by(db.desc('malwares_found')).limit(5)
top_ip_send_cmd = db.select([
db.func.sum(Capture.file_size).label('bytes_sent'),
Capture.source_ip
]).group_by(Capture.source_ip).order_by(
db.desc('bytes_sent')).order_by(db.desc('bytes_sent')).limit(5)
top_ip_rev_mal_cmd = db.select([
db.func.sum(
db.case([(db.or_(
Capture.detected_by.like('%virustotal%'),
Capture.detected_by.like('%static%'),
Capture.detected_by.like('%HAN_sec%'),
), 1)],
else_=0)).label('malwares_found'),
Capture.destination_ip
]).group_by(Capture.destination_ip).where(
db.and_(Capture.destination_ip.isnot(None),
Capture.destination_ip != '',
Capture.source_ip.isnot(None))).order_by(
db.desc('malwares_found')).limit(5)
# print('~~~~ top_ip_mal_cmd', top_ip_mal_cmd)
# print('~~~~ top_ip_rev_mal_cmd', top_ip_rev_mal_cmd)
# stat_by_date_mal_cmd = db.select([db.func.count(Capture.capture_id).label('mal_num'), db.func.MAX(Capture.time_received).label('time')]).group_by(db.func.MINUTE(Capture.time_received)).where(db.and_(Capture.date_received.isnot(None), Capture.detected_by != '')).order_by(db.asc('time'))
# top_ip_mal = db.session.query(top_ip_mal_cmd)
engine = db.create_engine(Config.SQLALCHEMY_DATABASE_URI, {})
connection = engine.connect()
top_ip_mal = connection.execute(top_ip_mal_cmd).fetchall()
top_ip_send = connection.execute(top_ip_send_cmd).fetchall()
top_ip_rev_mal = connection.execute(top_ip_rev_mal_cmd).fetchall()
captured_protocol_tuple = connection.execute(
captured_protocol_cmd).fetchall()
captured_protocol = {'http': 0, 'ftp': 0, 'smb': 0}
for r in captured_protocol_tuple:
captured_protocol[r[0]] = r[1]
print('** captured_protocol', captured_protocol)
stat_data = self.gen_text_data(top_ip_mal, top_ip_send, top_ip_rev_mal)
# print('stat_data', stat_data)
stat_data['num'] = {
'malwares_num': malwares_num,
'warnings_num': warnings_num,
'benigns_num': benigns_num,
'ip_malwares_num': ip_malwares_num
}
stat_data['captured_protocol'] = captured_protocol
return stat_data
def stat_by_date(self, days, split):
today = datetime.datetime.now()
dist = datetime.timedelta(days=days)
d_start = today - dist
print('[/capture][stat_by_date] days', days, 'split', split)
print('[/capture][stat_by_date] d_start', d_start)
stat_by_date_tot_cmd = db.select([
# db.func.count(Capture.capture_id).label('total_files'),
# db.func.sum(db.case([(db.or_(Capture.detected_by == '', db.and_(Capture.detected_by.notlike('%virustotal%'), Capture.detected_by.notlike('%static%'), Capture.detected_by.notlike('%HAN_sec%'))), 1)], else_=0)).label('normals_found'),
# db.func.sum(db.case([(Capture.detected_by != '', 1)], else_=0)).label('malwares_found'),
# with warnings
# db.func.sum(db.case([(Capture.detected_by == '', 1)], else_=0)).label('normals_found'),
# db.func.sum(db.case([(db.or_(Capture.detected_by.like('%virustotal%'), Capture.detected_by.like('%static%'), Capture.detected_by.like('%HAN_sec%')), 1)], else_=0)).label('malwares_found'),
# db.func.sum(db.case([(db.and_(Capture.detected_by != '', Capture.detected_by.notlike('%virustotal%'), Capture.detected_by.notlike('%static%'), Capture.detected_by.notlike('%HAN_sec%')), 1)], else_=0)).label('warnings_found'),
db.func.sum(
db.case(
[(db.and_(Capture.detected_by.notlike('%virustotal%'),
Capture.detected_by.notlike('%static%'),
Capture.detected_by.notlike('%HAN_sec%'),
Capture.detected_by.notlike('%cuckoo%')), 1)],
else_=0)).label('normals_found'),
db.func.sum(
db.case([(db.or_(Capture.detected_by.like('%virustotal%'),
Capture.detected_by.like('%static%'),
Capture.detected_by.like('%HAN_sec%')), 1)],
else_=0)).label('malwares_found'),
db.func.sum(
db.case([(Capture.detected_by.like('%cuckoo%'), 1)],
else_=0)).label('warnings_found'),
# db.func.MAX(Capture.time_received).label('time')
db.func.FROM_UNIXTIME(
db.func.FLOOR(
db.func.UNIX_TIMESTAMP(
# Capture.time_received
db.func.timestamp(Capture.date_received,
Capture.time_received)) / split) *
split).label('time')
]).group_by(
# db.func.MINUTE(Capture.time_received)
'time').where(
db.and_(Capture.date_received.isnot(None),
Capture.date_received >= d_start,
Capture.date_received <= today)).order_by(
db.asc('time'))
engine = db.create_engine(Config.SQLALCHEMY_DATABASE_URI, {})
connection = engine.connect()
stat_date_tot = connection.execute(stat_by_date_tot_cmd).fetchall()
stat_by_date_data_normal = []
stat_by_date_data_mal = []
stat_by_date_data_warnings = []
stat_by_date_cat = []
# print('stat_date_tot', stat_date_tot)
# print('stat_date_mal', stat_date_mal)
for k, r in enumerate(stat_date_tot):
# stat_by_date.append({'date': r[1], 'total': r[0]})
# r_m = stat_date_mal[k]
# print(r[0], '~~~~~', r_m[0])
if r[0] > 0 or r[1] > 0:
stat_by_date_data_normal.append(r[0])
# print('r[1]', r[1])
stat_by_date_data_mal.append(r[1])
stat_by_date_data_warnings.append(r[2])
# stat_by_date_cat.append(r[2].strftime('%D, %H:%M'))
stat_by_date_cat.append(r[3].strftime('%d/%m, %H:%M'))
# value = datetime.timedelta(0, 64800)
# time_val = (datetime.datetime.min + r[2]).time()
# stat_by_date_cat.append(time_val)
# print('stat_by_date_cat', stat_by_date_cat)
stat_by_date = {
'series': [
{
# 'name': 'Total files',
# 'type': 'column',
'name': 'Benigns',
'data': stat_by_date_data_normal
},
{
'name': 'Malwares',
# 'type': 'line',
'data': stat_by_date_data_mal
},
{
'name': 'Criticals',
# 'type': 'line',
'data': stat_by_date_data_warnings
}
],
'cat':
stat_by_date_cat
}
return stat_by_date
def check():
print('[check] **** CALL check')
# cmd = "select * from capture where report_id is null and (detected_by is null or detected_by = '') and file_path is not null and task_id is not null order by capture_id asc limit 0,{}".format(cf.process_batch_size)
cmd = "select * from capture where report_id is null and detected_by is null and file_path is not null and task_id is not null order by capture_id asc limit 0,{}".format(
cf.process_batch_size)
while True:
time.sleep(240) # sleep 3m
# Process by batch.
# Load a batch of {batch_size} files unprocessed in database
if not cf.is_processing:
print('[check] *** Load some tasks to process',
datetime.datetime.now())
# load unprocessed from database
t_engine = db.create_engine(Config.SQLALCHEMY_DATABASE_URI, {})
t_connection = t_engine.connect()
captures_unprocessed = t_connection.execute(cmd).fetchall()
# print('[check] captures_unprocessed', captures_unprocessed)
# if found unprocessed task
# if captures_unprocessed_proxy is not None and len(captures_unprocessed_proxy) > 0:
if captures_unprocessed is not None and len(
captures_unprocessed) > 0:
cf.is_processing = True # lock
filepaths = []
task_ids = []
# print('[check] *** captures_unprocessed', captures_unprocessed)
for capture_unprocessed in captures_unprocessed:
# print('[check] #', 'capture_unprocessed', capture_unprocessed)
filepaths.append(capture_unprocessed.file_path)
task_ids.append(capture_unprocessed.task_id)
print('[check] *** Working on ', task_ids, 'filepaths',
filepaths)
# Run detector core
# to get report
# and cukoo/virustotal result
resp, scan_time = cf.detector.run(filepaths, task_ids)
# start a thread for other detectors
# t1 = threading.Thread(target=detector.run_han, args=(task_ids))
# t1.start()
# print('[fcn_check] resp', resp)
# with concurrent.futures.ThreadPoolExecutor() as executor:
# future_han = executor.submit(detector.run_han, task_ids, resp)
# resp_all, scan_time = future_han.result()
resp_all, scan_time = cf.detector.run_han(task_ids, resp)
print('[check] *** HAN return ', resp_all, scan_time)
links = []
captures_data_new = []
filenames = []
i = 0
for capture_unprocessed in captures_unprocessed:
tmp = {}
task_id = task_ids[i]
filepath = filepaths[i]
filename = filepath.split('/')[-1]
res = resp_all[0][task_id]
engines_detected = resp_all[1][task_id]
detector_output = resp_all[2][task_id]
update_el = []
update_el.append("{} = '{}'".format('file_name', filename))
update_el.append("{} = '{}'".format(
'file_extension',
filepath.split('.')[-1]))
if os.path.exists(filepath):
update_el.append("{} = '{}'".format(
'file_size', os.path.getsize(filepath)))
update_el.append("{} = '{}'".format(
'report_path', res['report_path']))
update_el.append("{} = '{}'".format(
'report_id', res['report_id']))
update_el.append("{} = '{}'".format(
'hash', res['hash_value']))
update_el.append("{} = '{}'".format('md5', res['md5']))
update_el.append("{} = '{}'".format('sha1', res['sha1']))
update_el.append("{} = '{}'".format(
'sha256', res['sha256']))
update_el.append("{} = '{}'".format(
'sha512', res['sha512']))
update_el.append("{} = '{}'".format(
'ssdeep', res['ssdeep']))
update_el.append("{} = '{}'".format(
'detected_by', ','.join(engines_detected)))
update_el.append("{} = '{}'".format(
'detector_output', json.dumps(detector_output)))
update_el.append("{} = '{}'".format(
'scan_time', scan_time))
update_str = ', '.join(update_el)
cmd_update_capture = 'update capture set {} where capture_id = {}'.format(
update_str, capture_unprocessed.capture_id)
# send socket
# cmd_GetIP = 'select source_ip from capture where capture_id = {} limit 0,1'.format(capture_unprocessed.capture_id)
# ip = t_connection.execute(cmd_GetIP).fetchall()
# sendSocket = "./socket " + ip[0].source_ip + " " + filename
# print('[check]', sendSocket)
# try:
# subprocess.check_output(sendSocket, shell=True)
# except:
# print("not send Socket")
# close socket
print('[check] *** cmd_update_capture', cmd_update_capture)
t_connection.execute(cmd_update_capture)
filenames.append(filename)
captures_data_new.append(tmp)
links.append(str(capture_unprocessed.capture_id))
i += 1
cf.is_processing = False
# add notification
msg = 'Xử lý thành công các files {}. Xem chi tiết tại: <<{}>>'.format(
', '.join(filenames), '|'.join(links))
cmd_add_noti = "insert into notification (user_id, message, date_created) values ({}, '{}', '{}')".format(
2, msg, datetime.datetime.now())
t_connection.execute(cmd_add_noti)
