def get_time_duration():
sql = "select Second from basic_part_data limit 1"
start_time = db_helper.get_data(sql)[0][0]
sql = "select Second from basic_part_data order by Second desc limit 1"
end_time = db_helper.get_data(sql)[0][0]
duration = end_time - start_time
return duration
def get(self):
if request.authorization and request.authorization.username == creds.api_username and request.authorization.password == creds.api_password:
connection = db.create_connection()
results = db.get_data(connection)[0][0]
return results
return make_response(
'Could not verify!', 401,
{'WWW-Authentication': 'Basic realm="Login Required"'})
def create_undirect_whole_graph(self):
sql = "select * from social_network_ip_links"
results = db_helper.get_data(sql)
print(results)
i = 0
for r in results:
self.G.add_edge(r[1], r[2], weight=r[3])
i = i + 1
def get_ip_num_dict():
ip_num_dict = {}
sql = "Select Distinct Src_IP, Count(*) From basic_part_data Group by Src_IP"
results = db_helper.get_data(sql)
results = list(results)
for result in results:
ip_num_dict[result[0]] = result[1]
sql = "Select Distinct Dst_IP, Count(*) From basic_part_data Group by Dst_IP"
results = db_helper.get_data(sql)
results = list(results)
dict_keys = ip_num_dict.keys()
for result in results:
if result[0] in dict_keys:
ip_num_dict[result[0]] = ip_num_dict.get(result[0]) + result[1]
else:
ip_num_dict[result[0]] = result[1]
return ip_num_dict
def get_ip_connect_dict():
sql = "Select Distinct Src_IP,Dst_IP, Count(*) From basic_part_data Group by Src_IP,Dst_IP"
ip_connect_dict = {}
results = db_helper.get_data(sql)
results = list(results)
for result in results:
connection_pair = (result[0], result[1])
ip_connect_dict[connection_pair] = result[2]
return ip_connect_dict
def get_domain_IP():
sql = "select Domain,IP_address from dns_part_data where Direction = 'Response'"
results = db_helper.get_data(sql)
domain_list = []
IP_list = []
for result in results:
domain_list.append(result[0].split(".")[0])
IP_list.append(result[1])
return domain_list,IP_list
def create_undirect_whole_graph(self):
#sql = "select * from social_network_ip_links"
sql = "select * from social_network_ip_links_copy"
results = db_helper.get_data(sql)
i = 0
for r in results:
self.G.add_edge(r[1], r[2], weight=r[3])
i = i + 1
print("无向图绘制完成")
self.save_IP_social_details()
def get_length_distribution(total):
min = 0
max = 200
distribution_list = []
while max < 1601:
sql = "Select COUNT(*) FROM basic_part_data where Len> " + str(
min) + " and Len < " + str(max)
result = db_helper.get_data(sql)[0][0]
result = round(result / total * 100, 2)
distribution_list.append(result)
min += 200
max += 200
return distribution_list
def connect_nums(self, IPs):
sql = ""
node_list = []
for IP in IPs:
sum = 0
sql = "select Number from social_network_ip_links where Src_IP = '" + IP + "' or Dst_IP = '" + IP + "'"
results = db_helper.get_data(sql)
for result in results:
sum += result[0]
node_connect = (IP, sum)
node_list.append(node_connect)
order_connect_rank = sorted(node_list,
key=lambda x: x[1],
reverse=True)
return order_connect_rank
def feature_score(id, now_data_list):
sql = "Select Protocol_proportion,Length_distribution From net_statistic_feature where Id = " + str(
id)
print(sql)
results = db_helper.get_data(sql)
print(results)
temp_list = list(results[0])
print(temp_list)
datas = []
for string in temp_list:
datas += re.split(r",|", string)
print(datas)
squre = 0
for i in range(0, 13):
squre += pow(now_data_list[i] - float(datas[i]), 2)
score = math.sqrt(squre)
print(score)
def get_packet_num():
db_names = [
"basic_part_data", "tcp_part_data", "udp_part_data", "http_part_data",
"https_part_data", "dns_part_data"
]
strs = [
"packets", "tcp_packets", "udp_packets", "http_packets",
"https_packets", "dns_packets"
]
i = 0
packet_nums = {}
for db_name in db_names:
sql = "SELECT COUNT(*) FROM " + db_name
result = db_helper.get_data(sql)[0][0]
packet_nums[strs[i]] = result
i += 1
return packet_nums
def get_tcp_flow():
sql = "SELECT DISTINCT Src_IP,Dst_IP,Src_port,Dst_port,Protocol,COUNT(*)FROM basic_part_data basic, " \
"tcp_part_data tcp where basic.Id = tcp.Id Group by Src_IP,Dst_IP,Src_port,Dst_port,Protocol"
results = db_helper.get_data(sql)
results = list(results)
tcp_flow_dict = {}
for result in results:
exist_keys = tcp_flow_dict.keys()
temp_key = (result[0], result[1], result[2], result[3], result[4])
symmetrical_temp_key = (result[1], result[0], result[3], result[2],
result[4])
if temp_key in exist_keys:
tcp_flow_dict[temp_key] = tcp_flow_dict[temp_key] + result[5]
elif symmetrical_temp_key in exist_keys:
tcp_flow_dict[symmetrical_temp_key] = tcp_flow_dict[
symmetrical_temp_key] + result[5]
else:
tcp_flow_dict[temp_key] = result[5]
return tcp_flow_dict
def get_average_num():
sql = "select AVG(Len) from basic_part_data"
average_len = db_helper.get_data(sql)[0][0]
return average_len
