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)