<jupyter_start><jupyter_text>Query Output PreprocessingThe graph database Dgraph returns query results in JSON fromat. The queries consist of getting all `originated` and all `responded` connections of a specified host. The `query_handler` tool converts these JSON outputs to csv files (two csv files for each host with some IP address, one for each connection direction (`originated`, `responded`)). This Jupyter notebook is used to:1. Compute the neighbourhoods of these hosts. *(For each connection compute its neighbourhood from connections in a given time interval.)*2. Assign labels. *(All connections from day 1 are considered to be "normal traffic", in second day the labels are assigned based on a combination of attacker IP and a timestamp corresponding to a particular attack. The timestamps were discovered using an automated network intrusion detection tool [Snort](https://www.snort.org/).)*3. Concat DataFrames to one final `df` & write the result to a single file, ready for ML preprocessing (feature engineering).  Neighbourhood Computation 1. Load the data<jupyter_code>import pandas as pd
import numpy as np
import os

PREFIX = '/home/sramkova/diploma_thesis_data/test_stats/'
DAY_1_DIR_PATH_ORIG = PREFIX + 'day_1/originated'
DAY_1_DIR_PATH_RESP = PREFIX + 'day_1/responded'
DAY_2_DIR_PATH_ORIG = PREFIX + 'day_2/originated'
DAY_2_DIR_PATH_RESP = PREFIX + 'day_2/responded'


file_list_day1_orig = []
file_list_day1_resp = []
file_list_day2_orig = []
file_list_day2_resp = []

def get_file_names(file_list, dir_path):
    for filename in os.listdir(dir_path):
        # only IPv4: 
        if 'f' not in filename and filename.endswith('.csv'):
            # (if there is an 'f' present in the name of the file, it means that the file contains 
            # connections of a host with IPv6 address)
            file_list.append(filename)

# load filenames to lists:
get_file_names(file_list_day1_orig, DAY_1_DIR_PATH_ORIG)
get_file_names(file_list_day1_resp, DAY_1_DIR_PATH_RESP)
get_file_names(file_list_day2_orig, DAY_2_DIR_PATH_ORIG)
get_file_names(file_list_day2_resp, DAY_2_DIR_PATH_RESP)

print(len(file_list_day1_orig))
print(len(file_list_day1_resp))
print(len(file_list_day2_orig))
print(len(file_list_day2_resp))
# load as dataframes to a dictionary for easier processing:

# elements of the dictionary are in a form: { host.ip -> df with connections of corresponding host }
dfs_day1_orig = {}
dfs_day1_resp = {}
dfs_day2_orig = {}
dfs_day2_resp = {}

def load_files_to_dfs(dfs_dict, file_list, dir_path, prefix):
    prefix_name = 'output-' + prefix
    for filename in file_list:
        file_ip = filename
        file_ip = file_ip.replace(prefix_name, '').replace('.csv', '')
        df_conns = pd.read_csv(dir_path + '/' + filename)

        df_conns['connection.time'] = pd.to_datetime(df_conns['connection.ts'])
        
        # missing connection.service value means that Zeek wasn't able to extract the service => nulls can 
        # be treated as a new category
        df_conns['connection.service'].fillna('none', inplace = True)

        dfs_dict[file_ip] = df_conns

load_files_to_dfs(dfs_day1_orig, file_list_day1_orig, DAY_1_DIR_PATH_ORIG, 'o-')
load_files_to_dfs(dfs_day1_resp, file_list_day1_resp, DAY_1_DIR_PATH_RESP, 'r-')
load_files_to_dfs(dfs_day2_orig, file_list_day2_orig, DAY_2_DIR_PATH_ORIG, 'o-')
load_files_to_dfs(dfs_day2_resp, file_list_day2_resp, DAY_2_DIR_PATH_RESP, 'r-')

print(len(dfs_day1_orig))
print(len(dfs_day1_resp))
print(len(dfs_day2_orig))
print(len(dfs_day2_resp))
# check first 5 originated connections of a specified host from day 1:
pd.set_option('display.max_columns', None)
dfs_day1_orig['192.168.3.11'].head()
dfs_day2_orig['192.168.3.11'].head()<jupyter_output><empty_output><jupyter_text>2. Compute neighbourhoods for each row based on a time interval(e.g. time interval: +- 5 minutes)<jupyter_code># various stat functions on attributes from neighbourhood:

def get_counts(df, prefix):
    # counts (overall + counts of different protocols): 
    proto_tcp_count = 0
    proto_udp_count = 0
    proto_icmp_count = 0
            
    if 'connection.proto' in df:
        proto_counts = df['connection.proto'].value_counts()
        proto_tcp_count = proto_counts['tcp'] if 'tcp' in proto_counts else 0
        proto_udp_count = proto_counts['udp'] if 'udp' in proto_counts else 0
        proto_icmp_count = proto_counts['icmp'] if 'icmp' in proto_counts else 0
    
    return {prefix + '_total': len(df.index),
            prefix + '_proto_tcp_count': proto_tcp_count,
            prefix + '_proto_udp_count': proto_udp_count,
            prefix + '_proto_icmp_count': proto_icmp_count
           }

def get_modes(df, prefix):
    # .mode()[0] return the value of a categorical variable that appeared the most times
    return {prefix + '_connection.protocol_mode': df['connection.proto'].mode()[0] if 'connection.proto' in df else '-',
            prefix + '_connection.service_mode': df['connection.service'].mode()[0] if 'connection.service' in df else '-',
            prefix + '_connection.conn_state_mode': df['connection.conn_state'].mode()[0] if 'connection.conn_state' in df else '-'
           }

def get_means(df, prefix):
    # .mean() returns mean of the corresponding numerical attribute variable values
    return {prefix + '_connection.time_mean': df['connection.time'].mean() if 'connection.time' in df else cur_time,
            prefix + '_connection.duration_mean': df['connection.duration'].mean() if 'connection.duration' in df else 0, 
            # prefix + '_connection.orig_p_mean': df['connection.orig_p'].mean() if 'connection.orig_p' in df else 0, 
            prefix + '_connection.orig_bytes_mean': df['connection.orig_bytes'].mean() if 'connection.orig_bytes' in df else 0,
            prefix + '_connection.orig_pkts_mean': df['connection.orig_pkts'].mean() if 'connection.orig_pkts' in df else 0, 
            # prefix + '_connection.resp_p_mean': df['connection.resp_p'].mean() if 'connection.resp_p' in df else 0,
            prefix + '_connection.resp_bytes_mean': df['connection.resp_bytes'].mean() if 'connection.resp_bytes' in df else 0,
            prefix + '_connection.resp_pkts_mean': df['connection.resp_pkts'].mean() if 'connection.resp_pkts' in df else 0
           }

def get_stats_means(df, prefix):
    # .mean() returns mean of the corresponding numerical attribute variable values
    return {prefix + '_dns_count_mean': df['dns_count'].mean() if 'dns_count' in df else 0,
            prefix + '_ssh_count_mean': df['ssh_count'].mean() if 'ssh_count' in df else 0, 
            prefix + '_http_count_mean': df['http_count'].mean() if 'http_count' in df else 0,
            prefix + '_ssl_count_mean': df['ssl_count'].mean() if 'ssl_count' in df else 0,
            prefix + '_files_count_mean': df['files_count'].mean() if 'files_count' in df else 0
           }

def get_medians(df, prefix):
    # .median() returns median of the corresponding numerical attribute variable values
    return {prefix + '_connection.time_median': df['connection.time'].median() if 'connection.time' in df else cur_time,
            prefix + '_connection.duration_median': df['connection.duration'].median() if 'connection.duration' in df else 0, 
            # prefix + '_connection.orig_p_median': df['connection.orig_p'].median() if 'connection.orig_p' in df else 0,
            prefix + '_connection.orig_bytes_median': df['connection.orig_bytes'].median() if 'connection.orig_bytes' in df else 0,
            prefix + '_connection.orig_pkts_median': df['connection.orig_pkts'].median() if 'connection.orig_pkts' in df else 0, 
            # prefix + '_connection.resp_p_median': df['connection.resp_p'].median() if 'connection.resp_p' in df else 0,
            prefix + '_connection.resp_bytes_median': df['connection.resp_bytes'].median() if 'connection.resp_bytes' in df else 0,
            prefix + '_connection.resp_pkts_median': df['connection.resp_pkts'].median() if 'connection.resp_pkts' in df else 0
           }

def get_orig_ports(df, prefix):
    # count orig_p categories:
    orig_well_known_count = 0
    orig_reg_or_dyn_count = 0
    unique_orig_p_list = df['connection.orig_p'].unique().tolist()
    values_orig_p = df['connection.orig_p'].value_counts()
    
    for uniq_p in unique_orig_p_list:
        if uniq_p < 1024:
            orig_well_known_count += values_orig_p[uniq_p]
        else:
            orig_reg_or_dyn_count += values_orig_p[uniq_p]
            
    return {prefix + '_orig_p_well_known_count': orig_well_known_count,
            prefix + '_orig_p_reg_or_dyn_count': orig_reg_or_dyn_count}

def get_resp_ports(df, prefix):
    # count resp_p categories:
    common_ports = {21: 0, 
                    22: 0, 
                    53: 0, 
                    80: 0, 
                    123: 0, 
                    443: 0, 
                    3389: 0}
    resp_well_known = 0
    resp_reg = 0
    resp_dyn = 0
    unique_resp_p_list = df['connection.resp_p'].unique().tolist()
    values_resp_p = df['connection.resp_p'].value_counts()
    
    for uniq_p in unique_resp_p_list:
        if uniq_p in common_ports.keys():
            common_ports[uniq_p] += values_resp_p[uniq_p]
        elif uniq_p < 1024:
            resp_well_known += values_resp_p[uniq_p]
        elif uniq_p < 49152:
            resp_reg += values_resp_p[uniq_p]
        else:
            resp_dyn += values_resp_p[uniq_p]
            
    return {prefix + '_resp_p_21_count': common_ports[21],
            prefix + '_resp_p_22_count': common_ports[22],
            prefix + '_resp_p_53_count': common_ports[53], 
            prefix + '_resp_p_80_count': common_ports[80],
            prefix + '_resp_p_123_count': common_ports[123],
            prefix + '_resp_p_443_count': common_ports[443],
            prefix + '_resp_p_3389_count': common_ports[3389],
            prefix + '_resp_p_well_known_count': resp_well_known,
            prefix + '_resp_p_reg_count': resp_reg,
            prefix + '_resp_p_dyn_count': resp_dyn}
def generate_duration_filter(duration_val):
    # based on constants from data_exploration.ipynb
    if duration_val <= 0.0:
        return 0.000001, None
    elif duration_val <= 0.0001:
        return 0.000001, 0.001
    elif duration_val <= 0.009:
        return 0.001, 0.05
    elif duration_val <= 0.5:
        return 0.05, 1.5
    elif duration_val <= 5:
        return 1.5, 10
    elif duration_val <= 15:
        return 10, 20
    elif duration_val <= 30:
        return 20, 40
    elif duration_val <= 50:
        return 40, 60
    elif duration_val <= 75:
        return 60, 90
    elif duration_val <= 100:
        return 75, 110
    return None, 100

def generate_bytes_filter(bytes_val):
    if bytes_val == 0:
        return 0, 0
    elif bytes_val <= 1450:
        return bytes_val - 50, bytes_val + 50
    elif bytes_val <= 35000:
        return bytes_val - 500, bytes_val + 500
    else:
        return None, bytes_val - 1000
def get_similar_count(df, row, prefix):
    # protocol filter
    mask = (df['connection.proto'] == row['connection.proto'])
    df_filtered = df.loc[mask]
    
    # service filter
    mask = (df_filtered['connection.service'] == row['connection.service'])
    df_filtered = df_filtered.loc[mask]
    
    # conn_state filter
    mask = (df_filtered['connection.conn_state'] == row['connection.conn_state'])
    df_filtered = df_filtered.loc[mask]
    
    # duration filter
    lower, upper = generate_duration_filter(row['connection.duration'])
    if lower:
        mask = df_filtered['connection.duration'] >= lower
        df_filtered = df_filtered.loc[mask]
    if upper:
        mask = df_filtered['connection.duration'] <= upper
        df_filtered = df_filtered.loc[mask]
        
    # _bytes filter
    lower, upper = generate_duration_filter(row['connection.orig_bytes'])
    if lower:
        mask = df_filtered['connection.orig_bytes'] >= lower
        df_filtered = df_filtered.loc[mask]
    if upper:
        mask = df_filtered['connection.orig_bytes'] <= upper
        df_filtered = df_filtered.loc[mask]
        
    lower, upper = generate_duration_filter(row['connection.resp_bytes'])
    if lower:
        mask = df_filtered['connection.resp_bytes'] >= lower
        df_filtered = df_filtered.loc[mask]
    if upper:
        mask = df_filtered['connection.resp_bytes'] <= upper
        df_filtered = df_filtered.loc[mask]
    
    # _ip_bytes filter
    mask = (df_filtered['connection.orig_ip_bytes'] >= row['connection.orig_ip_bytes'] - 50) & (df_filtered['connection.orig_ip_bytes'] <= row['connection.orig_ip_bytes'] + 50)
    df_filtered = df_filtered.loc[mask]
    mask = (df_filtered['connection.resp_ip_bytes'] >= row['connection.resp_ip_bytes'] - 50) & (df_filtered['connection.resp_ip_bytes'] <= row['connection.resp_ip_bytes'] + 50)
    df_filtered = df_filtered.loc[mask]
    
    # remove original connection from neighbourhood (empty will have size 0 instead of 1)
    mask = (df_filtered['connection.uid'] != row['connection.uid'])
    df_filtered = df_filtered.loc[mask]

    return {prefix + '_similar_conns_count': df_filtered.shape[0]}
def check_attr_value(x, attr_str, row_attr_vals_list):
    if isinstance(x, float) and np.isnan(x):
        return False
    
    if isinstance(x, list) and len(x) < 1:
        return False
    
    if isinstance(x, str) and x == '[]':
        return False
    
    if isinstance(row_attr_vals_list, list) and len(row_attr_vals_list) > 0:
        for attribute in x:
            if attribute in row_attr_vals_list:
                return True
    return False

def get_similar_attributes_count(df, row, prefix):
    neighbourhood_attributes_dict = {}
    attributes = ['dns_qtype', 'dns_rcode', 'ssh_auth_attempts', 'ssh_host_key', 'http_method', 'http_status_code', 
                  'http_user_agent', 'ssl_version', 'ssl_cipher', 'ssl_curve', 'ssl_validation_status', 'files_source',
                  'file_md5']
    
    for attr in attributes:
        if not row[attr]:
            # attribute value list is empty, no similarity is counted
            attr_dict = {prefix + '_similar_' + attr + '_count': 0}
            neighbourhood_attributes_dict.update(attr_dict)
        else:
            # filter
            mask = df[attr].apply(lambda x: check_attr_value(x, attr, row[attr]))
            df_filtered = df.loc[mask]

            # remove original connection from neighbourhood (empty will have size 0 instead of 1)
            mask = (df_filtered['connection.uid'] != row['connection.uid'])
            df_filtered = df_filtered.loc[mask]

            # add attribute count to dictionary that contains all counts
            attr_dict = {prefix + '_similar_' + attr + '_count': df_filtered.shape[0]}
            neighbourhood_attributes_dict.update(attr_dict)
    
    return neighbourhood_attributes_dict
def compute_time_neighbourhood(host_ip, dfs_list, time_col_name, cur_time, time_start, time_end, row, prefix):
    if host_ip in dfs_list:
        ip_df = dfs_list[host_ip]
        mask = (ip_df[time_col_name] > time_start) & (ip_df[time_col_name] <= time_end)
        df = ip_df.loc[mask]

        if len(df) > 0:
            neighbourhood_dict = {}

            neighbourhood_counts = get_counts(df, prefix)
            neighbourhood_modes = get_modes(df, prefix)
            neighbourhood_means = get_means(df, prefix)
            # neighbourhood_medians = get_medians(df, prefix)
            neighbourhood_orig_ports = get_orig_ports(df, prefix)
            neighbourhood_resp_ports = get_resp_ports(df, prefix)
            neighbourhood_stats_means = get_stats_means(df, prefix)
            neighbourhood_similar_count = get_similar_count(df, row, prefix)
            neighbourhood_similar_attributes_count = get_similar_attributes_count(df, row, prefix)
            
            neighbourhood_dict.update(neighbourhood_counts)
            neighbourhood_dict.update(neighbourhood_modes)
            neighbourhood_dict.update(neighbourhood_means)
            # neighbourhood_dict.update(neighbourhood_medians)
            neighbourhood_dict.update(neighbourhood_orig_ports)
            neighbourhood_dict.update(neighbourhood_resp_ports)
            neighbourhood_dict.update(neighbourhood_stats_means)
            neighbourhood_dict.update(neighbourhood_similar_count)
            neighbourhood_dict.update(neighbourhood_similar_attributes_count)
            
            return neighbourhood_dict

    return {prefix + '_total': 0,
            prefix + '_proto_tcp_count': 0,
            prefix + '_proto_udp_count': 0,
            prefix + '_proto_icmp_count': 0,
            prefix + '_connection.protocol_mode': '-',
            prefix + '_connection.service_mode': '-',
            prefix + '_connection.conn_state_mode': '-',
            prefix + '_connection.time_mean': cur_time, # time_mean: 0 could not be here => problem later with time conversion (missing year) 
                                                        # (but does it make sense as a default value?)
            prefix + '_connection.duration_mean': 0, 
            # prefix + '_connection.orig_p_mean': 0,
            prefix + '_connection.orig_bytes_mean': 0,
            prefix + '_connection.orig_pkts_mean': 0,
            # prefix + '_connection.resp_p_mean': 0,
            prefix + '_connection.resp_bytes_mean': 0,
            prefix + '_connection.resp_pkts_mean': 0,
            # prefix + '_connection.time_median': cur_time,
            # prefix + '_connection.duration_median': 0, 
            # prefix + '_connection.orig_p_median': 0,
            # prefix + '_connection.orig_bytes_median': 0,
            # prefix + '_connection.orig_pkts_median': 0, 
            # prefix + '_connection.resp_p_median': 0,
            # prefix + '_connection.resp_bytes_median': 0,
            # prefix + '_connection.resp_pkts_median': 0, 
            prefix + '_orig_p_well_known_count': 0,
            prefix + '_orig_p_reg_or_dyn_count': 0,
            prefix + '_resp_p_21_count': 0,
            prefix + '_resp_p_22_count': 0,
            prefix + '_resp_p_53_count': 0, 
            prefix + '_resp_p_80_count': 0,
            prefix + '_resp_p_123_count': 0,
            prefix + '_resp_p_443_count': 0,
            prefix + '_resp_p_3389_count': 0,
            prefix + '_resp_p_well_known_count': 0,
            prefix + '_resp_p_reg_count': 0,
            prefix + '_resp_p_dyn_count': 0,
            prefix + '_dns_count_mean': 0,
            prefix + '_ssh_count_mean': 0,
            prefix + '_http_count_mean': 0,
            prefix + '_ssl_count_mean': 0,
            prefix + '_files_count_mean': 0,
            prefix + '_similar_conns_count': 0,
            prefix + '_similar_dns_qtype_count': 0,
            prefix + '_similar_dns_rcode_count': 0,
            prefix + '_similar_ssh_auth_attempts_count': 0,
            prefix + '_similar_ssh_host_key_count': 0,
            prefix + '_similar_http_method_count': 0,
            prefix + '_similar_http_status_code_count': 0,
            prefix + '_similar_http_user_agent_count': 0,
            prefix + '_similar_ssl_version_count': 0,
            prefix + '_similar_ssl_cipher_count': 0,
            prefix + '_similar_ssl_curve_count': 0,
            prefix + '_similar_ssl_validation_status_count': 0,
            prefix + '_similar_files_source_count': 0,
            prefix + '_similar_file_md5_count': 0
           }
NEIGHBOURHOOD_TIME_WINDOW_MINUTES_ORIG_DIRECTION = 5
NEIGHBOURHOOD_TIME_WINDOW_MINUTES_RESP_DIRECTION = 2

def compute_neighbourhoods(cur_orig_ip, dfs_list_orig, dfs_list_resp):
    df_result = pd.DataFrame()
    print('[{}]: Computing neighbourhood for connections of originator {:15} ({})'.format(datetime.now().strftime("%H:%M:%S"), cur_orig_ip, str(len(dfs_list_orig[cur_orig_ip]))))
    # iterate over rows in originated connections df of host with cur_orig_ip IP address:
    for index, row in dfs_list_orig[cur_orig_ip].iterrows():
        cur_row_dict = row.to_dict()
        cur_time = row['connection.time']
        
        time_start_orig = cur_time - pd.Timedelta(minutes=NEIGHBOURHOOD_TIME_WINDOW_MINUTES_ORIG_DIRECTION)
        time_end_orig = cur_time + pd.Timedelta(minutes=NEIGHBOURHOOD_TIME_WINDOW_MINUTES_ORIG_DIRECTION)
        time_start_resp = cur_time - pd.Timedelta(minutes=NEIGHBOURHOOD_TIME_WINDOW_MINUTES_RESP_DIRECTION)
        time_end_resp = cur_time + pd.Timedelta(minutes=NEIGHBOURHOOD_TIME_WINDOW_MINUTES_RESP_DIRECTION)
        ip_responder = row['responded_ip']
        try:
            # compute neighbourhoods (from originated connections for originator, from responded connections for responder):
            originator_neighbourhood = compute_time_neighbourhood(cur_orig_ip, dfs_list_orig, 'connection.time', cur_time, time_start_orig, time_end_orig, row, 'orig_orig')
            originator_neighbourhood2 = compute_time_neighbourhood(cur_orig_ip, dfs_list_resp, 'connection.time', cur_time, time_start_resp, time_end_resp, row, 'orig_resp')
            responder_neighbourhood = compute_time_neighbourhood(ip_responder, dfs_list_orig, 'connection.time', cur_time, time_start_orig, time_end_orig, row, 'resp_orig')
            responder_neighbourhood2 = compute_time_neighbourhood(ip_responder, dfs_list_resp, 'connection.time', cur_time, time_start_resp, time_end_resp, row, 'resp_resp')

            cur_row_dict.update(originator_neighbourhood)
            cur_row_dict.update(originator_neighbourhood2)
            cur_row_dict.update(responder_neighbourhood)
            cur_row_dict.update(responder_neighbourhood2)
            
            # concat to one long row and to df_result:
            row_df = pd.DataFrame([cur_row_dict])
            df_result = pd.concat([df_result, row_df], axis=0, ignore_index=True)
        except: 
            print('Problem with originator {} and responder {} ({})'.format(cur_orig_ip, ip_responder, row['connection.uid']))
            pass
    return df_result
# small input for development purposes:
smaller_dfs_orig = {}
smaller_dfs_resp = {}

# smaller_dfs_orig['172.16.58.3'] = dfs_day2_orig['172.16.58.3'].loc[dfs_day2_orig['172.16.58.3']['connection.uid'] == 'CNXOFP2e8fzmzjEjP6']
# # smaller_dfs_orig['10.2.4.44'] = dfs_day2_orig['10.2.4.44'].sample(20)
# smaller_dfs_resp['172.16.58.3'] = dfs_day2_resp['172.16.58.3']
# smaller_dfs_resp['10.5.5.2'] = dfs_day2_resp['10.5.5.2']

smaller_dfs_orig['172.16.58.3'] = dfs_day1_orig['172.16.58.3'].loc[dfs_day1_orig['172.16.58.3']['connection.uid'] == 'CRVwBM2CVD3ZPKvGF6']
smaller_dfs_resp['172.16.58.3'] = dfs_day1_resp['172.16.58.3']
smaller_dfs_resp['10.5.5.2'] = dfs_day1_resp['10.5.5.2']
from datetime import datetime
import multiprocessing
from multiprocessing import Pool
from functools import partial
from contextlib import contextmanager

@contextmanager
def poolcontext(*args, **kwargs):
    pool = multiprocessing.Pool(*args, **kwargs)
    yield pool
    pool.terminate()

# compute neighbourhoods using multiple threads (time optimalization):
print('Start at ' + datetime.now().strftime("%H:%M:%S") + '.')
with poolcontext(processes=32) as pool:
    ### for development purposes:
#     dfs_with_neighbourhoods = pool.map(partial(compute_neighbourhoods, 
#                                                dfs_list_orig=smaller_dfs_orig, 
#                                                dfs_list_resp=smaller_dfs_resp), 
#                                        smaller_dfs_orig.keys())
    ###
    
    dfs_with_neighbourhoods_day1 = pool.map(
        partial(compute_neighbourhoods, 
                dfs_list_orig=dfs_day1_orig, 
                dfs_list_resp=dfs_day1_resp), 
        dfs_day1_orig.keys())
    
    dfs_with_neighbourhoods_day2 = pool.map(
        partial(compute_neighbourhoods, 
                dfs_list_orig=dfs_day2_orig, 
                dfs_list_resp=dfs_day2_resp), 
        dfs_day2_orig.keys())

print('Done at ' + datetime.now().strftime("%H:%M:%S") + '.')
# pd.set_option('display.max_columns', None)
# dfs_with_neighbourhoods[0].head()
# for col in dfs_with_neighbourhoods[0].columns:
#     print(col)
# print(type(dfs_with_neighbourhoods))
print(type(dfs_with_neighbourhoods_day1))
print(type(dfs_with_neighbourhoods_day2))
# print(len(dfs_with_neighbourhoods))
print(len(dfs_with_neighbourhoods_day1))
print(len(dfs_with_neighbourhoods_day2))
dfs_with_neighbourhoods_day1[0].head()<jupyter_output><empty_output><jupyter_text>3. Concat<jupyter_code>def concat_dfs(df_neighourhoods):
    df_result = pd.DataFrame()
    for i in range(0, len(df_neighourhoods)):
        df_i = df_neighourhoods[i]
        df_result = df_result.append(df_i)
        # print('Appending ' + str(i) + ', len = ' + str(len(df_i)) + ', df_result len = ' + str(len(df_result)))
    return df_result

df_result_day1 = concat_dfs(dfs_with_neighbourhoods_day1)
df_result_day2 = concat_dfs(dfs_with_neighbourhoods_day2)
df_result_day1
df_result_day2
df_result_day2<jupyter_output><empty_output><jupyter_text>Assign attacker labels<jupyter_code>df_result_day1['attacker_label'] = 'No'
df_result_day2['attacker_label'] = 'No'
# Red Team CIDR ranges:
ATTACKER_IPS = ["192.168.127.12/24", 
                "1.9.0.0/16", "5.23.128.0/17", "172.16.58.3/20", "172.16.58.3/19", "172.16.58.3/20", "172.16.31.10/16", "192.168.3.11/19",
                "172.16.31.10/20", "192.168.3.11/16", "172.16.31.10/16", "172.16.17.32/20", "172.16.17.32/20", "192.168.3.11/20", "172.16.17.32/19", 
                "192.168.127.12/20", "172.16.58.3/16", "192.168.3.11/16", "192.168.3.11/19", "172.16.31.10/20", "172.16.31.10/16", "172.16.31.10/19", 
                "172.16.17.32/20", "192.168.127.12/19", "172.16.17.32/19", "192.168.127.12/19", "192.168.3.11/21", "192.168.3.11/20", "192.168.3.11/20"]

# from netaddr import IPNetwork, IPAddress
import ipaddress

ips_cache = {} # optimalizatiion

def is_attacker(ip_address):
  for attacker_ip in ATTACKER_IPS:
    #if IPAddress(ip_address) in IPNetwork(attacker_ip):
    if ip_address in ips_cache:
      return ips_cache[ip_address]
    try:
      if ipaddress.ip_address(ip_address) in ipaddress.ip_network(attacker_ip):
        #print("IP address " + ip_address + " is from Red team (" + attacker_ip + ").")
        ips_cache[ip_address] = True
        return True
    except:
      pass # IPv6
  ips_cache[ip_address] = False
  return False
# assign labels to input data ('No' not from/ to attacker, 'Yes' originated from/ responded to attacker):
df_result_day2.loc[df_result_day2['responded_ip'].apply(is_attacker),'attacker_label'] = 'Yes'
df_result_day2.loc[df_result_day2['originated_ip'].apply(is_attacker),'attacker_label'] = 'Yes'
df_result_day2<jupyter_output><empty_output><jupyter_text>Assign attack labels<jupyter_code>df_result_day1['attack_label'] = 'Normal'
df_result_day2['attack_label'] = 'Normal'
print(df_result_day2['connection.time'].min())
print(df_result_day2['connection.time'].max())
import csv
import socket

LABEL_TIME_WINDOW_SECONDS = 4

CSV_LABELS_FILE_PATH = '/home/sramkova/diploma_thesis_data/labels.csv'

# Snort alerts mapper:
label_mapper = {'SYN Scan': 'Scan',
                'POP3 SSLv3 invalid timestamp attempt': 'Pop3',
                'WEB-MISC Invalid HTTP Version String': 'Web',
                'COMMUNITY WEB-MISC mod_jrun overflow attempt': 'Web',
                'SNMP AgentX/tcp request': 'Snmp',
                'SNMP request tcp': 'Snmp',
                'SCAN UPnP service discover attempt': 'Scan',
                'LOCAL DOS SYN packet flood outbound': 'DoS',
                'LOCAL DOS SYN packet flood inbound': 'DoS',
                'WEB-MISC PCT Client_Hello overflow attempt': 'Web',
                'INFO web bug 0x0 gif attempt': 'Web',
                'POP3 PCT Client_Hello overflow attempt': 'Pop3',
                'ATTACK-RESPONSES 403 Forbidden': 'Web',
                'MISC MS Terminal server request': 'Else',
                'SCAN nmap XMAS': 'Scan',
                'VIRUS OUTBOUND bad file attachment': 'Virus',
                'DNS named version attempt': 'Web',
                'MISC MS Terminal server request RDP': 'Else',
                'WEB-MISC Lotus Notes .exe script source download attempt': 'Web',
                'INFO TELNET login failed': 'Else',
                'WEB-MISC Chunked-Encoding transfer attempt': 'Web',
                'DNS zone transfer UDP': 'Web',
                'WEB-ATTACKS id command attempt': 'Web',
                'WEB-MISC SSLv2 Client_Hello with pad Challenge Length overflow attempt': 'Web',
                'POP3 PCT Client_Hello overflow attempt': 'Pop3',
                'WEB-MISC cat%20 access': 'Web',
                'WEB-MISC robots.txt access': 'Web',
                'MS-SQL probe response overflow attempt': 'Else',
                'WEB-ATTACKS netcat command attempt': 'Web',
                'WEB-MISC .htaccess access': 'Web',
                'WEB-MISC http directory traversal': 'Web'}

labels_list = []


with open(CSV_LABELS_FILE_PATH) as labels_file:
    csv_file = csv.reader(labels_file, delimiter=',')
    for splitted_csv_line in csv_file:
        label = label_mapper[splitted_csv_line[1].strip()] if splitted_csv_line[1].strip() in label_mapper else ''
        
        if label:
            time_start = pd.Timestamp(splitted_csv_line[0].replace('-', '/19 ')).tz_localize(None)
            time_start = time_start - pd.Timedelta(seconds=LABEL_TIME_WINDOW_SECONDS)
            time_end = pd.Timestamp(splitted_csv_line[0].replace('-', '/19 ')).tz_localize(None)
            time_end = time_end + pd.Timedelta(seconds=LABEL_TIME_WINDOW_SECONDS) #, milliseconds=30)
                
            res = (time_start, time_end, label,  splitted_csv_line[2].strip(), splitted_csv_line[3].strip())
            labels_list.append(res)

# sort based on starting time:
labels_list.sort(key=lambda x: x[0])
            
print(len(labels_list))
for i in range(10):
    print(labels_list[i])
labels_list[0]
def is_attack(attacker_label, time, orig_ip, resp_ip):
    if attacker_label == 'Yes':
        attack_labels = []
        
        for attack_start, attack_end, attack_label, ip_originator, ip_responder in labels_list:

            time_localized = time.tz_localize(None)
            
            # time optimalization in one direction (TODO)
            if attack_start > time_localized and attack_end > time_localized: 
                break

            if time_localized >= attack_start and time_localized <= attack_end:
                if attack_label not in attack_labels and orig_ip == ip_originator and resp_ip == ip_responder: 
                    attack_labels.append(attack_label)
        
        if attack_labels:
            final_label  = ''
            for label in attack_labels:
                if final_label:
                    final_label += ','
                final_label += label
            return final_label
        
        return 'Not_Specified'
        
    return 'Normal'
print('Start at ' + datetime.now().strftime("%H:%M:%S") + '.')
df_result_day2['attack_label'] = df_result_day2.apply(lambda row : is_attack(row['attacker_label'], 
                                                                             row['connection.time'],
                                                                             row['originated_ip'], 
                                                                             row['responded_ip']), 
                                                      axis = 1)
end_time = datetime.now().strftime("%H:%M:%S")
print('Done at ' + end_time + '.')
df_result_day2['attack_label'].value_counts()<jupyter_output><empty_output><jupyter_text>Concat to one DF and write to file<jupyter_code>df_result = df_result_day1.append(df_result_day2)
print(len(df_result_day1) + len(df_result_day2))
print(len(df_result))
df_result.to_csv('/home/sramkova/diploma_thesis_data/neighbourhood_both_days.csv', index=False, header=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11006279483895154, 'fraction_numerical': 0.02852322784216939, 'mean_word_length': 3.0163111668757843, 'pattern_counts': {'":': 0, '<': 48, '<?xml version=': 0, '>': 39, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 47, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 19, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '201702', 'license': 'MIT', 'n_tokens_mistral': 11094, 'n_tokens_neox': 10308, 'n_words': 2145, 'path': 'impl/jupyter_notebooks/cybeczech/query_output_preprocessing.ipynb', 'repo_name': 'roa7n/graph_network_traffic_ml', 'size': '31969'}