def fetch_domains(): """ To fetch urls from the database, and return urls """ tld ={} db1 = MySQL(DBCONFIG1) db2 = MySQL(DBCONFIG2) sql = 'SELECT SUBSTRING_INDEX(tld,".",-1) as a,count(*) FROM domain_features GROUP BY a' db1.query(sql) tld_count = db1.fetch_all_rows() for i in tld_count: print i if len(i[0])==0: continue sql = 'SELECT type FROM tld_details WHERE tld="%s"' % ('.'+i[0]) db2.query(sql) urls2 = db2.fetch_all_rows() try: if urls2[0][0] in tld.keys(): tld[urls2[0][0]] += i[1] else: tld[urls2[0][0]] = i[1] except: continue print tld db1.close() db2.close()
def fetch_domains(): """ To fetch urls from the database, and return urls """ tld = {} db1 = MySQL(DBCONFIG1) db2 = MySQL(DBCONFIG2) sql = 'SELECT SUBSTRING_INDEX(tld,".",-1) as a,count(*) FROM domain_features GROUP BY a' db1.query(sql) tld_count = db1.fetch_all_rows() for i in tld_count: print i if len(i[0]) == 0: continue sql = 'SELECT type FROM tld_details WHERE tld="%s"' % ('.' + i[0]) db2.query(sql) urls2 = db2.fetch_all_rows() try: if urls2[0][0] in tld.keys(): tld[urls2[0][0]] += i[1] else: tld[urls2[0][0]] = i[1] except: continue print tld db1.close() db2.close()
def update_table(tb_name): """ 更新表 :param tb_name: :return: """ sql = 'SELECT flag,sum(whois_sum) FROM domain_whois_%s GROUP BY flag' % tb_name.lower() flag_undetected = flag_no_svr = flag_no_connect = 0 flag_reg_info = flag_reg_date = flag_part_info = 0 db = MySQL(DESTINATION_CONFIG) db.query(sql) results = db.fetch_all_rows() for item in results: flag = item[0] whois_sum = item[1] if flag == '-6': flag_undetected += whois_sum if flag == '-5': flag_no_svr += whois_sum elif flag == '-1' or flag == '-2' or flag == '-3' or flag == '-4': flag_no_connect += whois_sum elif flag == '120' or flag == '121' or flag == '122': flag_reg_info += whois_sum elif flag == '110' or flag == '102' or flag == '112': flag_reg_date += whois_sum elif flag == '100' or flag == '101' or flag == '111': flag_part_info += whois_sum sql = 'INSERT INTO table_overall_history (table_name,flag_undetected,\ flag_no_svr,flag_no_connect,flag_reg_info,flag_reg_date,flag_part_info) \ VALUES ("%s","%s","%s","%s","%s","%s","%s")' % ('domain_whois_'+tb_name,flag_undetected,flag_no_svr,flag_no_connect,flag_reg_info,flag_reg_date,flag_part_info) db.insert(sql) db.close()
def fetch_mal_domains(): """获取待查询的域名列表""" db = MySQL(SOURCE_CONFIG) sql = 'SELECT domain,visit_times FROM domain_records LIMIT 0,71622' db.query(sql) query_domains = db.fetch_all_rows() # 得到总共的数量 db.close() return query_domains
def fetch_domains(): """ To fetch urls from the database, and return urls """ db = MySQL() sql = 'SELECT domain FROM domain_white_list' db.query(sql) urls = db.fetch_all_rows() return tuple_to_list(urls)
def obtain_monitoring_host_process(): """获取待监测的进程信息""" db = MySQL(SOURCE_CONFIG) # 获取超过间隔时间的进程 sql = fetch_sql db.query(sql) host_process_result = db.fetch_all_rows() host_process = group_host_process(host_process_result) db.close() return host_process
def get_data_from_source(tb_name): """ :param name:string 数据库名称 :return: results 数据库whois数量分布 """ source_db = MySQL(SOURCE_CONFIG) source_db.query('select tld,flag,count(*) as whois_sum from domain_whois_%s group by tld,flag' % (tb_name)) results = source_db.fetch_all_rows() source_db.close() return results
def get_resource_data(tb_name): """ 获得基础数据 :param tb_name:string 表名 :return: 返回基础数据 """ db = MySQL(DESTINATION_CONFIG) db.query('SELECT tld, SUM(whois_sum) AS count FROM domain_whois_%s WHERE flag <> "-6" GROUP BY tld' % tb_name) results = db.fetch_all_rows() return results
def fetch_urls(): """ 获取要提取词汇特征的网址 """ db = MySQL() sql = 'SELECT url FROM url_features' db.query(sql) urls = db.fetch_all_rows() db.close() return tuple_to_list(urls)
def fetch_source_data(tb_name): """获取源数据 :param tb_name: string 表名 :return: results 数据 """ db = MySQL(DESTINATION_CONFIG) db.query('SELECT tld,flag,whois_sum FROM domain_whois_%s WHERE flag <> "-6" GROUP BY tld,flag' % tb_name) results = db.fetch_all_rows() db.close() return results
def fetch_resource_data(tb_name): """获得源数据 :param tb_name: string 表名 :return: results: 查询结果 """ db = MySQL(DESTINATION_CONFIG) db.query('SELECT tld, SUM(whois_sum) FROM domain_whois_%s GROUP BY tld' % tb_name) results = db.fetch_all_rows() db.close() return results
def fetch_data(): """ 从数据库中获取域名(domain)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT domain FROM domain_features limit 1000' db.query(sql) domains = db.fetch_all_rows() db.close() return domains
def fetch_data(): """ 从数据库中获取数据基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT url_length,path_tokens,path_brand,domain_tokens,malicious,domain_characters,path_characters FROM url_features_copy_copy' db.query(sql) urls = db.fetch_all_rows() db.close() return urls
def fetch_mal_domains(): """ 获取待查询的域名列表 """ db = MySQL(SOURCE_CONFIG) sql = 'SELECT domain, malicious_type FROM domain_index LIMIT 143244,71622' db.query(sql) query_domains = db.fetch_all_rows() # 得到总共的数量 db.close() return query_domains
def fetch_data(): """ 从数据库中获取域名长度(domain_length)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT tld FROM domain_features WHERE tld_token_count = "2"' db.query(sql) tlds = db.fetch_all_rows() db.close() return tlds
def fetch_data(): """ 从数据库中获取域名长度(domain_length)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT domain_characters FROM domain_features' db.query(sql) tlds = db.fetch_all_rows() db.close() return tlds
def fetch_data(): """ 从数据库中获取域名(domain)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT domain FROM domain_features' db.query(sql) domains = db.fetch_all_rows() db.close() return domains
def fetch_data(): """ 从数据库中获取域名长度(domain_length)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT domain_length FROM domain_features' db.query(sql) domains_length = db.fetch_all_rows() db.close() return domains_length
def fetch_data(): """ 从数据库中获取域名长度(domain_length)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT domain_characters,domain_digit FROM domain_features' db.query(sql) tlds = db.fetch_all_rows() db.close() return tlds
def get_resource_data(tb_name): """ 获得基础数据 :param tb_name:string 表名 :return: 返回基础数据 """ db = MySQL(DESTINATION_CONFIG) db.query( 'SELECT tld, SUM(whois_sum) AS count FROM domain_whois_%s WHERE flag <> "-6" GROUP BY tld' % tb_name) results = db.fetch_all_rows() return results
def fetch_data(): """ 从数据库中获取域名长度(domain_length)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT tld FROM domain_features' sql = 'SELECT SUBSTRING_INDEX(tld,".",-1) as a FROM domain_features' db.query(sql) domains_length = db.fetch_all_rows() db.close() return domains_length
def fetch_resource_data(): """ 获得待查询whois信息的域名,包括域名名称、更新时间、到期时间和详细信息,顶级域名 注意: domain_whois表中的域名是由domain_index中根据触发器更新的 """ db = MySQL(SOURCE_CONFIG) sql = 'SELECT domain,update_date,expiration_date,LENGTH (details),tld,flag FROM domain_whois WHERE flag = 1 LIMIT 0,80000' db.query(sql) query_domains = db.fetch_all_rows() # 得到总共的数量 db.close() return query_domains
def get_source_data(tb_name): """ 得到基础数据 :param tb_name:string 表名 :return: """ db = MySQL(SOURCE_CONFIG) sql = 'SELECT top_whois_server as top_svr, sec_whois_server AS sec_svr, count(*) \ FROM domain_whois_%s WHERE sec_whois_server <> "" GROUP BY top_svr, sec_svr' % tb_name db.query(sql) results = db.fetch_all_rows() db.close() return results
def process_id_to_user(process_id): """通过process_id获取用户的邮箱地址和主机地址""" sql = "SELECT email,host_ip,pid,code_route,process_name,warning_times FROM `process_info`,user_account,host_info WHERE process_id = '%s' AND user_account.user_id = process_info.user_id AND host_info.host_id = process_info.host_id" db = MySQL(SOURCE_CONFIG) db.query(sql % process_id) email_host = db.fetch_all_rows() email = email_host[0][0] host = base64.decodestring(email_host[0][1]) pid = email_host[0][2] code_route = email_host[0][3] process_name = email_host[0][4] warning_times = email_host[0][5] db.close() return email, host,pid,code_route,process_name,warning_times
def delete_empty_rc(domain): """ 删除空白域名DNS记录,但弱最后一条为空白,则不删除 方法:1)获取最新记录的last_updated时间 2)删除除最新last_updated所有域名的时间 """ db = MySQL(SOURCE_CONFIG) tb_name = domain2tb(domain) # 获取最近一条记录的last_updated时间 max_time_sql = 'select max(last_updated) from ' + tb_name + ' WHERE domain= "%s" ' db.query(max_time_sql % domain) max_time = db.fetch_all_rows()[0][0] # 得到最大值 # 删除某次探测为空的数据,但不删除最后一条记录 delete_sql = 'DELETE FROM ' + tb_name + ' WHERE (last_updated = insert_time) AND ips = "" AND domain="%s" AND last_updated != "%s"' db.update(delete_sql % (domain, str(max_time))) db.close()
def fetch_domains(): """ To fetch urls from the database, and return urls """ db1 = MySQL1() sql = 'SELECT url FROM benign_urls' db1.query(sql) urls1 = db1.fetch_all_rows() db2 = MySQL() sql = 'SELECT domain FROM domain_white_list' db2.query(sql) urls2 = db2.fetch_all_rows() db1.close() db2.close() urls1 = tuple_to_list(urls1) urls2 = tuple_to_list(urls2) urls = urls1 + urls2 print len(urls) urls = list(set(urls)) print len(urls) return urls
def fetch_domains(): """ To fetch urls from the database, and return urls """ db1 = MySQL1() sql = 'SELECT url FROM benign_urls' db1.query(sql) urls1 = db1.fetch_all_rows() db2 = MySQL() sql = 'SELECT domain FROM domain_white_list' db2.query(sql) urls2 = db2.fetch_all_rows() db1.close() db2.close() urls1 = tuple_to_list(urls1) urls2 = tuple_to_list(urls2) urls = urls1+urls2 print len(urls) urls = list(set(urls)) print len(urls) return urls
def merge_same_rc(domain): """ 最简单的合并相同的记录 todo: 1. 以某时间窗口来进行合并,去掉因为探测导致的问题 """ domain_rc = [] rc_last_updated = [] db = MySQL(SOURCE_CONFIG) tb_name = domain2tb(domain) sql = 'select ips, cnames, ns,last_updated from ' + tb_name + ' WHERE domain="%s"' db.query(sql % domain) rc = db.fetch_all_rows() # 域名记录预处理 for i in rc: domain_rc.append([i[0], i[1], i[2]]) rc_last_updated.append(str(i[3])) # 遍历修改记录 for i in range(0, len(domain_rc) - 1): if sorted(domain_rc[i][0]) == sorted(domain_rc[i+1][0]) and sorted(domain_rc[i][1])==sorted(domain_rc[i+1][1]) \ and sorted(domain_rc[i][2])==sorted(domain_rc[i+1][2]): # 前后两次的记录是否一致 ## 若相邻一致的情况 # 先删除后者记录 delete_sql = 'delete from ' + tb_name + ' WHERE domain="%s" and last_updated="%s"' db.update(delete_sql % (domain, rc_last_updated[i + 1])) # 再将前者记录的last_updated时间修改为后者记录的时间 update_sql = 'update ' + tb_name + ' set last_updated="%s" WHERE domain="%s" AND last_updated="%s"' db.update(update_sql % (rc_last_updated[i + 1], domain, rc_last_updated[i])) else: pass db.close()
def update_table(tb_name): """ 更新表 :param tb_name: :return: """ sql = 'SELECT flag,sum(whois_sum) FROM domain_whois_%s GROUP BY flag' % tb_name.lower( ) flag_undetected = flag_no_svr = flag_no_connect = 0 flag_reg_info = flag_reg_date = flag_part_info = 0 db = MySQL(DESTINATION_CONFIG) db.query(sql) results = db.fetch_all_rows() for item in results: flag = item[0] whois_sum = item[1] if flag == '-6': flag_undetected += whois_sum if flag == '-5': flag_no_svr += whois_sum elif flag == '-1' or flag == '-2' or flag == '-3' or flag == '-4': flag_no_connect += whois_sum elif flag == '120' or flag == '121' or flag == '122': flag_reg_info += whois_sum elif flag == '110' or flag == '102' or flag == '112': flag_reg_date += whois_sum elif flag == '100' or flag == '101' or flag == '111': flag_part_info += whois_sum sql = 'INSERT INTO table_overall_history (table_name,flag_undetected,\ flag_no_svr,flag_no_connect,flag_reg_info,flag_reg_date,flag_part_info) \ VALUES ("%s","%s","%s","%s","%s","%s","%s")' % ( 'domain_whois_' + tb_name, flag_undetected, flag_no_svr, flag_no_connect, flag_reg_info, flag_reg_date, flag_part_info) db.insert(sql) db.close()
# fig.add_subplot(121) # plt.plot(beg_x,beg_y) # plt.xlabel(u'网址个数') # plt.ylabel(u"域名长度") # fig.add_subplot(122) # plt.plot(mal_x,mal_y,color='r') # plt.xlabel(u'网址个数') # plt.ylabel(u"域名长度") # plt.show() db = MySQL() mal_sql = 'select domain_tokens,domain_characters from url_features WHERE malicious="1"' db.query(mal_sql) mal = db.fetch_all_rows() mal_y = [] for j,i in mal: # print j,i mal_y.append(int(list(eval(i))[1]*list(eval(j))[1]/100.0)) beg_sql = 'select domain_tokens,domain_characters from url_features WHERE malicious="0"' db.query(beg_sql) beg = db.fetch_all_rows() beg_y = [] for j,i in beg: beg_y.append(int(list(eval(i))[1]*list(eval(j))[1]/100.0)) # print mal_y #
# beg_x = np.arange(len(beg_y)) # fig = plt.figure() # fig.add_subplot(121) # plt.plot(beg_x,beg_y) # plt.xlabel(u'网址个数') # plt.ylabel(u"域名长度") # fig.add_subplot(122) # plt.plot(mal_x,mal_y,color='r') # plt.xlabel(u'网址个数') # plt.ylabel(u"域名长度") # plt.show() db = MySQL() mal_sql = 'select domain_tokens,domain_characters from url_features WHERE malicious="1"' db.query(mal_sql) mal = db.fetch_all_rows() mal_y = [] for j, i in mal: # print j,i mal_y.append(int(list(eval(i))[1] * list(eval(j))[1] / 100.0)) beg_sql = 'select domain_tokens,domain_characters from url_features WHERE malicious="0"' db.query(beg_sql) beg = db.fetch_all_rows() beg_y = [] for j, i in beg: beg_y.append(int(list(eval(i))[1] * list(eval(j))[1] / 100.0)) # print mal_y #
'port': 3306, 'user':'******', 'passwd':'platform', 'db':'DomainWhois', 'charset':'utf8'} db = MySQL(DBCONFIG) sql = 'select port_available from svr_ip WHERE level="2"' db.query(sql) test = db.fetch_all_rows() result = [] for i in test: result.append(len(i[0])) # print result result = np.array(result) # print np.unique(result) # print len(result[result==1]) x = []
DBCONFIG = { 'host': '172.26.253.3', 'port': 3306, 'user': '******', 'passwd': 'platform', 'db': 'DomainWhois', 'charset': 'utf8' } db = MySQL(DBCONFIG) sql = 'select port_available from svr_ip WHERE level="2"' db.query(sql) test = db.fetch_all_rows() result = [] for i in test: result.append(len(i[0])) # print result result = np.array(result) # print np.unique(result) # print len(result[result==1]) x = [] y = []