seclake_cloudtrail_investigation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -r requirements.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl\n",
    "import pandas\n",
    "import os, duckdb"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Definition Language"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = duckdb.connect('seclake_analysis.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8d0b2e83c1f14f3fbca6074442fcd7a4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "con.sql(\"CREATE TABLE route53 as select * from parquet_scan('data/route53/*/*/*.gz.parquet', hive_partitioning=false)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "71d8dee850c7413ca9d4708350b452c6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "con.sql(\"CREATE TABLE eks as select * from parquet_scan('data/eks/*/*/*.gz.parquet', hive_partitioning=false)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.sql(\"CREATE TABLE sechub as select * from read_parquet('data/sechub/*/*/*.gz.parquet', hive_partitioning=false)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "13a96385a5a845e2a79eca7443122aee",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "con.sql(\"CREATE TABLE cloudtrail as select * from read_parquet('data/cloudtrail/*/*6*1*/*.gz.parquet', hive_partitioning=false)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.sql(\"CREATE TABLE vpcflow as select * from read_parquet('data/vpcflow/*/*/*.gz.parquet', hive_partitioning=false)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### helper table DDL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.sql(\"create table aws_account_info as select * from read_csv('helper_tables/aws_account_information.csv')\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.sql(\"create table kev as select * from read_csv('helper_tables/known_exploited_vulnerabilities.csv')\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-------"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# OCSF event classes across log sources\n",
    "\n",
    "It is worth spending the time to understand how AWS translates native log sources to OCSF subclasses. AWS makes this available in the documentation for OOB sources as well"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Cloudtrail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────────────────────┬────────────────┬───────┐\n",
       "│        category_name         │   class_name   │  cnt  │\n",
       "│           varchar            │    varchar     │ int64 │\n",
       "├──────────────────────────────┼────────────────┼───────┤\n",
       "│ Application Activity         │ API Activity   │ 85266 │\n",
       "│ Identity & Access Management │ Authentication │  2961 │\n",
       "└──────────────────────────────┴────────────────┴───────┘"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql('select category_name, class_name, count(class_name) as cnt from cloudtrail group by category_name, class_name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### SecHub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌───────────────┬───────────────────────┬───────┐\n",
       "│ category_name │      class_name       │  cnt  │\n",
       "│    varchar    │        varchar        │ int64 │\n",
       "├───────────────┼───────────────────────┼───────┤\n",
       "│ Findings      │ Detection Finding     │   579 │\n",
       "│ Findings      │ Compliance Finding    │   103 │\n",
       "│ Findings      │ Vulnerability Finding │    53 │\n",
       "└───────────────┴───────────────────────┴───────┘"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql('select category_name, class_name, count(class_name) as cnt from sechub group by category_name, class_name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### vpcflow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────────┬──────────────────┬─────────┐\n",
       "│  category_name   │    class_name    │   cnt   │\n",
       "│     varchar      │     varchar      │  int64  │\n",
       "├──────────────────┼──────────────────┼─────────┤\n",
       "│ Network Activity │ Network Activity │ 1959325 │\n",
       "└──────────────────┴──────────────────┴─────────┘"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql('select category_name, class_name, count(class_name) as cnt from vpcflow group by category_name, class_name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Route53"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────────┬──────────────┬───────┐\n",
       "│  category_name   │  class_name  │  cnt  │\n",
       "│     varchar      │   varchar    │ int64 │\n",
       "├──────────────────┼──────────────┼───────┤\n",
       "│ Network Activity │ DNS Activity │ 20668 │\n",
       "└──────────────────┴──────────────┴───────┘"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql('select category_name, class_name, count(class_name) as cnt from route53 group by category_name, class_name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### EKS Audit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────────────┬──────────────┬───────────────────┐\n",
       "│    category_name     │  class_name  │ count(class_name) │\n",
       "│       varchar        │   varchar    │       int64       │\n",
       "├──────────────────────┼──────────────┼───────────────────┤\n",
       "│ Application Activity │ API Activity │           9388581 │\n",
       "└──────────────────────┴──────────────┴───────────────────┘"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql('select distinct category_name, class_name, count(class_name) from eks group by category_name, class_name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------------------------\n",
    "# CloudTrail in OCSF\n",
    "Common cloudtrail fields as OCSF fields:\n",
    "\n",
    "- eventName \n",
    "- eventSource\n",
    "- userAgent\n",
    "- userIdentity:userName\n",
    "\n",
    "OCSF query\n",
    "\n",
    "```sql\n",
    "SELECT DISTINCT \n",
    "    api.operation, \n",
    "    api.service.name, \n",
    "    http_request.user_agent, \n",
    "    actor.user.name as username\n",
    "FROM cloudtrail \n",
    "WHERE username IS NOT NULL\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────────────┬──────────────────────┬─────────────────────────────────────────────────────────┬──────────────┐\n",
       "│      operation       │         name         │                       user_agent                        │     name     │\n",
       "│       varchar        │       varchar        │                         varchar                         │   varchar    │\n",
       "├──────────────────────┼──────────────────────┼─────────────────────────────────────────────────────────┼──────────────┤\n",
       "│ ListApplications     │ sso.amazonaws.com    │ Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit…  │ dakota-riley │\n",
       "│ ListApplications     │ sso.amazonaws.com    │ Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) Apple…  │ dakota-riley │\n",
       "│ AssumeRoleWithSAML   │ sts.amazonaws.com    │ aws-internal/3 aws-sdk-java/1.12.735 Linux/4.14.344-2…  │ dakota-riley │\n",
       "│ Authenticate         │ sso.amazonaws.com    │ Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit…  │ dakota-riley │\n",
       "│ ListProfilesForApp…  │ sso.amazonaws.com    │ Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit…  │ dakota-riley │\n",
       "│ UserAuthentication   │ signin.amazonaws.com │ Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) Apple…  │ dakota-riley │\n",
       "│ Federate             │ sso.amazonaws.com    │ Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit…  │ dakota-riley │\n",
       "│ CredentialVerifica…  │ signin.amazonaws.com │ Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) Apple…  │ dakota-riley │\n",
       "│ UserAuthentication   │ signin.amazonaws.com │ Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit…  │ dakota-riley │\n",
       "│ GetRoleCredentials   │ sso.amazonaws.com    │ Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) Apple…  │ dakota-riley │\n",
       "│ Federate             │ sso.amazonaws.com    │ Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) Apple…  │ dakota-riley │\n",
       "│ CredentialVerifica…  │ signin.amazonaws.com │ Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit…  │ dakota-riley │\n",
       "│ CredentialChallenge  │ signin.amazonaws.com │ Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) Apple…  │ dakota-riley │\n",
       "│ ListProfilesForApp…  │ sso.amazonaws.com    │ Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) Apple…  │ dakota-riley │\n",
       "│ CredentialChallenge  │ signin.amazonaws.com │ Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit…  │ dakota-riley │\n",
       "│ GetRoleCredentials   │ sso.amazonaws.com    │ Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit…  │ dakota-riley │\n",
       "│ Authenticate         │ sso.amazonaws.com    │ Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) Apple…  │ dakota-riley │\n",
       "├──────────────────────┴──────────────────────┴─────────────────────────────────────────────────────────┴──────────────┤\n",
       "│ 17 rows                                                                                                    4 columns │\n",
       "└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql('select distinct api.operation, api.service.name, http_request.user_agent, actor.user.name from cloudtrail where actor.user.name is not null')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Network Traffic Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Source and destination connection information mapped to src_endpoint and dst_endpoint objects\n",
    "\n",
    "```sql\n",
    "SELECT DISTINCT \n",
    "    src_endpoint.ip as srcip, \n",
    "    src_endpoint.port as srcprt, \n",
    "    dst_endpoint.ip as dstip, \n",
    "    dst_endpoint.port as dstprt \n",
    "FROM vpcflow \n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────────────┬────────┬──────────────┬────────┐\n",
       "│      srcip      │ srcprt │    dstip     │ dstprt │\n",
       "│     varchar     │ int32  │   varchar    │ int32  │\n",
       "├─────────────────┼────────┼──────────────┼────────┤\n",
       "│ 165.154.120.253 │  40307 │ 10.0.1.151   │  42901 │\n",
       "│ 193.163.125.184 │  33098 │ 10.0.36.53   │  49183 │\n",
       "│ 44.213.45.216   │      0 │ 10.0.81.48   │      0 │\n",
       "│ 10.0.1.151      │  56994 │ 52.46.133.88 │    443 │\n",
       "│ 52.46.133.88    │    443 │ 10.0.1.151   │  56994 │\n",
       "│ 162.216.149.44  │  49577 │ 10.0.1.151   │  52951 │\n",
       "│ 180.118.254.16  │  35360 │ 10.0.36.53   │  27015 │\n",
       "│ 35.203.211.147  │  49195 │ 10.0.36.53   │  11028 │\n",
       "│ 87.246.7.46     │  53382 │ 10.0.36.53   │   4034 │\n",
       "│ 8.222.200.88    │  53322 │ 10.0.1.151   │   2222 │\n",
       "│      ·          │    ·   │     ·        │     ·  │\n",
       "│      ·          │    ·   │     ·        │     ·  │\n",
       "│      ·          │    ·   │     ·        │     ·  │\n",
       "│ 52.15.109.126   │  38866 │ 10.0.81.48   │  12165 │\n",
       "│ 82.223.35.114   │  59990 │ 10.0.36.53   │     80 │\n",
       "│ 162.216.150.132 │  53399 │ 10.0.81.48   │  30012 │\n",
       "│ 184.105.247.224 │  51084 │ 10.0.81.48   │   8888 │\n",
       "│ 192.241.205.27  │  38188 │ 10.0.81.48   │   8983 │\n",
       "│ 111.33.71.238   │  43701 │ 10.0.36.53   │   9001 │\n",
       "│ 35.203.211.107  │  53745 │ 10.0.75.211  │   9523 │\n",
       "│ 147.78.47.118   │  48127 │ 10.0.75.211  │  52035 │\n",
       "│ 35.203.211.83   │  51230 │ 10.0.75.211  │   3391 │\n",
       "│ 162.216.150.152 │  52288 │ 10.0.75.211  │  49353 │\n",
       "├─────────────────┴────────┴──────────────┴────────┤\n",
       "│ ? rows (>9999 rows, 20 shown)          4 columns │\n",
       "└──────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"select distinct src_endpoint.ip as srcip, src_endpoint.port as srcprt, dst_endpoint.ip as dstip, dst_endpoint.port as dstprt from vpcflow\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---------"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating helpful views\n",
    "\n",
    "The Amazon SecurityHub integration with Security Lake streams ALL finding changes to our buckets. While this maybe helpful for certain analytics, often teams want to understand what the current state of SecurityHub is and its issues across accounts.\n",
    "\n",
    "These can be replicated in AWS Athena: [AWS Athena User Guide - Working with views](https://docs.aws.amazon.com/athena/latest/ug/views.html)\n",
    "\n",
    "### Creating a view of the current status of all SecurityHub findings based on the latest record per unique finding ID\n",
    "\n",
    "```sql\n",
    "    CREATE VIEW sechub_current as \n",
    "    SELECT sechub.* FROM sechub \n",
    "    JOIN (SELECT MAX(time_dt) AS latest, finding_info.uid FROM sechub GROUP BY finding_info.uid) latest_finding_status \n",
    "    ON sechub.finding_info.uid = latest_finding_status.uid AND sechub.time_dt = latest_finding_status.latest;\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.sql(\"\"\"\n",
    "    CREATE VIEW sechub_current as \n",
    "    SELECT sechub.* FROM sechub \n",
    "    JOIN (SELECT MAX(time_dt) AS latest, finding_info.uid FROM sechub GROUP BY finding_info.uid) latest_finding_status \n",
    "    ON sechub.finding_info.uid = latest_finding_status.uid AND sechub.time_dt = latest_finding_status.latest;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Loading additional helpful data sources"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### AWS Account Metadata table\n",
    "\n",
    "Having a table of AWS Account IDs to helpful metadata will allow you to produce more actionable reports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────┬───────────┬────────────────────┬─────────────────────┐\n",
       "│  account_id  │ env_level │    owner_email     │    account_alias    │\n",
       "│    int64     │  varchar  │      varchar       │       varchar       │\n",
       "├──────────────┼───────────┼────────────────────┼─────────────────────┤\n",
       "│ 137294155267 │ prod      │ dakota@example.com │ super_secret_prod   │\n",
       "│ 211125761087 │ prod      │ bob@example.com    │ its_just_dev        │\n",
       "│ 637423320304 │ prod      │ rick@example.com   │ financial_data_here │\n",
       "└──────────────┴───────────┴────────────────────┴─────────────────────┘"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"select * from aws_account_info\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CISA KEV\n",
    "\n",
    "Great and free source for assisting with vulnerability prioritzation using a list of CVEs that are known to have been observed being exploited in the wild\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌────────────────┬───────────────┬──────────────────────┬───┬────────────┬──────────────────────┬─────────┐\n",
       "│     cveID      │ vendorProject │       product        │ … │  dueDate   │ knownRansomwareCam…  │  notes  │\n",
       "│    varchar     │    varchar    │       varchar        │   │    date    │       varchar        │ varchar │\n",
       "├────────────────┼───────────────┼──────────────────────┼───┼────────────┼──────────────────────┼─────────┤\n",
       "│ CVE-2021-27104 │ Accellion     │ FTA                  │ … │ 2021-11-17 │ Known                │ NULL    │\n",
       "│ CVE-2021-27102 │ Accellion     │ FTA                  │ … │ 2021-11-17 │ Known                │ NULL    │\n",
       "│ CVE-2021-27101 │ Accellion     │ FTA                  │ … │ 2021-11-17 │ Known                │ NULL    │\n",
       "│ CVE-2021-27103 │ Accellion     │ FTA                  │ … │ 2021-11-17 │ Known                │ NULL    │\n",
       "│ CVE-2021-21017 │ Adobe         │ Acrobat and Reader   │ … │ 2021-11-17 │ Unknown              │ NULL    │\n",
       "│ CVE-2021-28550 │ Adobe         │ Acrobat and Reader   │ … │ 2021-11-17 │ Unknown              │ NULL    │\n",
       "│ CVE-2018-4939  │ Adobe         │ ColdFusion           │ … │ 2022-05-03 │ Unknown              │ NULL    │\n",
       "│ CVE-2018-15961 │ Adobe         │ ColdFusion           │ … │ 2022-05-03 │ Unknown              │ NULL    │\n",
       "│ CVE-2018-4878  │ Adobe         │ Flash Player         │ … │ 2022-05-03 │ Known                │ NULL    │\n",
       "│ CVE-2020-5735  │ Amcrest       │ Cameras and Networ…  │ … │ 2022-05-03 │ Unknown              │ NULL    │\n",
       "├────────────────┴───────────────┴──────────────────────┴───┴────────────┴──────────────────────┴─────────┤\n",
       "│ 10 rows                                                                            10 columns (6 shown) │\n",
       "└─────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"select * from kev limit 10\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Vulnerability and Configuration Management"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Active SecHub Findings by AWS Account"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT cloud.account.uid, severity, count(*) AS cnt \n",
    "FROM sechub_current \n",
    "WHERE status IN ('New', 'Notified') \n",
    "GROUP BY cloud.account.uid, severity\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────┬───────────────┬───────┐\n",
       "│     uid      │   severity    │  cnt  │\n",
       "│   varchar    │    varchar    │ int64 │\n",
       "├──────────────┼───────────────┼───────┤\n",
       "│ 137294155267 │ Informational │     8 │\n",
       "│ 137294155267 │ High          │   142 │\n",
       "│ 137294155267 │ Medium        │   155 │\n",
       "│ 137294155267 │ Critical      │    10 │\n",
       "│ 137294155267 │ Low           │    27 │\n",
       "│ 637423320304 │ Medium        │     5 │\n",
       "└──────────────┴───────────────┴───────┘"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"select cloud.account.uid, severity, count(*) as cnt from sechub_current where status in ('New', 'Notified') group by cloud.account.uid, severity\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────┬───────────────────────┬───────┐\n",
       "│     uid      │      class_name       │ count │\n",
       "│   varchar    │        varchar        │ int64 │\n",
       "├──────────────┼───────────────────────┼───────┤\n",
       "│ 137294155267 │ Compliance Finding    │     1 │\n",
       "│ 137294155267 │ Detection Finding     │   288 │\n",
       "│ 137294155267 │ Vulnerability Finding │    53 │\n",
       "│ 637423320304 │ Detection Finding     │     4 │\n",
       "│ 637423320304 │ Compliance Finding    │     3 │\n",
       "└──────────────┴───────────────────────┴───────┘"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"select cloud.account.uid, class_name, count(*) as count from sechub_current group by cloud.account.uid, class_name order by cloud.account.uid\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────┬───────────────┬───────┐\n",
       "│     uid      │   severity    │  cnt  │\n",
       "│   varchar    │    varchar    │ int64 │\n",
       "├──────────────┼───────────────┼───────┤\n",
       "│ 137294155267 │ High          │    14 │\n",
       "│ 137294155267 │ Informational │     8 │\n",
       "│ 137294155267 │ Medium        │    21 │\n",
       "│ 137294155267 │ Critical      │    10 │\n",
       "└──────────────┴───────────────┴───────┘"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"select cloud.account.uid, severity, count(*) as cnt from sechub_current where status in ('New', 'Notified') and class_name='Vulnerability Finding' group by cloud.account.uid, severity\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT \n",
    "    vulnerability.cve.uid, \n",
    "    vulnerability.cve.epss.score as score\n",
    "    cloud.account.uid, \n",
    "    severity,\n",
    "    FROM (\n",
    "            select unnest(vulnerabilities) as vulnerability, cloud, severity from sechub_current where status in ('New', 'Notified') and class_name='Vulnerability Finding')\n",
    "    ORDER BY severity, score\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────────────────┬─────────┬──────────────┬──────────┐\n",
       "│         uid         │  score  │     uid      │ severity │\n",
       "│       varchar       │ varchar │   varchar    │ varchar  │\n",
       "├─────────────────────┼─────────┼──────────────┼──────────┤\n",
       "│ CVE-2023-46233      │ 0.00067 │ 137294155267 │ Critical │\n",
       "│ CVE-2023-26136      │ 0.00108 │ 137294155267 │ Critical │\n",
       "│ CVE-2020-15084      │ 0.00197 │ 137294155267 │ Critical │\n",
       "│ CVE-2023-37903      │ 0.00248 │ 137294155267 │ Critical │\n",
       "│ CVE-2023-37466      │ 0.00348 │ 137294155267 │ Critical │\n",
       "│ CVE-2015-9235       │ 0.00559 │ 137294155267 │ Critical │\n",
       "│ CVE-2020-12265      │ 0.00619 │ 137294155267 │ Critical │\n",
       "│ CVE-2023-32314      │ 0.01236 │ 137294155267 │ Critical │\n",
       "│ CVE-2019-10744      │ 0.01478 │ 137294155267 │ Critical │\n",
       "│ GHSA-5mrr-rgp6-x4gr │ NULL    │ 137294155267 │ Critical │\n",
       "│       ·             │  ·      │      ·       │   ·      │\n",
       "│       ·             │  ·      │      ·       │   ·      │\n",
       "│       ·             │  ·      │      ·       │   ·      │\n",
       "│ CVE-2021-26540      │ 0.00137 │ 137294155267 │ Medium   │\n",
       "│ CVE-2021-32822      │ 0.00221 │ 137294155267 │ Medium   │\n",
       "│ CVE-2020-28500      │ 0.00231 │ 137294155267 │ Medium   │\n",
       "│ CVE-2019-1010266    │ 0.00317 │ 137294155267 │ Medium   │\n",
       "│ CVE-2016-4055       │ 0.0081  │ 137294155267 │ Medium   │\n",
       "│ GHSA-rvg8-pwq2-xj7q │ NULL    │ 137294155267 │ Medium   │\n",
       "│ NSWG-ECO-154        │ NULL    │ 137294155267 │ Medium   │\n",
       "│ NULL                │ NULL    │ 137294155267 │ Medium   │\n",
       "│ NULL                │ NULL    │ 137294155267 │ Medium   │\n",
       "│ NULL                │ NULL    │ 137294155267 │ Medium   │\n",
       "├─────────────────────┴─────────┴──────────────┴──────────┤\n",
       "│ 60 rows (20 shown)                            4 columns │\n",
       "└─────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"select vulnerability.cve.uid, vulnerability.cve.epss.score as score, cloud.account.uid, severity from (select unnest(vulnerabilities) as vulnerability, cloud, severity from sechub_current where status in ('New', 'Notified') and class_name='Vulnerability Finding') order by severity, score\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Utilizing Observables for Fun and Profit\n",
    "\n",
    "OCSF provides an observables column, which is designed to surface common entities across an event. In the case of SecurityHub findings, SecurityLake will transform ASFF findings and include things like AWS Resources, or GuardDuty finding targets in the observables. \n",
    "\n",
    "#### AWS Inspector Container vulnerability scan\n",
    "\n",
    "```json\n",
    "[\n",
    "  {\n",
    "    \"name\": \"resource.uid\",\n",
    "    \"value\": \"arn:aws:ecr:us-east-1:137294155267:repository/juiceshop/sha256:1ee9b8d6e89e8faee4c4c1a7a31931509032f8ac095e1d3664bc5a49c1ee778b\",\n",
    "    \"type\": \"Resource UID\",\n",
    "    \"type_id\": 10\n",
    "  }\n",
    "]\n",
    "```\n",
    "\n",
    "#### AWS GuardDuty Finding\n",
    "\n",
    "```json\n",
    "[\n",
    "  {\n",
    "    \"name\": \"resources[].uid\",\n",
    "    \"value\": \"arn:aws:ec2:us-east-1:137294155267:instance/i-99999999\",\n",
    "    \"type\": \"Resource UID\",\n",
    "    \"type_id\": 10\n",
    "  },\n",
    "  {\n",
    "    \"name\": \"evidences[].actor.process.user.name\",\n",
    "    \"value\": \"ec2-user\",\n",
    "    \"type\": \"User Name\",\n",
    "    \"type_id\": 4\n",
    "  },\n",
    "  {\n",
    "    \"name\": \"resources[].uid\",\n",
    "    \"value\": \"GeneratedFindingContainerId\",\n",
    "    \"type\": \"Resource UID\",\n",
    "    \"type_id\": 10\n",
    "  }\n",
    "]\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Unique Observable types in SecHub findings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌───────────────────────────────────────────────┐\n",
       "│                     name                      │\n",
       "│                    varchar                    │\n",
       "├───────────────────────────────────────────────┤\n",
       "│ evidences[].actor.process.name                │\n",
       "│ evidences[].query.hostname                    │\n",
       "│ evidences[].process.file.hashes[].value       │\n",
       "│ evidences[].process.user.name                 │\n",
       "│ evidences[].actor.process.user.name           │\n",
       "│ resource.uid                                  │\n",
       "│ resources[].uid                               │\n",
       "│ evidences[].actor.process.file.hashes[].value │\n",
       "│ evidences[].src_endpoint.ip                   │\n",
       "│ evidences[].process.name                      │\n",
       "│ evidences[].dst_endpoint.ip                   │\n",
       "├───────────────────────────────────────────────┤\n",
       "│                    11 rows                    │\n",
       "└───────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"SELECT distinct observable.name from (SELECT *, unnest(observables) as observable FROM sechub_current) \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Count of SecHub findings associated with a particular AWS Resource\n",
    "\n",
    "Viewing resources with the highest number of active sechub findings associated with them could be a method of identifying risky assets that deserve further analysis\n",
    "\n",
    "```sql\n",
    "SELECT resources.value AS VALUE, count(*) AS count \n",
    "FROM (SELECT unnest(observables) AS resources FROM sechub_current) \n",
    "WHERE (value like 'arn%' or value like 'AWS%') \n",
    "GROUP BY resources.value \n",
    "ORDER BY count desc\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────┬───────┐\n",
       "│                                                    value                                                     │ count │\n",
       "│                                                   varchar                                                    │ int64 │\n",
       "├──────────────────────────────────────────────────────────────────────────────────────────────────────────────┼───────┤\n",
       "│ arn:aws:ec2:us-east-1:137294155267:instance/i-99999999                                                       │   230 │\n",
       "│ arn:aws:eks:us-east-1:137294155267:cluster/GeneratedFindingEKSClusterName                                    │    73 │\n",
       "│ AWS::IAM::AccessKey:GeneratedFindingAccessKeyId                                                              │    57 │\n",
       "│ arn:aws:ecr:us-east-1:137294155267:repository/juiceshop/sha256:1ee9b8d6e89e8faee4c4c1a7a31931509032f8ac095…  │    51 │\n",
       "│ arn:aws:ecs:region:123456789000:cluster/clusterName                                                          │    38 │\n",
       "│ arn:aws:ecs:region:123456789000:task/mycluster/043de9ab3                                                     │    38 │\n",
       "│ arn:aws:s3:::EXAMPLE-BUCKET2                                                                                 │    20 │\n",
       "│ arn:aws:s3:::EXAMPLE-BUCKET1                                                                                 │    20 │\n",
       "│ arn:aws:s3:::EXAMPLE-BUCKET3                                                                                 │    20 │\n",
       "│ arn:aws:s3:::EXAMPLE-BUCKET4                                                                                 │    20 │\n",
       "│ arn:aws:ec2:us-west-2:123456789000:volume/vol-09d5050dea915943d                                              │    10 │\n",
       "│ arn:aws:rds:us-east-1:137294155267:db:GeneratedFindingDBInstanceId                                           │     9 │\n",
       "│ arn:aws:lambda:us-east-1:137294155267:function:GeneratedFindingLambdaFunctionName:$LATEST                    │     7 │\n",
       "│ arn:aws:eks:us-east-1:637423320304:cluster/Cluster9EE0221C-be7ef46258d64fbaad28f21a1bf6c8fb                  │     4 │\n",
       "│ AWS::::Account:637423320304                                                                                  │     3 │\n",
       "│ arn:aws:ec2:us-east-1:137294155267:instance/i-05f75718774ddb32e                                              │     3 │\n",
       "│ AWS::::Account:137294155267                                                                                  │     1 │\n",
       "├──────────────────────────────────────────────────────────────────────────────────────────────────────────────┴───────┤\n",
       "│ 17 rows                                                                                                    2 columns │\n",
       "└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"select resources.value as value, count(*) as count from (select unnest(observables) as resources from sechub_current) where (value like 'arn%' or value like 'AWS%') group by resources.value order by count desc\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Finding all events that occured against a given resource in CloudTrail\n",
    "\n",
    "```sql\n",
    "SELECT api.operation, observable.value, time FROM \n",
    "(SELECT *, unnest(observables) as observable FROM cloudtrail) \n",
    "WHERE observable.value LIKE 'arn:aws:ecr:us-east-1:%:repository/%'\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────────────┬───────────────────────────────────────────────────────────────────────────────────────────────┐\n",
       "│      operation       │                                             value                                             │\n",
       "│       varchar        │                                            varchar                                            │\n",
       "├──────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────┤\n",
       "│ CreateRepository     │ arn:aws:ecr:us-east-1:137294155267:repository/juiceshop                                       │\n",
       "│ DescribeRepositories │ arn:aws:ecr:us-east-1:137294155267:repository/juiceshop                                       │\n",
       "│ DescribeImages       │ arn:aws:ecr:us-east-1:137294155267:repository/juiceshop                                       │\n",
       "│ DescribeImages       │ arn:aws:ecr:us-east-1:137294155267:repository/repository22e53bbd-qiifj2kgxqdx                 │\n",
       "│ GetRepositoryPolicy  │ arn:aws:ecr:us-east-1:137294155267:repository/python                                          │\n",
       "│ PutImage             │ arn:aws:ecr:us-east-1:137294155267:repository/juiceshop                                       │\n",
       "│ GetRepositoryPolicy  │ arn:aws:ecr:us-east-1:137294155267:repository/juiceshop                                       │\n",
       "│ BatchGetRepository…  │ arn:aws:ecr:us-east-1:137294155267:repository/juiceshop                                       │\n",
       "│ CompleteLayerUpload  │ arn:aws:ecr:us-east-1:137294155267:repository/juiceshop                                       │\n",
       "│ BatchCheckLayerAva…  │ arn:aws:ecr:us-east-1:137294155267:repository/juiceshop                                       │\n",
       "│          ·           │                            ·                                                                  │\n",
       "│          ·           │                            ·                                                                  │\n",
       "│          ·           │                            ·                                                                  │\n",
       "│ GetRepositoryPolicy  │ arn:aws:ecr:us-east-1:137294155267:repository/repository22e53bbd-qiifj2kgxqdx                 │\n",
       "│ BatchGetRepository…  │ arn:aws:ecr:us-east-1:137294155267:repository/cdk-hnb659fds-container-assets-137294155267-u…  │\n",
       "│ DescribeRepositories │ arn:aws:ecr:us-east-1:137294155267:repository/python                                          │\n",
       "│ DescribeImages       │ arn:aws:ecr:us-east-1:137294155267:repository/python                                          │\n",
       "│ GetRepositoryPolicy  │ arn:aws:ecr:us-east-1:137294155267:repository/cdk-hnb659fds-container-assets-137294155267-u…  │\n",
       "│ BatchGetRepository…  │ arn:aws:ecr:us-east-1:137294155267:repository/repository22e53bbd-qiifj2kgxqdx                 │\n",
       "│ GetDownloadUrlForL…  │ arn:aws:ecr:us-east-1:137294155267:repository/juiceshop                                       │\n",
       "│ BatchGetImage        │ arn:aws:ecr:us-east-1:137294155267:repository/juiceshop                                       │\n",
       "│ CreateRepository     │ arn:aws:ecr:us-east-1:137294155267:repository/python                                          │\n",
       "│ PolicyExecutionEvent │ arn:aws:ecr:us-east-1:637423320304:repository/cdk-hnb659fds-container-assets-637423320304-u…  │\n",
       "├──────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────┤\n",
       "│ 24 rows (20 shown)                                                                                         2 columns │\n",
       "└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"SELECT distinct api.operation, observable.value from (SELECT *, unnest(observables) as observable FROM cloudtrail) where observable.value like 'arn:aws:ecr:us-east-1:%:repository/%' \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.10.12"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}