ThreatHuntingWithWebProxyLogs_NoOutput.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d91c07c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8ff85a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('/home/sansforensics/Desktop/Blucoat_Pandas/Demo_log_004.log',engine='python', sep=' ',error_bad_lines=False)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cca161a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9efd95c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45a8e77b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1) Uncategorized sites -> Depending on proxy uncategorized site can have diff name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce655f26",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['sc-filter-category'] == \"unavailable\"]['cs-host'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab27ac57",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['sc-filter-category'] == \"unavailable\"]['c-ip'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c895898",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2) Hits toward malicious sites and count ( categories ) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "806197b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['sc-filter-category'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ba2007a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df [ df['sc-filter-category'].str.contains('mal|Mal|bot|spy',case=False) == True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fabba9dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df [ df['sc-filter-category'].str.contains('mal|Mal|bot|spy',case=False) == True]['cs-host'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4942f19f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df [ df['sc-filter-category'].str.contains('mal|Mal|bot|spy',case=False) == True]['cs-host'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "309cc0a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df [ df['sc-filter-category'].str.contains('mal|Mal|bot|spy',case=False) == True]['cs-host'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12b3142a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3) IP based communication. Take this data and check reputation of ip's for further analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28718e3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df [ df['cs-host'].str.contains(r'\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}') == True ]['cs-host'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b345424",
   "metadata": {},
   "outputs": [],
   "source": [
    "df [df['cs-host'].str.contains(r'\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}') == True ][['cs-host','sc-status']]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d221a70",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 4)  HTTP POST / PUT method usage......"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16f9d88d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df [(df['cs-method'] == 'POST') | (df['cs-method'] == 'PUT')][['c-ip','cs-host','cs-bytes']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f9d69ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 5) sites where max data is uploaded"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c19e2b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = df.groupby('cs-host').sum().sort_values('cs-bytes',ascending=False) \n",
    "temp [ test > 1000000 ]['cs-bytes'].sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f1aa199",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 6) Playing with Content Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "971d983c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of content \n",
    "df['rs(Content-Type)'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9eff8083",
   "metadata": {},
   "outputs": [],
   "source": [
    "# POST method uses application/x-www-urlform-encoded to upload data\n",
    "df [ ( df['rs(Content-Type)'] == 'application/x-www-urlform-encoded' ) == True ]['cs-host'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02953aa9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# when executables are downloaded this content type is seen\n",
    "df [ (df['rs(Content-Type)'] == 'application/octate-stream') == True ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a07b642",
   "metadata": {},
   "outputs": [],
   "source": [
    "#images with malicious content\n",
    "df[ (df['cs-uri-path'].str.contains('png|PNG') ) & ( df['rs(Content-Type)'] == 'application/octate-stream') ]['rs(Content-Type)']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9050ab68",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 7)  User Agent string checks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4dd21dcc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# hunitng for powershell | python | curl | office  User agent string\n",
    "df [df['cs(User-Agent)'].str.contains('pow|office|python|curl', case= False) == True]['cs(User-Agent)'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c98099b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# less occuring UA\n",
    "temp = df['cs(User-Agent)'].value_counts() < 5\n",
    "temp.tail(10).index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90331674",
   "metadata": {},
   "outputs": [],
   "source": [
    "# print UA with values\n",
    "temp = df['cs(User-Agent)'].value_counts()[df['cs(User-Agent)'].value_counts() < 5 ]\n",
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "190d58d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8)  Quick check with whois data - To check recently registered domain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db47e522",
   "metadata": {},
   "outputs": [],
   "source": [
    "import whois\n",
    "temp = df [ df ['cs(User-Agent)'] == 'Windows-Update-Agent']['cs-host'].unique()\n",
    "\n",
    "for i in temp:\n",
    "    c_date =  str ( whois.whois(i).creation_date )\n",
    "    print(i + \"\\t\" + c_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fd62ed9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 9) Adding column based on certain criteria - In this data set, there were no Usernames so I just added with below conditions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52e44ab3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "conditions = [\n",
    "    (df['sc-bytes'] <= 500),\n",
    "    (df['sc-bytes'] > 500) & (df['sc-bytes'] <= 1000),\n",
    "    (df['sc-bytes'] > 1000) & (df['sc-bytes'] <= 1500),\n",
    "    (df['sc-bytes'] > 1500)\n",
    "    ]\n",
    "\n",
    "# create a list of the values we want to assign for each condition\n",
    "values = ['sanket', 'suraj', 'parth', 'jay']\n",
    "\n",
    "# create a new column and use np.select to assign values to it using our lists as arguments\n",
    "df['new_cs_user'] = np.select(conditions, values)\n",
    "\n",
    "# display updated DataFrame\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84de86db",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 10 )  User uploading data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d84c281e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# adding  new column with data in Kb\n",
    "df['cs-bytes-in-Kb'] = df['cs-bytes'].apply(lambda x : x/1000)\n",
    "df['cs-bytes-in-Kb'].sort_values(ascending=False)\n",
    "\n",
    "# POST method check\n",
    "df.groupby('cs-host').sum()['cs-bytes-in-Kb']\n",
    "temp = df[df['cs-method'] == \"POST\"]\n",
    "for u in temp.groupby('new_cs_user').groups.keys(): # to iterate users\n",
    "    site = temp [ temp['new_cs_user'] == u]         # create dataframe based on user   \n",
    "    site_grp = site.groupby('cs-host').sum()['cs-bytes-in-Kb']  # groupby website name and sum the upload data\n",
    "    print(\"user uploading is \", u)\n",
    "    print ( site_grp [ site_grp > 1000 ] ) # show more than 1000 Kb data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbd46f6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Thank You"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}