Crwd-GetDetectionSummary-Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "603e9d77",
   "metadata": {},
   "source": [
    "# Getting Detections using Falconpy and Pandas\n",
    "\n",
    "## Main Goal: \n",
    "\n",
    "`Gathering details from detections directly from the console via API and pivot them for a better visualization`\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ddc52a89",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing libraries\n",
    "# PS: Not all packages are being used but is good to have them to handle data if necessary\n",
    "from   falconpy import Detects\n",
    "from   dateutil   import parser\n",
    "import pytz\n",
    "import csv\n",
    "import os as system\n",
    "import configparser\n",
    "import json\n",
    "import pandas as pd\n",
    "\n",
    "# Making pandas visualizarion results more attractive\n",
    "pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.width', None)\n",
    "pd.set_option('max_colwidth', 5000)\n",
    "\n",
    "# Getting id and secret stored in the hd\n",
    "# Do not hardcode API credentials!\n",
    "CONFIGFILE = '/home/bruce/PROJECTS/JUPYTER/TOKENS/am_key.ini'\n",
    "cfg = configparser.ConfigParser()\n",
    "cfg.read(CONFIGFILE)\n",
    "\n",
    "# Setting up credentials variables\n",
    "CLIENT_ID     = cfg.get('CLIENT_ID', 'CLIENT_ID')\n",
    "CLIENT_SECRET = cfg.get('CLIENT_SECRET', 'CLIENT_SECRET')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b93473a9",
   "metadata": {},
   "source": [
    "## Part 1 - Getting a raw list of detection ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "73c89f64",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "98\n"
     ]
    }
   ],
   "source": [
    "# Instantiate Detects class\n",
    "falcon = Detects(client_id=CLIENT_ID,                            \n",
    "                 client_secret=CLIENT_SECRET,                 \n",
    "                 )\n",
    "\n",
    "# Getting the list id of the detections\n",
    "# Here is a simple filter using date/time of detections\n",
    "# For more informarion about using filters and FQL go to the documentation page described in README.md\n",
    "\n",
    "response = falcon.query_detects(\n",
    "    limit=9999,                            \n",
    "    filter=\"last_behavior:>='2024-01-01'+last_behavior:<='2024-01-03'+max_severity_displayname:!'Informational'\",\n",
    "    sort=\"last_behavior\"\n",
    "    )\n",
    "\n",
    "# Checking answer from the API before start\n",
    "if response['status_code'] == 200:    \n",
    "    id_detections = response['body']['resources']    \n",
    "    print(len(id_detections))\n",
    "    \n",
    "else:\n",
    "    print(response['body']['errors'])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b953dacb",
   "metadata": {},
   "source": [
    "## Part 2 - Gathering detections details\n",
    "\n",
    "Here I'm using a tricky code to handle over 1k of detections details from [GetDetectSummaries](https://www.falconpy.io/Service-Collections/Detects.html#getdetectsummaries) since this endpoint doesn't have offset and limit parameters\n",
    "\n",
    "`PS1: I'm working on a better solucion using` [concurrent.futures](https://docs.python.org/3/library/concurrent.futures.html)\n",
    "\n",
    "`PS2: This is only needed if you have more than 1k of detections`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a300ff20",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Making indexes for receive 1k detections details\n",
    "indexes = [1000,2000,3000,4000,5000,6000,7000,8000,9000,9999]\n",
    "output = []\n",
    "prev = 0\n",
    "\n",
    "# Slicing the id_detections and parts of 1k\n",
    "for index in indexes:\n",
    "    output.append(id_detections[prev:index])\n",
    "    prev = index\n",
    "\n",
    "# Gathering detection details by 1k\n",
    "# If you have less detections than 9999 you can make a smaller list of lists\n",
    "count = -1\n",
    "full_list = []\n",
    "while count < len(indexes[:-1]):\n",
    "    count += 1\n",
    "    detects       = falcon.get_detect_summaries(ids=output[count], sort=\"last_behavior\") \n",
    "    detail_list   = detects['body']['resources']\n",
    "    full_list.append(detail_list)  \n",
    "    \n",
    "# PS: This will create 10 lists of 1k records for each one. \n",
    "len(full_list)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2e152a4",
   "metadata": {},
   "source": [
    "## Part 3: Making a big list with all small lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "80357893",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "98"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_list = []\n",
    "\n",
    "for i in range(len(full_list)):\n",
    "    final_list += full_list[i]  \n",
    "len(final_list)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2e8ea1a",
   "metadata": {},
   "source": [
    "## Part 4: Creating a dataframe with the data collected\n",
    "\n",
    "\n",
    "You can choose whatever fields you want to compose your dataframe, just check the documentation to get what fields are available\n",
    "\n",
    "`PS: About action taken (pattern_disposition) I'm using the most common, also considering that the best practices in policy configuration is already applied in you environment`\n",
    " \n",
    "I encourage you the read more in [EventsDataDictionary](https://falcon.crowdstrike.com/documentation/page/e3ce0b24/events-data-dictionary) to have a more wide comprehension of the detections endpoint and how to use it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7e268126",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "98"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adjust datetime fields for your country since the API uses UTC as base timestamp \n",
    "\n",
    "count_detect = -1\n",
    "for detection in final_list:\n",
    "    count_detect += 1\n",
    "    # Grab the \"last_behavior\" field\n",
    "    detect_last_behavior = parser.parse(detection[\"last_behavior\"])\n",
    "    \n",
    "    # Create a timestamp in our local timezone\n",
    "    local_timezone = pytz.timezone(\"Brazil/East\")\n",
    "    local_datetime = detect_last_behavior.replace(tzinfo=pytz.utc)\n",
    "    # Convert the UTC date time to a more aware local\n",
    "    local_datetime = local_datetime.astimezone(local_timezone)\n",
    "    # Output our debug results    \n",
    "    final_list[count_detect]['last_behavior'] = str(local_datetime)[:-6]\n",
    "    \n",
    "\n",
    "# Creating the dataframe\n",
    "df = pd.DataFrame(columns=['SEVERITY', 'HOSTNAME', 'TYPE', 'OS', 'LAST_DETECTION', \n",
    "                           'USERNAME', 'FILENAME', 'CMD', 'IOC', 'ACTION', 'DESCRIPTION'])\n",
    "\n",
    "# Populating the dataframe\n",
    "for i in range(len(final_list)):\n",
    "    severity       = final_list[i]['max_severity_displayname']\n",
    "    hostname       = final_list[i]['device']['hostname']\n",
    "    host_type      = final_list[i]['device']['product_type_desc']\n",
    "    os             = final_list[i]['device']['os_version']\n",
    "    last_behavior  = final_list[i]['last_behavior']\n",
    "    user           = final_list[i]['behaviors'][0]['user_name']    \n",
    "    filename       = final_list[i]['behaviors'][0]['filename']\n",
    "    cmdline        = final_list[i]['behaviors'][0]['cmdline']\n",
    "    ioc            = final_list[i]['behaviors'][0]['ioc_description']\n",
    "    ptd            = final_list[i]['behaviors'][0]['pattern_disposition']\n",
    "    description    = final_list[i]['behaviors'][0]['description']\n",
    "    \n",
    "    if ptd     == 0:\n",
    "        action  = 'Detection Only'\n",
    "    elif ptd   == 16:\n",
    "        action  = 'Process Killed'\n",
    "    elif ptd   == 128:\n",
    "        action  = 'File Quarantined'\n",
    "    elif ptd   == 272:\n",
    "        action  = 'Detection Only'\n",
    "    elif ptd   == 512:\n",
    "        action  = 'Process Killed'\n",
    "    elif ptd   == 768:\n",
    "        action  = 'Detection Only'    \n",
    "    elif ptd   == 1024:\n",
    "        action  = 'Operation Blocked'\n",
    "    elif ptd   == 1280:\n",
    "        action  = 'Detection Only'\n",
    "    elif ptd   == 2048:\n",
    "        action  = 'Process Blocked'\n",
    "    elif ptd   == 2176:\n",
    "        action  = 'File Quarantined'\n",
    "    elif ptd   == 2304:\n",
    "        action  = 'Detection Only' \n",
    "    elif ptd   == 4096:\n",
    "        action  = 'Registry Operation Blocked'\n",
    "    elif ptd   == 4112:\n",
    "        action  = 'Registry Operation Blocked'\n",
    "    elif ptd   == 4638:\n",
    "        action  = 'Detection Only' \n",
    "    elif ptd   == 32768:\n",
    "        action  = 'File system operation blocked'\n",
    "    elif ptd   == 8208:\n",
    "        action  = 'Process Killed - Parent process killed'\n",
    "    elif ptd   == 2099200:\n",
    "        action  = 'Process Blocked - Response action applied'\n",
    "            \n",
    "    else:\n",
    "        action  = 'Cant get action. Check the event'\n",
    "            \n",
    "    df.loc[i] = [severity, hostname, host_type, os, last_behavior, user, filename, cmdline, \n",
    "                 ioc, action, description]\n",
    "\n",
    "len(df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29bc1fdc",
   "metadata": {},
   "source": [
    "# ||   ATTENTION   ||\n",
    "\n",
    "\n",
    "### Here I'm showing a small sample of the DF just to give an idea that how it will look like, however I needed to hide some data since I haven't a lab environment and all data used is private\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2c43a29d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SEVERITY</th>\n",
       "      <th>TYPE</th>\n",
       "      <th>OS</th>\n",
       "      <th>LAST_DETECTION</th>\n",
       "      <th>CMD</th>\n",
       "      <th>ACTION</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Low</td>\n",
       "      <td>Workstation</td>\n",
       "      <td>Windows 10</td>\n",
       "      <td>2024-01-03 19:26:30</td>\n",
       "      <td>C:\\WINDOWS\\Explorer.EXE</td>\n",
       "      <td>File Quarantined</td>\n",
       "      <td>A file written to the file-system was classified as Adware/PUP based on its SHA256 hash.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>High</td>\n",
       "      <td>Workstation</td>\n",
       "      <td>Windows 10</td>\n",
       "      <td>2024-01-03 18:54:58</td>\n",
       "      <td>\"C:\\ProgramData\\Package Cache\\{a5ca1367-4057-471d-aa23-d25568ee038c}\\WindowsSensor.x64.exe\"  /uninstall</td>\n",
       "      <td>Process Killed - Parent process killed</td>\n",
       "      <td>A process attempted to uninstall the Falcon sensor in an unusual way. If this is unexpected, it might be an adversary trying to disable the Falcon sensor. Review the process tree.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  SEVERITY         TYPE          OS       LAST_DETECTION  \\\n",
       "0      Low  Workstation  Windows 10  2024-01-03 19:26:30   \n",
       "1     High  Workstation  Windows 10  2024-01-03 18:54:58   \n",
       "\n",
       "                                                                                                       CMD  \\\n",
       "0                                                                                  C:\\WINDOWS\\Explorer.EXE   \n",
       "1  \"C:\\ProgramData\\Package Cache\\{a5ca1367-4057-471d-aa23-d25568ee038c}\\WindowsSensor.x64.exe\"  /uninstall   \n",
       "\n",
       "                                   ACTION  \\\n",
       "0                        File Quarantined   \n",
       "1  Process Killed - Parent process killed   \n",
       "\n",
       "                                                                                                                                                                           DESCRIPTION  \n",
       "0                                                                                             A file written to the file-system was classified as Adware/PUP based on its SHA256 hash.  \n",
       "1  A process attempted to uninstall the Falcon sensor in an unusual way. If this is unexpected, it might be an adversary trying to disable the Falcon sensor. Review the process tree.  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['SEVERITY', 'TYPE', 'OS', 'LAST_DETECTION', 'CMD', 'ACTION', 'DESCRIPTION']].head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df448da5",
   "metadata": {},
   "source": [
    "## Part 5: Adding duplicated values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8cb3b168",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Counting duplicate values to have a sum of all detections for specific filename and hostname\n",
    "count = df.value_counts(['HOSTNAME', 'FILENAME'], dropna=True, sort=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c3b4771",
   "metadata": {},
   "source": [
    "## Part 6: Making the `count` object a new DF\n",
    "\n",
    "It is needed since the the value_counts() returned is a Series, and Series can't be merged with DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ad296524",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Converting count Series onto Dataframe\n",
    "df_count_list = pd.DataFrame(count)\n",
    "\n",
    "# Reset columns to show in df_count\n",
    "df_count = df_count_list.reset_index()\n",
    "\n",
    "# Renaming columns to show accordingly\n",
    "df_count.columns = ['HOSTNAME', 'FILENAME', 'COUNT']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8ce299c",
   "metadata": {},
   "source": [
    "## Part 7: Sorting by HOSTNAME and LAST_DETECTION "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3bcf6993",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sorting detections by column HOST and LAST_DETECTION\n",
    "sorted_df = df.sort_values(by=['HOSTNAME', 'LAST_DETECTION'], ascending=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e8cdf7f",
   "metadata": {},
   "source": [
    "## Part 8: Dropping duplicates hostnames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "33bf2b87",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Removing all unnecessary rows since we already have the full couting of it\n",
    "df_droped = sorted_df.drop_duplicates(subset=['HOSTNAME', 'FILENAME'], keep=\"first\") \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8ea6aa3",
   "metadata": {},
   "source": [
    "## Part 9: Merging the two dataframes (with only one last occurrence that host and file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "80d3ff30",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged = pd.merge(df_droped, df_count, on=['HOSTNAME', 'FILENAME'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "04f74a14",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SEVERITY</th>\n",
       "      <th>TYPE</th>\n",
       "      <th>OS</th>\n",
       "      <th>FILENAME</th>\n",
       "      <th>LAST_DETECTION</th>\n",
       "      <th>ACTION</th>\n",
       "      <th>COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Critical</td>\n",
       "      <td>Workstation</td>\n",
       "      <td>Windows 10</td>\n",
       "      <td>Utilman.exe</td>\n",
       "      <td>2024-01-03 17:38:03</td>\n",
       "      <td>Process Blocked</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>High</td>\n",
       "      <td>Workstation</td>\n",
       "      <td>Windows 10</td>\n",
       "      <td>net.exe</td>\n",
       "      <td>2024-01-03 14:38:47</td>\n",
       "      <td>Process Blocked</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>High</td>\n",
       "      <td>Workstation</td>\n",
       "      <td>Windows 7</td>\n",
       "      <td>bitsadmin.exe</td>\n",
       "      <td>2024-01-03 13:12:25</td>\n",
       "      <td>Process Blocked</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Critical</td>\n",
       "      <td>Workstation</td>\n",
       "      <td>Windows 10</td>\n",
       "      <td>sethc.exe</td>\n",
       "      <td>2024-01-01 15:33:59</td>\n",
       "      <td>Process Blocked</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Critical</td>\n",
       "      <td>Workstation</td>\n",
       "      <td>Windows 11</td>\n",
       "      <td>PsExec.exe</td>\n",
       "      <td>2024-01-03 11:03:43</td>\n",
       "      <td>File Quarantined</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    SEVERITY         TYPE          OS       FILENAME       LAST_DETECTION  \\\n",
       "26  Critical  Workstation  Windows 10    Utilman.exe  2024-01-03 17:38:03   \n",
       "27      High  Workstation  Windows 10        net.exe  2024-01-03 14:38:47   \n",
       "4       High  Workstation   Windows 7  bitsadmin.exe  2024-01-03 13:12:25   \n",
       "28  Critical  Workstation  Windows 10      sethc.exe  2024-01-01 15:33:59   \n",
       "22  Critical  Workstation  Windows 11     PsExec.exe  2024-01-03 11:03:43   \n",
       "\n",
       "              ACTION  COUNT  \n",
       "26   Process Blocked     32  \n",
       "27   Process Blocked     15  \n",
       "4    Process Blocked      6  \n",
       "28   Process Blocked      4  \n",
       "22  File Quarantined      4  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Showing a sample of the final result\n",
    "df_merged[['SEVERITY', 'TYPE', 'OS', 'FILENAME', 'LAST_DETECTION', 'ACTION', 'COUNT']].sort_values(by=['COUNT'], \n",
    "    ascending=False).head(5)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d8cbded",
   "metadata": {},
   "source": [
    "## Part 10: Exporting the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "25262a44",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_name = '/home/bruce/PROJECTS/JUPYTER/REPORTS/Detections.xlsx'\n",
    "df_merged.to_excel(file_name)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af69cc1b",
   "metadata": {},
   "source": [
    "## Part 11: Optional (quick tip if the detections has some strange chars)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f42ff873",
   "metadata": {},
   "outputs": [],
   "source": [
    "### *** If raise IllegalCharacterError saving as excel by utf-8 errors, try this *** ###\n",
    "\n",
    "#file_name = '/home/bruce/PROJECTS/JUPYTER/FALCON/REPORTS/Detections.csv'\n",
    "#df_merged.to_csv(file_name, encoding='utf-8-sig')\n"
   ]
  }
 ],
 "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}