<jupyter_start><jupyter_text>Producer/Consumer Ratio of DNS PreambleWithin an enterprise network, all network nodes are producer and consumers of data in someway or another.  At an atomic level, a network node will have a typical "behaviour profile" related to its consumption/production known as the Producer/Consumer Ratio (PCR).  Mathematically, PCR can be expressed as:$$ PCR = \frac{SrcAppBytes - DstAppBytes}{SrcAppBytes + DstAppBytes} $$and expresses whether a device can be classified as more of a producer or a consumer.  A network node that achieves a PCR of 1.0 tends to be purely pushing data or conversely a PCR of -1.0 represents behaviours related to consuming.Although PCR can be applied to to all network connections from a node, it is often better to perform analysis on particular protocols inorder to isolate the signal to noise ratio further.  One popular target protocol is DNS due to its preveleance as a exfiltration technique and smaller domain of behaviours (when compared to something wild like HTTP).   Required Data:Primary data required for this technique are connection logs which may come in various forms such as NetFlow, Bro Logs, Argus, etc. Hunt***Hunt Hypothesis: ***  Groups of users within the same business unit will generate similar DNS traffic.  Applying the Producer Consumer Ratio to each of these hosts should reveal similar values.  ScenarioYou have collected the Bro conn.logs from the Accounting VLAN for PCR Analysis. Data Schema| Field          | Desc                                                 | Type     | |----------------|------------------------------------------------------|----------|| ts             | Timestamp of connection                              | datetime || uid            | Unique ID of connection                              | string   || id.orig_h      | Originating connection's host IP address             | string   || id.orig_p      | Originating connection's host port number            | integer  || id.resp_h      | Responding connection's host IP address              | string   || id.resp_p      | Responding connection's host port number             | integer  || proto          | Transport layer protocol of connection               | string   || service        | Dynamically detected application protocol, if any    | string   || duration       | Length of time of the connection                     | float    || orig_bytes     | Originators payload bytes                            | integer  || resp_bytes     | Responder payload bytes                              | integer  || conn_state     | Connection state                                     | string   || local_orig     | If conn originated locally; T, if remotely F         | boolean  || missed_bytes   | Number of missi9ng bytes in content gaps             | integer  || history        | Connection state history                             | string   || orig_pkts      | Number of orig packets                               | integer  || orig_ip_bytes  | Number of Orig IP bytes                              | integer  || resp_pkts      | Number of responding packets                         | integer  || resp_ip_bytes  | Number of Resp IP bytes                              | integer  || tunnel_parents | If tunneled, connection UID of encapsulating parents | string   |<jupyter_code>import os

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>Data IngestionGiven that the Bro conn.logs reside in a flat file structure, and with there only exist approximately 2,000 logs we will write two functions to convert all files within the "./dataset/a/*/conn.log" into one Pandas DataFrame.  Should this data exist within a Database or Hadoop DataLake, a connector or PySpark may be useful.<jupyter_code>def parseBro(file):
    """This function converts a given conn.log into a Pandas DataFrame. 
    """
    
    df = pd.read_csv(file, delimiter='\t', skiprows=[0,1,2,3,4,5,6], skipfooter=1, engine='python')
     
    df.columns = ['ts','uid','id.orig_h','id.orig_p','id.resp_h','id.resp_p','proto','service','duration','orig_bytes','resp_bytes','conn_state',
                  'local_orig','missed_bytes','history','orig_pkts','orig_ip_bytes','resp_pkts','resp_ip_bytes','tunnel_parents', 'delete']
    
    # Typecast the 'ts' column to be a Python datetime
    df['ts'] = pd.to_datetime(df['ts'],unit='s')
    
    return df

def generateDF(files):
    """Generates a single DataFrame from a collection of conn.logs
    """
    
    # Initialise an empty DataFrame
    df = pd.DataFrame()
    
    for file in files:
        df = pd.concat([df, parseBro('./dataset/a/' + str(file) + '/conn.log')])
        
    return df

directoryList = os.listdir(path='./dataset/a/')[1:200]
df = generateDF(directoryList)

display(df.head())<jupyter_output><empty_output><jupyter_text>Explore Data ScopeAs a preventitative measure - let's check that the database is correctly made.<jupyter_code>display(df['id.orig_h'].unique())

df = df[df['id.orig_h'].str.contains('192.168.', regex=False)]
display(df['id.orig_h'].unique())

display(df['service'].unique())
df = df[df['id.resp_p'] == 53]
display(df['service'].unique())

display(df.head())<jupyter_output><empty_output><jupyter_text>Calculating PCRGiven that the data was generated within the same bucket of time, we will sum each of the byte counts.  This way we are able to average behaviour out over the given bucket of time.<jupyter_code>df = df.groupby('id.orig_h').sum()

df['PCR'] = (df['orig_ip_bytes'] - df['resp_ip_bytes'])/(df['orig_ip_bytes'] + df['resp_ip_bytes'])

df.head()<jupyter_output><empty_output><jupyter_text>Plotting TimeseriesPlots the timeseries vs PCR values to visually inspect for trends.<jupyter_code>df.plot.bar(y='PCR', figsize=(15,10), title="Producer/Consumer Ratio across Account VLAN IPs")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10333164214442753, 'fraction_numerical': 0.005580923389142567, 'mean_word_length': 2.810567010309278, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '178598', 'license': 'Apache-2.0', 'n_tokens_mistral': 1598, 'n_tokens_neox': 1520, 'n_words': 573, 'path': 'DNS/pcr.ipynb', 'repo_name': 'pjabes/threat-hunting-notebooks', 'size': '5913'}