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 master_control():
"""主线程控制"""
try:
db = MySQL(SOURCE_CONFIG)
except:
print "连接数据库失败"
queue.task_done()
return
while queue.qsize(): # 重要，以前为1，导致线程不结束
check_domain, visit_times = queue.get()
check_domain = str(check_domain.strip())
print '线程数量：', threading.activeCount(), ',任务数量：', queue.qsize()
print "域名：", check_domain, datetime.now().strftime("%Y-%m-%d %H:%M:%S")
tb_name = domain2tb(check_domain)
if not tb_name: # 是否转换成功
print "域名名称异常，无法转换为表名称：", check_domain
queue.task_done()
continue
rc_ttl = manage_rc_ttl(check_domain)
rc_ttl = rc2str(rc_ttl) # 转换为字符串
if visit_times == 0: # 第一次探测域名直接插入到数据库中
insert_rc_db(db, tb_name, check_domain, rc_ttl)
else:
update_data(db, tb_name, check_domain, rc_ttl)
queue.task_done()
# time.sleep(1) # 去掉偶尔会出现错误
db.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 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 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 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 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():
"""
从数据库中获取数据基础数据
: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_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 update_day():
"""更新表whois_sum_by_day，即统计数据库中最新的域名whois总量
"""
print str(datetime.now()), '开始更新当天数据库中最新的WHOIS总量(whois_sum_by_day)'
db = MySQL(DESTINATION_CONFIG)
sql = 'INSERT INTO whois_sum_by_day(sum) SELECT MAX(tld_sum) FROM whois_sum \
WHERE to_days(insert_time) = to_days(now())'
db.insert(sql)
db.close()
print str(datetime.now()), '结束更新当天数据库中最新的WHOIS总量(whois_sum_by_day)'
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)基础数据
: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)基础数据
: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():
"""
从数据库中获取域名长度(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 domain_characters,domain_digit 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 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 tld FROM domain_features WHERE tld_token_count = "2"'
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 update_db():
"""更新数据库"""
tld_num = sum_all_domains()
db = MySQL(DESTINATION_CONFIG)
db.truncate('TRUNCATE TABLE domain_summary')
update_sql = 'INSERT INTO domain_update(tld_name, domain_num) VALUES("%s", "%s")'
summary_sql = 'INSERT INTO domain_summary(tld_name, domain_num) VALUES("%s", "%s")'
for tld,num in tld_num:
db.insert_no_commit(update_sql % (tld,num))
db.insert_no_commit(summary_sql % (tld,num))
db.commit()
db.close()
def update_day():
"""更新表whois_sum_by_day，即统计数据库中最新的域名whois总量
"""
print str(datetime.now()), '开始更新当天数据库中最新的WHOIS总量(whois_sum_by_day)'
db = MySQL(DESTINATION_CONFIG)
sql = 'INSERT INTO whois_sum_by_day(sum) SELECT MAX(tld_sum) FROM whois_sum \
WHERE to_days(insert_time) = to_days(now())'
db.insert(sql)
db.close()
print str(datetime.now()), '结束更新当天数据库中最新的WHOIS总量(whois_sum_by_day)'
def test(results):
db = MySQL()
for url_feature in results:
print url_feature
sql = 'UPDATE url_features SET url_length = "%s", domain="%s",domain_tokens="%s",domain_characters="%s",path="%s", ' \
'path_tokens="%s",path_characters="%s",path_brand="%s" WHERE url="%s"' % \
(url_feature[1],url_feature[2],url_feature[3],url_feature[4],url_feature[5],url_feature[6],
url_feature[7],url_feature[8],url_feature[0])
db.no_update(sql)
db.commit()
db.close()
def update_db():
"""
更新数据库
"""
svr_num = sum_all_list()
db = MySQL(DESTINATION_CONFIG)
db.truncate('TRUNCATE TABLE top_sec_svr')
sql = 'INSERT INTO top_sec_svr(top_svr, sec_svr, whois_sum) VALUES("%s", "%s", "%s")'
for top,sec,num in svr_num:
db.insert_no_commit(sql % (top,sec,num))
db.commit()
db.close()
def insert_db():
url_set = pd.read_csv('csv/kt-content.csv')
db = MySQL()
for i in range(len(url_set)):
print url_set.ix[i].values
sql = 'Insert ignore into benign_urls(url,type)VALUES ("%s","%s")' % (
url_set.ix[i].values[1], url_set.ix[i].values[2])
db.insert_no_commit(sql)
db.commit()
db.close()
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 update_db():
"""更新数据库"""
tld_whois = sum_all_whois()
total = 0
db = MySQL(DESTINATION_CONFIG)
sql = 'INSERT INTO tld_whois_sum_history(tld,whois_sum) VALUES("%s", "%s")' # 插入数据
for tld,whois in tld_whois:
db.insert_no_commit(sql % (tld,whois))
total += whois
db.commit()
sql = 'INSERT INTO whois_sum(tld_sum) VALUES("%s")'
db.insert(sql % total)
db.close()
def update_url_features(url_feature):
"""
更新数据库中词汇特征
:param url_feature:
:return:
"""
db = MySQL()
sql = 'UPDATE url_features SET url_length = "%s", domain="%s",domain_tokens="%s",domain_characters="%s",path="%s", ' \
'path_tokens="%s",path_characters="%s",path_brand="%s" WHERE url="%s"' % \
(url_feature[1],url_feature[2],url_feature[3],url_feature[4],url_feature[5],url_feature[6],
url_feature[7],url_feature[8],url_feature[0])
db.update(sql)
db.close()
def update_destination(tb_name, results):
"""
更新数据库
:param results:
:return:
"""
destination_db = MySQL(DESTINATION_CONFIG)
destination_db.truncate('TRUNCATE TABLE domain_whois_%s' % tb_name)
for item in results:
destination_db.insert_no_commit('INSERT INTO domain_whois_%s (tld,flag,whois_sum) VALUES ("%s","%s","%s")' % (
tb_name, item[0], item[1], item[2]))
destination_db.commit()
destination_db.close()
def update_top_sec_num():
"""
更新表msvr_ssvr,统计顶级服务器和二级服务器数量
"""
print str(datetime.now()), '开始更新数据库中顶级和二级服务器数量(msvr_ssvr)'
db = MySQL(DESTINATION_CONFIG)
sql = 'UPDATE msvr_ssvr SET msvr_ssvr.ssvr=(SELECT COUNT(DISTINCT sec_svr) FROM top_sec_svr)'
db.update(sql)
sql = 'UPDATE msvr_ssvr SET msvr=(SELECT COUNT(DISTINCT addr) FROM whois_addr)'
db.update(sql)
db.close()
print str(datetime.now()), '结束更新数据库中顶级和二级服务器数量(msvr_ssvr)'
def insert_db():
file_name = open('small_benign.txt')
urls = file_name.readlines()
db = MySQL()
count = 0
for url in urls:
sql = 'Insert into url_features(url,malicious)VALUES ("%s","0")'%(url.strip())
db.insert_no_commit(sql)
count += 1
db.commit()
db.close()
print count
def update_top_sec_num():
"""
更新表msvr_ssvr,统计顶级服务器和二级服务器数量
"""
print str(datetime.now()), '开始更新数据库中顶级和二级服务器数量(msvr_ssvr)'
db = MySQL(DESTINATION_CONFIG)
sql = 'UPDATE msvr_ssvr SET msvr_ssvr.ssvr=(SELECT COUNT(DISTINCT sec_svr) FROM top_sec_svr)'
db.update(sql)
sql = 'UPDATE msvr_ssvr SET msvr=(SELECT COUNT(DISTINCT addr) FROM whois_addr)'
db.update(sql)
db.close()
print str(datetime.now()), '结束更新数据库中顶级和二级服务器数量(msvr_ssvr)'
def update_db():
"""更新数据库"""
tld_whois = sum_all_whois()
total = 0
db = MySQL(DESTINATION_CONFIG)
sql = 'INSERT INTO tld_whois_sum_history(tld,whois_sum) VALUES("%s", "%s")' # 插入数据
for tld, whois in tld_whois:
db.insert_no_commit(sql % (tld, whois))
total += whois
db.commit()
sql = 'INSERT INTO whois_sum(tld_sum) VALUES("%s")'
db.insert(sql % total)
db.close()
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 insert_domains(domain_set):
"""
更新数据库中词汇特征
:param url_feature:
:return:
"""
db = MySQL()
for domain in domain_set:
sql = 'Insert into domain_features(domain,tld,tld_token_count,domain_length,domain_characters,domain_digit)VALUES ("%s","%s","%s","%s","%s","%s")' \
%(domain['domain'],domain['tld'],domain['tld_token_count'],domain['domain_length'],domain['character'],domain['digit'])
db.insert_no_commit(sql)
db.commit()
db.close()
def insert_db():
file_name = open('small_benign.txt')
urls = file_name.readlines()
db = MySQL()
count = 0
for url in urls:
sql = 'Insert into url_features(url,malicious)VALUES ("%s","0")' % (
url.strip())
db.insert_no_commit(sql)
count += 1
db.commit()
db.close()
print count
def insert_domains(domain_set):
"""
更新数据库中词汇特征
:param url_feature:
:return:
"""
db = MySQL()
for domain in domain_set:
sql = 'Insert into domain_features(domain,tld,tld_token_count,domain_length,domain_characters,domain_digit)VALUES ("%s","%s","%s","%s","%s","%s")' \
%(domain['domain'],domain['tld'],domain['tld_token_count'],domain['domain_length'],domain['character'],domain['digit'])
db.insert_no_commit(sql)
db.commit()
db.close()
def update_domain_info(whois_dict, whois_geo, rule):
"""
依据更新规则，更新信息到数据库表中
"""
domain = whois_dict['domain']
db = MySQL(SOURCE_CONFIG)
if 1 in rule:
print domain + ' ,' + str(rule) + str(datetime.now())
# 注意更新顺序，先更新WHOWAS表
if rule[1]: # 是否更新WHOWAS表
update_whowas(db, domain)
if rule[0]: # 是否更新WHOIS表
update_domain_whois_locate(db, whois_dict, whois_geo)
db.close()
def insert_host_error(error,process_ids):
"""更新主机错误"""
db = MySQL(SOURCE_CONFIG)
error_info = '主机异常:'+ str(error)
status = '异常'
sql = 'insert into process_status (process_id,cpu,mem,vsz,rss,status,error_info,log_size) values("%s","%s","%s","%s","%s","%s","%s","%s")'
for _,process_id,_,_ in process_ids:
db.insert(sql % (process_id, 0, 0, 0, 0, status, error_info,'-3')) # -3表示日志的主机错误
email, host, pid, code_route, code_name,warning_times = process_id_to_user(process_id)
if warning_times > 0:
content = (pid, host, status, error_info, code_route, code_name)
if send_process_exception([email], content):
print "邮件预警成功:" + email
reduce_warning_times(process_id)
else:
print "邮件预警失败:" + email
db.close()
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 update_db():
"""更新数据库"""
db = MySQL(DESTINATION_CONFIG)
truncate_sql = 'TRUNCATE TABLE tld_whois_flag'
db.truncate(truncate_sql)
sql = 'INSERT INTO tld_whois_flag(tld, flag,flag_detail, whois_sum) VALUES("%s", "%s", "%s", "%s")'
tld_flag_num = sum_all_flags()
for tld, flag_detail, num in tld_flag_num:
try:
if int(flag_detail) < 0:
flag = '0'
elif int(flag_detail) >= 120:
flag = '1'
elif flag_detail == '102' or flag_detail == '110' or flag_detail == '112':
flag = '2'
else:
flag = '3'
db.insert_no_commit(sql % (tld,flag,flag_detail,num))
except:
continue
db.commit()
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
