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 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_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)基础数据
: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 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 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 = 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
# mal_x = np.arange(len(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
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)
import numpy as np
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])
