{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "394caeb2",
   "metadata": {},
   "source": [
    "# IAM Credential Compromise - Detection\n",
    "This notebook is to be used in case of malicious activity inside your AWS Account.\n",
    "We will query CloudTrail logs using Athena in order to detect and contain malicious account activity.\n",
    "\n",
    "We are following best practices from the AWS Incident Response guide as we execute our processes:\n",
    "\n",
    "https://docs.aws.amazon.com/whitepapers/latest/aws-security-incident-response-guide/aws-security-incident-response-guide.html"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a9f5f5a",
   "metadata": {},
   "source": [
    "# Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3c4bf4d",
   "metadata": {},
   "source": [
    "## Load Libraries\n",
    "\n",
    "In order to query CloudTrail and interact with AWS, we need to load several libraries and configure our environment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad09e1c1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "pip install pyathena --quiet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89920512",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import boto3  # the Python SDK for AWS\n",
    "import pandas as pd # Pandas is a data analysis tool for Python\n",
    "from pyathena import connect # Python API client for Amazon Athena\n",
    "region='us-east-1' # Set region variable to us-east-1 for API commands\n",
    "athena_bucket = 's3:// ENTER BUCKET NAME HERE /'  # S3 bucket that is configured to store your Athena queries\n",
    "db_name = 'security_analysis' # database used by Athena. Choose 'default' if your CloudTrail was created via the Athena console and 'security_analysis' if you are using the AWS Security Analytics Bootstrap"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9c04dd5",
   "metadata": {},
   "source": [
    "## 1.0 Set up helper function for Athena"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cce8bd01",
   "metadata": {},
   "source": [
    "The Python query_results function shown below will help you query Athena tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9173ba28",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def query_results(sql):\n",
    "    \n",
    "    cursor = connect(s3_staging_dir=athena_bucket, region_name=region).cursor()\n",
    "    cursor.execute(sql)\n",
    "    \n",
    "    columns = cursor.description\n",
    "    data = cursor.fetchall()\n",
    "\n",
    "    column_names = [column[0] for column in columns]\n",
    "    rows = [dict(zip(column_names, data_row)) for data_row in data]\n",
    "\n",
    "    df = pd.DataFrame(rows, columns=column_names)\n",
    "    df1 = df.style.set_table_styles([dict(selector='th', props=[('text-align', 'center')])])\n",
    "    \n",
    "    return df1.set_properties(**{'text-align': 'center'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73c76c04",
   "metadata": {},
   "source": [
    "# 2.0 IAM Investigation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a9956cf",
   "metadata": {},
   "source": [
    "### 2.1 Investigating Authorization Failures\n",
    "Lists failed access attempts with additional information such as the user identity ARN, time, AWS service, API call, region, source IP address, error code, and error message."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c944a62",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "auth_fail =f\"\"\"\n",
    "\n",
    "select *\n",
    "from \"{db_name}\".\"cloudtrail\" \n",
    "where errorCode in \n",
    "('Client.UnauthorizedOperation','Client.InvalidPermission.NotFound','Client.OperationNotPermitted','AccessDenied')\n",
    "and useridentity.arn like '%iam%'\n",
    "order by eventTime desc\n",
    "limit 10\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "82dc51d3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "results = query_results(auth_fail)\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0ca7fda",
   "metadata": {},
   "source": [
    "### 2.2 Investigating the User Identities with Authorization Failures\n",
    "As you know there are some failed access attempts, lets now look at the user identities with a lot of authorization failures."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a7a2d3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "usr_auth_fail =f\"\"\"\n",
    "\n",
    "select userIdentity.arn, count(*) as total\n",
    "from \"{db_name}\".\"cloudtrail\" \n",
    "where errorCode in ('Client.UnauthorizedOperation','Client.InvalidPermission.NotFound','Client.OperationNotPermitted','AccessDenied')\n",
    "and useridentity.arn like '%iam%'\n",
    "group by userIdentity.arn\n",
    "order by total desc\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bf3324b",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = query_results(usr_auth_fail)\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1630e83",
   "metadata": {},
   "source": [
    "### 2.3 Investigating an IAM User\n",
    "Now that you have identified the potential IAM entity which has been compromised, you should do further investigations to identify what the entity has been attemping to do."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4155d75d",
   "metadata": {},
   "outputs": [],
   "source": [
    "iam_usr =f\"\"\"\n",
    "\n",
    "select *\n",
    "from \"{db_name}\".\"cloudtrail\" \n",
    "where userIdentity.arn='insert ARN of IAM Credential from query in 2.2'\n",
    "limit 5\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41b6d3e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = query_results(iam_usr)\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6fa5ce6",
   "metadata": {},
   "source": [
    "### 2.4 Investigation the AWS services Used by the Suspicious Identity\n",
    "Now that you have identified the potential IAM entity which has been compromised, you should do further investigations to identify what services the suspicious identity has been attempting to use."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2141a25",
   "metadata": {},
   "outputs": [],
   "source": [
    "services_used =f\"\"\"\n",
    "\n",
    "select eventSource, count(*) as total\n",
    "from \"{db_name}\".\"cloudtrail\" \n",
    "where userIdentity.arn='insert ARN of IAM Credential from query in 2.2'\n",
    "group by eventSource\n",
    "order by total desc\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a22c14e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = query_results(services_used)\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8e43855",
   "metadata": {},
   "source": [
    "### 2.5 Investigation the Action Attempted by the Suspicious Identity\n",
    "Now that you have identified the potential IAM entity which has been compromised, you should do further investigations to identify what action the suspicious identity has been attempting to take."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7deddba8",
   "metadata": {},
   "outputs": [],
   "source": [
    "act_attempt =f\"\"\"\n",
    "select eventName, count(*) as total\n",
    "from \"{db_name}\".\"cloudtrail\" \n",
    "where userIdentity.arn='insert ARN of IAM Credential from query in 2.2'\n",
    "group by eventName\n",
    "order by total desc\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca23e3ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = query_results(act_attempt)\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc8b89ea",
   "metadata": {},
   "source": [
    "### 2.6 Bringing it all together\n",
    "Bring together the previous queries to create a single query showing the event name, AWS service, and AWS region where requests were being made to by the compromised IAM entity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81201658",
   "metadata": {},
   "outputs": [],
   "source": [
    "service_apicalls_time =f\"\"\"\n",
    "\n",
    "select eventTime, eventSource, eventName, awsregion, count(*) AS total \n",
    "from \"{db_name}\".\"cloudtrail\" \n",
    "where userIdentity.arn='insert ARN of IAM Credential from query in 2.2'\n",
    "group by eventName, eventSource, eventTime, awsregion\n",
    "order by eventTime DESC\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c5d4b5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = query_results(service_apicalls_time)\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "793f45e6",
   "metadata": {},
   "source": [
    "### Title of the query\n",
    "Insert description about the purpose and expected output of the query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ea227ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "query =f\"\"\"\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30c69a1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = query_results(query)\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a21f356-a0e8-4986-8f28-ae80ae630621",
   "metadata": {},
   "source": [
    "## [Credential Compromise Containment Notebook](./credential-compromise-containment.ipynb)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}