New

IT Threat Detection, Part 2

Notebook

SingleStore Notebooks

IT Threat Detection, Part 2

Note

This tutorial is meant for Standard & Premium Workspaces. You can't run this with a Free Starter Workspace due to restrictions on Storage. Create a Workspace using +group in the left nav & select Standard for this notebook. Gallery notebooks tagged with "Starter" are suitable to run on a Free Starter Workspace

Install Dependencies

In [1]:

!pip3 install tensorflow keras==2.15.0 scikit-learn --quiet

In [2]:

import os
os.environ['TF_CPP_MIN_LOG_LEVEL'] = '3'

import pandas as pd
import tensorflow.keras.backend as K
from collections import Counter
from sklearn.metrics import accuracy_score, precision_score, recall_score
from sklearn.metrics import confusion_matrix
from tensorflow import keras
from tensorflow.keras.models import Model

We'll define a Python context manager called clear_memory() using the contextlib module. This context manager will be used to clear memory by running Python's garbage collector (gc.collect()) after a block of code is executed.

In [3]:

import contextlib
import gc

@contextlib.contextmanager
def clear_memory():
    try:
        yield
    finally:
        gc.collect()

Load Model

In [4]:

with clear_memory():
    model = keras.models.load_model('it_threat_model')

model.summary()

In [5]:

with clear_memory():
    # Select the first layer
    layer_name = 'dense'
    intermediate_layer_model = Model(
        inputs = model.input,
        outputs = model.get_layer(layer_name).output
    )

Data Preparation

We'll use the second file we downloaded earlier for testing purposes.

Review Data

In [6]:

with clear_memory():
    data = pd.read_csv('Thursday-22-02-2018_TrafficForML_CICFlowMeter.csv')

data.Label.value_counts()

Clean Data

We'll run a cleanup script from the previously downloaded GitHub repo.

In [7]:

!python DeepLearning-IDS/data_cleanup.py "Thursday-22-02-2018_TrafficForML_CICFlowMeter.csv" "result22022018"

We'll now review the cleaned data from the previous step.

In [8]:

with clear_memory():
    data_22_cleaned = pd.read_csv('result22022018.csv')

data_22_cleaned.head()

In [9]:

data_22_cleaned.Label.value_counts()

We'll create a sample that encompasses all the distinct types of web attacks observed on this particular date.

In [10]:

with clear_memory():
    data_sample = data_22_cleaned[-2000:]

data_sample.Label.value_counts()

Get Connection Details

Action Required

Select the database from the drop-down menu at the top of this notebook. It updates the connection_url which is used by SQLAlchemy to make connections to the selected database.

In [11]:

from sqlalchemy import *

db_connection = create_engine(connection_url)

Queries

Next, we'll perform queries on the test dataset and store the predicted and expected results, enabling us to construct a confusion matrix.

In [12]:

from tqdm import tqdm
import numpy as np

y_true = []
y_pred = []

BATCH_SIZE = 100

for i in tqdm(range(0, len(data_sample), BATCH_SIZE)):
    test_data = data_sample.iloc[i:i+BATCH_SIZE, :]

    # Create vector embedding using the model
    test_vector = intermediate_layer_model.predict(K.constant(test_data.iloc[:, :-1]))
    query_results = []

    for xq in test_vector.tolist():
        # SQL query here, make sure it returns 'id' column
        query_res = %sql SELECT id, EUCLIDEAN_DISTANCE(Model_Results, JSON_ARRAY_PACK('{{xq}}')) AS score FROM model_results WHERE score IS NOT NULL ORDER BY score ASC LIMIT 50;
        query_results.append(pd.DataFrame(query_res))

    for label, res in zip(test_data.Label.values, query_results):

        if 'id' not in res.columns:
            print("Column 'id' not found in res.")
            continue

        if label == 'Benign':
            y_true.append(0)
        else:
            y_true.append(1)

        ids_to_count = [id.split('_')[0] for id in res['id']]
        counter = Counter(ids_to_count)
        # print(counter)

        if counter.get('Bru') or counter.get('SQL'):
            y_pred.append(1)
        else:
            y_pred.append(0)

Visualize Results

Confusion Matrix

In [13]:

import plotly.graph_objs as go

# Calculate the confusion matrix
conf_matrix = confusion_matrix(y_true, y_pred)

# Create a DataFrame from the confusion matrix
conf_matrix_df = pd.DataFrame(
    conf_matrix,
    columns = ['Benign', 'Attack'],
    index = ['Benign', 'Attack']
)

# Create an empty list to store annotations
annotations = []

# Define a threshold for text color
thresh = conf_matrix_df.values.max() / 2

# Loop through the confusion matrix and add annotations with text color based on the threshold
for i in range(len(conf_matrix_df)):
    for j in range(len(conf_matrix_df)):
        value = conf_matrix_df.iloc[i, j]
        text_color = "white" if value > thresh else "black"
        annotations.append(
            go.layout.Annotation(
                x = j,
                y = i,
                text = str(value),
                font = dict(color = text_color),
                showarrow = False,
            )
        )

# Create a heatmap trace with showscale set to False
trace = go.Heatmap(
    z = conf_matrix_df.values,
    x = ['Benign', 'Attack'],
    y = ['Benign', 'Attack'],
    colorscale = 'Reds',
    showscale = False
)

# Create the figure with heatmap and annotations
fig = go.Figure(
    data = [trace],
    layout = {
        "title": "Confusion Matrix",
        "xaxis": {"title": "Predicted", "scaleanchor": "y", "scaleratio": 1},
        "yaxis": {"title": "Actual"},
        "annotations": annotations,
        "height": 400,
        "width": 400
    }
)

fig.show()

In [14]:

# Create confusion matrix
conf_matrix = confusion_matrix(y_true, y_pred)

# Define class labels
class_labels = ['Benign', 'Attack']

# Print confusion matrix with labels
print("Confusion Matrix:")
for i in range(len(class_labels)):
    for j in range(len(class_labels)):
        print(f"{class_labels[i]} (Actual) -> {class_labels[j]} (Predicted): {conf_matrix[i][j]}")

Accuracy

In [15]:

# Calculate accuracy
acc = accuracy_score(y_true, y_pred, normalize = True, sample_weight = None)
precision = precision_score(y_true, y_pred)
recall = recall_score(y_true, y_pred)

print(f"Accuracy: {acc:.3f}")
print(f"Precision: {precision:.3f}")
print(f"Recall: {recall:.3f}")

Per Class Accuracy

In [16]:

# Calculate per class accuracy
cmd = confusion_matrix(y_true, y_pred, normalize = "true").diagonal()
per_class_accuracy_df = pd.DataFrame([(index, round(value,4)) for index, value in zip(['Benign', 'Attack'], cmd)], columns = ['type', 'accuracy'])
per_class_accuracy_df = per_class_accuracy_df.round(2)
display(per_class_accuracy_df)

Predict Values Directly from Model

We achieved excellent results with SingleStoreDB. Now, let's explore what happens when we bypass the similarity search step and make predictions directly from the model. In other words, we'll utilize the model responsible for generating the embeddings as a classifier. We can then compare the accuracy of this approach with that of the similarity search method.

In [17]:

from tensorflow.keras.utils import normalize
import numpy as np

data_sample = normalize(data_22_cleaned.iloc[:, :-1])[-2000:]
y_pred_model = model.predict(normalize(data_sample)).flatten()
y_pred_model = np.round(y_pred_model)

Visualize Results

Confusion Matrix

In [18]:

# Create confusion matrix
conf_matrix = confusion_matrix(y_true, y_pred_model)

# Create a DataFrame from the confusion matrix
conf_matrix_df = pd.DataFrame(
    conf_matrix,
    columns = ['Benign', 'Attack'],
    index = ['Benign', 'Attack']
)

# Create an empty list to store annotations
annotations = []

# Define a threshold for text color
thresh = conf_matrix_df.values.max() / 2

# Loop through the confusion matrix and add annotations with text color based on the threshold
for i in range(len(conf_matrix_df)):
    for j in range(len(conf_matrix_df)):
        value = conf_matrix_df.iloc[i, j]
        text_color = "white" if value > thresh else "black"
        annotations.append(
            go.layout.Annotation(
                x = j,
                y = i,
                text = str(value),
                font = dict(color=text_color),
                showarrow = False,
            )
        )

# Create a heatmap trace with showscale set to False
trace = go.Heatmap(
    z = conf_matrix_df.values,
    x = ['Benign', 'Attack'],
    y = ['Benign', 'Attack'],
    colorscale = 'Reds',
    showscale = False
)

# Create the figure with heatmap and annotations
fig = go.Figure(
    data = [trace],
    layout = {
        "title": "Confusion Matrix",
        "xaxis": {"title": "Predicted", "scaleanchor": "y", "scaleratio": 1},
        "yaxis": {"title": "Actual"},
        "annotations": annotations,
        "height": 400,
        "width": 400
    }
)

fig.show()

In [19]:

# Create confusion matrix
conf_matrix = confusion_matrix(y_true, y_pred_model)

# Define class labels
class_labels = ['Benign', 'Attack']

# Print confusion matrix with labels
print("Confusion Matrix:")
for i in range(len(class_labels)):
    for j in range(len(class_labels)):
        print(f"{class_labels[i]} (Actual) -> {class_labels[j]} (Predicted): {conf_matrix[i][j]}")

Accuracy

In [20]:

# Calculate accuracy
acc = accuracy_score(y_true, y_pred_model, normalize = True, sample_weight = None)
precision = precision_score(y_true, y_pred_model)
recall = recall_score(y_true, y_pred_model)

print(f"Accuracy: {acc:.3f}")
print(f"Precision: {precision:.3f}")
print(f"Recall: {recall:.3f}")

Per Class Accuracy

In [21]:

# Calculate per class accuracy
cmd = confusion_matrix(y_true, y_pred_model, normalize = "true").diagonal()
per_class_accuracy_df = pd.DataFrame([(index, round(value,4)) for index, value in zip(['Benign', 'Attack'], cmd)], columns = ['type', 'accuracy'])
per_class_accuracy_df = per_class_accuracy_df.round(2)
display(per_class_accuracy_df)

Conclusions

Utilizing SingleStoreDB's vector embeddings, we achieved an extremely high detection rate for attacks while maintaining a very small false-positive rate. Furthermore, our example showed that our similarity search methodology surpassed the direct classification approach that relies on the classifier's embedding model.

Details

License

This Notebook has been released under the Apache 2.0 open source license.

IT Threat Detection, Part 2

Notebook

IT Threat Detection, Part 2

Install Dependencies

Load Model

Data Preparation

Review Data

Clean Data

Get Connection Details

Queries

Visualize Results

Confusion Matrix

Accuracy

Per Class Accuracy

Predict Values Directly from Model

Visualize Results

Confusion Matrix

Accuracy

Per Class Accuracy

Conclusions

Details

Tags

License