def get_mac_table():
s = "select MAC,Organization from MAC_OUI"
sql_result = db_base.execute_result(s)
mac_result = {}
for i in sql_result:
mac_result[i[0]] = i[1]
#print mac_result
return mac_result
def get_database_df():
sql = "select ip,MAC,mfrs from MAC_analysis"
sql_result = db_base.execute_result(sql)
np_list = []
for i in sql_result:
np_list.append([i[0], i[1], i[2]])
df_result = pd.DataFrame(np_list, columns=['user', 'mac', 'pingpai'])
return df_result
def write_databae():
data = get_data()
if not isinstance(data, pd.DataFrame):
return
time_data = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
for i in list(data.index):
sql1 = "insert into protocol(date,protocol,incomingTraff,outgoingTraff) values('%s','%s',%d,%d)" % (
time_data, i, data.loc[i, 'resp_bytes'], data.loc[i, 'orig_bytes'])
origin_ip = db_base.execute_result(sql1)
def get_data_base_host():
'''
获取数据库中收录的网站host
'''
sql="select host,name from application_host"
sql_result=db_base.execute_result(sql)
result={}
for i in sql_result:
result[i[0].split('.')[0]]=i[1]
return result
def get_data_base_host():
'''
获取数据库中收录的网站host
'''
sql = "select host,name from application_host"
sql_result = db_base.execute_result(sql)
result = {}
for i in sql_result:
result[i[0].split('.')[0]] = i[1]
return result
def main2():
f=file('temp.pkl','r')
result_list=pickle.load(f)
top10=Counter(result_list).most_common(10)
sql="select name,host from application_host"
result=db_base.execute_result(sql)
host_list={}
for i in result:
host_list[i[1].split('.')[0]]=i[0]
for i in top10:
print '{}:{}'.format(host_list[i[0]],i[1])
def get_data_base_port():
'''
获取数据库中收录的APP
'''
sql="select port1,name from application_port"
sql_result=db_base.execute_result(sql)
result={}
for i in sql_result:
result[i[0]]=i[1]
print result
return result
def get_database_df(date_time):
'''
获取date_time当天数据库中的数据
返回dataframe,“用户，host，duration”
'''
sql="select user,webhost,duration from user_web where date='%s'"%(date_time)
sql_result=db_base.execute_result(sql)
np_list=[]
for i in sql_result:
np_list.append([i[0],i[1],i[2]])
df_result=pd.DataFrame(np_list,columns=['user', 'webhost','duration'])
return df_result
def get_database_df(date_time):
'''
获取date_time当天数据库中的数据
返回dataframe
'''
sql = "select user,app_name,duration from my_app where date='%s'" % (
date_time)
sql_result = db_base.execute_result(sql)
np_list = []
for i in sql_result:
np_list.append([i[0], i[1], i[2]])
df_result = pd.DataFrame(np_list, columns=['user', 'app_name', 'duration'])
return df_result
def get_database_df():
'''
获取date_time当天数据库中的数据
返回dataframe
'''
#sql="select white,gray,black from anomaly_list where date='%s'"%(date_time)
sql = "truncate table anomaly_list"
sql_result = db_base.execute_result(sql)
#np_list=[]
#for i in sql_result:
#np_list.append([i[0],i[1],i[2]])
#df_result=pd.DataFrame(np_list,columns=['user', 'app_name','duration'])
return sql_result
def get_database_df(date_time):
'''
'''
sql = "select video_name,user,orig_ip_bytes,resp_ip_bytes from video_flow_bytes where date='%s'" % (
date_time)
sql_result = db_base.execute_result(sql)
np_list = []
for i in sql_result:
np_list.append([i[0], i[1], i[2], i[3]])
df_result = pd.DataFrame(
np_list,
columns=['video_name', 'user', 'orig_ip_bytes', 'resp_ip_bytes'])
return df_result
def get_database_df(date_time):
'''
获取date_time当天数据库中的数据
返回dataframe,“file_type，count”
'''
sql = "select file_type,count1 from file_type where date='%s'" % (
date_time)
sql_result = db_base.execute_result(sql)
np_list = []
for i in sql_result:
np_list.append([i[0], i[1]])
df_result = pd.DataFrame(np_list, columns=['file_type', 'count1'])
return df_result
def get_database_df():
'''
获取date_time当天数据库中的数据
返回dataframe
'''
sql = "select * from train "
sql_result = db_base.execute_result(sql)
df = pd.DataFrame(list(sql_result))
np_list = []
user_list = []
mp = {
'tcp': 1,
'udp': 2,
'ssl': 3,
'http': 1,
'dns': 2,
'dhcp': 3,
'dtls': 4,
'irc': 5,
'xmpp': 6
}
df = df.replace(mp)
sql_result = df.values
for i in sql_result:
pro = i[3]
service = i[4]
if i[10] == None or i[10] == 'Null':
browser = 0
else:
browser = 1
if i[11] == None or i[11] == 'Null':
web = 0
else:
web = 1
if i[12] == None or i[12] == 'Null':
app = 0
else:
app = 1
if i[13] == None or i[13] == 'Null':
result.append(0)
else:
result.append(10)
np_list.append(
[[pro, service, i[5], i[6], i[7], i[8], i[9], browser, web, app],
[result]])
user_list.append(
[[i[0], i[1], i[2]],
[pro, service, i[5], i[6], i[7], i[8], i[9], browser, web, app]])
return np_list, user_list
def write_database():
user_data_out=get_user_data()#待插入的数据
if len(user_data_out)==0:
return
time_data=get_time_data()
sql0="select date,user,beginTime,endTime,outgoingTraff,incomingTraff from user_traff where date='%s'"%(time_data)
user_iterms=db_base.execute_result(sql0)
user_dict={}#已经存入的数据
if len(user_iterms)>0:
for user_iterm in user_iterms:
user_dict[user_iterm[1]]=[user_iterm[2],user_iterm[3],user_iterm[5],user_iterm[4]]
#########################
user_data_insert=[]#需插入的数据
user_data_update=[]#需更新的数据
update_ip_list=[]
for user in user_data_out:
if user.get_ip() not in user_dict.iterkeys():
user_data_insert.append(user)
else:
update_ip_list.append(user.get_ip())
user.min_ts=user_dict[user.get_ip()][0]
user.incoming=user.incoming+user_dict[user.get_ip()][2]
user.outgoing=user.outgoing+user_dict[user.get_ip()][3]
user_data_update.append(user)
################################
if user_data_insert!=[]:
sql1="insert into user_traff(date,user,beginTime,endTime,outgoingTraff,incomingTraff) values"
for user in user_data_insert:
sql1=sql1+"('%s','%s','%s','%s',%d,%d)," %(time_data,user.get_ip(),user.get_min_ts(),user.get_max_ts(),user.get_outgoing(),user.get_incoming())
sql1=sql1[:-1]
db_base.execute_no_result(sql1)#批量插入
if user_data_update!=[]:
sql2="delete from user_traff where user in ("
for ip in update_ip_list:
sql2=sql2+"'%s'," %(ip)
sql2=sql2[:-1]+") and date='%s'"%(time_data)
db_base.execute_no_result(sql2)#批量删除
sql3="insert into user_traff(date,user,beginTime,endTime,outgoingTraff,incomingTraff) values"
for user in user_data_update:
sql3=sql3+"('%s','%s','%s','%s',%d,%d)," %(time_data,user.get_ip(),user.min_ts,user.get_max_ts(),user.get_outgoing(),user.get_incoming())
sql3=sql3[:-1]
db_base.execute_no_result(sql3)#再次批量插入
def write_database():
user_data_out=get_user_data()#待插入的数据
if len(user_data_out)==0:
return
time_data=get_time_data()
sql0="select date,user,beginTime,endTime,outgoingTraff,incomingTraff from user_traff where date='%s'"%(time_data)
user_iterms=db_base.execute_result(sql0)
user_dict={}#已经存入的数据
if len(user_iterms)>0:
for user_iterm in user_iterms:
user_dict[user_iterm[1]]=[user_iterm[2],user_iterm[3],user_iterm[5],user_iterm[4]]
#########################
user_data_insert=[]#需插入的数据
user_data_update=[]#需更新的数据
update_ip_list=[]
for user in user_data_out:
if user.get_ip() not in user_dict.iterkeys():
user_data_insert.append(user)
else:
update_ip_list.append(user.get_ip())
user.min_ts=user_dict[user.get_ip()][0]
user.incoming=user.incoming+user_dict[user.get_ip()][2]
user.outgoing=user.outgoing+user_dict[user.get_ip()][3]
user_data_update.append(user)
################################
if user_data_insert!=[]:
sql1="insert into user_traff(date,user,beginTime,endTime,outgoingTraff,incomingTraff) values"
for user in user_data_insert:
sql1=sql1+"('%s','%s','%s','%s',%d,%d)," %(time_data,user.get_ip(),user.get_min_ts(),user.get_max_ts(),user.get_outgoing(),user.get_incoming())
sql1=sql1[:-1]
db_base.execute_no_result(sql1)#批量插入
if user_data_update!=[]:
sql2="delete from user_traff where user in ("
for ip in update_ip_list:
sql2=sql2+"'%s'," %(ip)
sql2=sql2[:-1]+") and date='%s'"%(time_data)
db_base.execute_no_result(sql2)#批量删除
sql3="insert into user_traff(date,user,beginTime,endTime,outgoingTraff,incomingTraff) values"
for user in user_data_update:
sql3=sql3+"('%s','%s','%s','%s',%d,%d)," %(time_data,user.get_ip(),user.min_ts,user.get_max_ts(),user.get_outgoing(),user.get_incoming())
sql3=sql3[:-1]
db_base.execute_no_result(sql3)#再次批量插入
def get_data_base():
sql="select name,host from application_host"
result=db_base.execute_result(sql)
return result
from fileTraff import file_path
from fileTraff import read_file
from fileTraff import file_name
from DBTraff import db_base
import pandas as pd
import sys
import time
import pickle
import datetime
import os
time_data=time.strftime('%Y-%m-%d',time.localtime(time.time()))
sql1="select date,user,beginTime,endTime,outgoingTraff,incomingTraff from user_traff where date='%s'"%time_data
origin_ip=db_base.execute_result(sql1)
print origin_ip[0][1]