workbench/notebooks/PCAP_DriveBy.ipynb

{
 "metadata": {
  "name": ""
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "\n",
      "<div style=\"float: right; margin: 30px 0px 0px 30px\"><img src=\"files/images/driveby.gif\" width=\"350px\"></div>\n",
      "## WIP: DriveBy PCAP Analysis with Workbench:\n",
      "\n",
      "**Context:** The company's Security Information and Event Management (SIEM) system is giving some DNS alerts coming from 'that' guy's computer. They look like that might be DGA (Dynamic Generation Algorithm) based domains so we pull some pcaps to try and quickly find out what's going on.\n",
      "\n",
      "- Please see [DGA Notebook](http://nbviewer.ipython.org/github/ClickSecurity/data_hacking/blob/master/dga_detection/DGA_Domain_Detection.ipynb) for more info on DGAs.\n",
      "\n",
      "**Note:** This notebook was inspired by the data_hacking notebook called [DriveBy PCAP Analysis](http://nbviewer.ipython.org/github/ClickSecurity/data_hacking/blob/master/driveby_pcap_analysis/driveby_pcap_analysis.ipynb). Here we're leveraging the workbench server and focusing the notebook at looking at a specific case captured by [ThreatGlass](http://www.threatglass.com/). The exploited website for this exercise is kitchboss.com.au [ThreatGlass_Info_for_Kitchenboss](http://www.threatglass.com/malicious_urls/60d4098703770bd93c70dbb2f74ba1fb?process_date=2014-04-09) (arbitrarily choosen).\n",
      "\n",
      "**Tools in this Notebook:**\n",
      "\n",
      "- Workbench: Open Source Security Framework [Workbench GitHub](https://github.com/SuperCowPowers/workbench)\n",
      "- Bro Network Security Monitor (http://www.bro.org)\n",
      "- Pandas: Python Data Analysis Library (http://pandas.pydata.org)\n",
      "- Matplotlib: Python 2D plotting library (http://matplotlib.org)\n",
      "\n",
      "Workbench can be setup to utilize several indexers:\n",
      "\n",
      "- Straight up Indexing with [ElasticSearch](http://www.elasticsearch.org) \n",
      "- Super awesome [Neo4j](http://www.neo4j.org) as both an indexer and graph database.\n",
      "\n",
      "Neo4j also incorporates Lucene based indexing so not only can we capture a rich set of relationships between our data entities but searches and queries are super quick.\n",
      "\n",
      "#### Thanks:\n",
      "\n",
      "- **Thanks to Eric Chavez (SeaDawg) for patiently telling me how computers work. Super shout out... :)**\n",
      "- Thanks to [ThreatGlass](http://www.threatglass.com) for providing a great service and website.\n",
      "- Thanks to Mike Sconzo for putting together the original notebook.\n",
      "\n",
      "<div style=\"float: right; margin: 0px 0px 0px 30px\"><img src=\"files/images/workbench.jpg\" width=\"400px\"></div>\n",
      "## Lets start up the workbench server...\n",
      "Run the workbench server (from somewhere, for the demo we're just going to start a local one)\n",
      "<pre>\n",
      "$ workbench_server\n",
      "</pre>\n",
      "\n",
      "#### Okay so when the server starts up, it autoloads any worker plugins in the server/worker directory and dynamically monitors the directory, if a new python file shows up, it's validated as a properly formed plugin and if it passes is added to the list of workers."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Lets start to interact with workbench, please note there is NO specific client to workbench,\n",
      "# Just use the ZeroRPC Python, Node.js, or CLI interfaces.\n",
      "import zerorpc\n",
      "c = zerorpc.Client()\n",
      "c.connect(\"tcp://127.0.0.1:4242\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 10,
       "text": [
        "[None]"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: left; margin: 0px 30px 0px 0px\"><img src=\"files/images/disk.jpg\" width=\"250px\"></div>\n",
      "# Read in the Data\n",
      "<font size=4> The data is pulled from [ThreatGlass](http://www.threatglass.com/), the exploited website for this exercise is kitchboss.com.au [ThreatGlass_Info_for_Kitchenboss](http://www.threatglass.com/malicious_urls/60d4098703770bd93c70dbb2f74ba1fb?process_date=2014-04-09)\n",
      "</font>"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Load in the PCAP file\n",
      "filename = '../data/pcap/kitchen_boss.pcap'\n",
      "with open(filename,'rb') as f:\n",
      "    pcap_md5 = c.store_sample(f.read(), filename, 'pcap')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 11
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: right; margin: 40px 0px 60px 30px\"><img src=\"files/images/bro_tshirt.jpg\" width=\"150px\"></div>\n",
      "## Run the PCAP through Bro\n",
      "Workbench makes running Bro super easy, it manages the PCAPs and the resulting Bro logs. Perhaps most importantly it allows us to pull back specific logs with super awesome client/server generators for the data that we want to analyze.\n",
      "\n",
      "**Note:** we only have one in this case but running sets of PCAPs through Bro is well supported, please see our [Batches_and_Sets](http://nbviewer.ipython.org/github/SuperCowPowers/workbench/blob/master/notebooks/Batches_and_Sets.ipynb) notebook for more infomation."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Run the Bro Network Security Monitor on the PCAP (or set of PCAPS) we just loaded.\n",
      "# We could run several requests here... 'pcap_bro','view_pcap' or 'view_pcap_details',\n",
      "# workbench is super granular and it's easy to try them all and add your own as well\n",
      "output = c.work_request('view_pcap_details', pcap_md5)['view_pcap_details']\n",
      "output"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 13,
       "text": [
        "{'bro_logs': {'conn_log': 'bd20b5e153d44bf43f95838540230200',\n",
        "  'dhcp_log': '55e8aead94d8edf48e636e39c65e846e',\n",
        "  'dns_log': '14873855a02c7e3952bf75cc2cf74d20',\n",
        "  'files_log': 'a3e84dcbfecb881ad81123fe109bf9e8',\n",
        "  'http_log': 'e58c1abb0182e9c48c4e5e37e68fa875',\n",
        "  'packet_filter_log': 'c89d73c9ff2a12b66cdf74007af8a952',\n",
        "  'weird_log': 'e872ecdb518caf4dcddc1044b90962c5'},\n",
        " 'connectionId': 130,\n",
        " 'err': None,\n",
        " 'extracted_files': [{'entropy': 7.889679107924797,\n",
        "   'file_size': 18643,\n",
        "   'file_type': 'Java Jar file data (zip)',\n",
        "   'md5': 'c762b6ba4f560692b6b84ac212cd3ec2',\n",
        "   'sha256': 'c776c5f3b979233c8466fc521e38271bbd59081538e126273fe1a75a228bd25d',\n",
        "   'ssdeep': '384:7SXliKrIvBZFzoceSZNZ2wLk588eHYBAXGIsMeV:AiKrKySZNxg5892SGII'},\n",
        "  {'entropy': 7.515107193655836,\n",
        "   'file_size': 273920,\n",
        "   'file_type': 'PE32 executable (GUI) Intel 80386, for MS Windows',\n",
        "   'md5': '4410133f571476f2e76e29e61767b557',\n",
        "   'sha256': 'e4bbdc8f869502183293797f51d6d64cc6c49d39b82effbcb738abe511054b51',\n",
        "   'ssdeep': '6144:RSxqC+ayi6eWLj622ARbJFMQzynbJDxL3oPlRa:oxqC+ayi6p6EmQz+bf3otA'},\n",
        "  {'entropy': 7.889679107924797,\n",
        "   'file_size': 18643,\n",
        "   'file_type': 'Java Jar file data (zip)',\n",
        "   'md5': 'c762b6ba4f560692b6b84ac212cd3ec2',\n",
        "   'sha256': 'c776c5f3b979233c8466fc521e38271bbd59081538e126273fe1a75a228bd25d',\n",
        "   'ssdeep': '384:7SXliKrIvBZFzoceSZNZ2wLk588eHYBAXGIsMeV:AiKrKySZNxg5892SGII'},\n",
        "  {'entropy': 7.9751436379093406,\n",
        "   'file_size': 7724,\n",
        "   'file_type': 'Macromedia Flash data (compressed), version 9',\n",
        "   'md5': '16cf037b8c8caad6759afc8c309de0f9',\n",
        "   'sha256': '8af130ffe1140894895225e265d5d9a753ad9d85883db742c88f13bae01e2c30',\n",
        "   'ssdeep': '192:v27CdZAdM5nmNvipKRRkLozlrEKcgTtpEV3+5jxChbNT3:v27QZAAVpKRRkLu/cOEa9ORD'},\n",
        "  {'entropy': 7.9751436379093406,\n",
        "   'file_size': 7724,\n",
        "   'file_type': 'Macromedia Flash data (compressed), version 9',\n",
        "   'md5': '16cf037b8c8caad6759afc8c309de0f9',\n",
        "   'sha256': '8af130ffe1140894895225e265d5d9a753ad9d85883db742c88f13bae01e2c30',\n",
        "   'ssdeep': '192:v27CdZAdM5nmNvipKRRkLozlrEKcgTtpEV3+5jxChbNT3:v27QZAAVpKRRkLu/cOEa9ORD'},\n",
        "  {'entropy': 7.889679107924797,\n",
        "   'file_size': 18643,\n",
        "   'file_type': 'Java Jar file data (zip)',\n",
        "   'md5': 'c762b6ba4f560692b6b84ac212cd3ec2',\n",
        "   'sha256': 'c776c5f3b979233c8466fc521e38271bbd59081538e126273fe1a75a228bd25d',\n",
        "   'ssdeep': '384:7SXliKrIvBZFzoceSZNZ2wLk588eHYBAXGIsMeV:AiKrKySZNxg5892SGII'},\n",
        "  {'entropy': 7.889679107924797,\n",
        "   'file_size': 18643,\n",
        "   'file_type': 'Java Jar file data (zip)',\n",
        "   'md5': 'c762b6ba4f560692b6b84ac212cd3ec2',\n",
        "   'sha256': 'c776c5f3b979233c8466fc521e38271bbd59081538e126273fe1a75a228bd25d',\n",
        "   'ssdeep': '384:7SXliKrIvBZFzoceSZNZ2wLk588eHYBAXGIsMeV:AiKrKySZNxg5892SGII'},\n",
        "  {'entropy': 7.889679107924797,\n",
        "   'file_size': 18643,\n",
        "   'file_type': 'Java Jar file data (zip)',\n",
        "   'md5': 'c762b6ba4f560692b6b84ac212cd3ec2',\n",
        "   'sha256': 'c776c5f3b979233c8466fc521e38271bbd59081538e126273fe1a75a228bd25d',\n",
        "   'ssdeep': '384:7SXliKrIvBZFzoceSZNZ2wLk588eHYBAXGIsMeV:AiKrKySZNxg5892SGII'},\n",
        "  {'entropy': 7.284164074455011,\n",
        "   'file_size': 13006,\n",
        "   'file_type': 'PDF document, version 1.6',\n",
        "   'md5': '40b8c3c98f50e078251ec272620dfb5b',\n",
        "   'sha256': '5dac98c66c7440992de9de860dd4312790bcc7bbcbb4aebfdce8b3fdfcfc56af',\n",
        "   'ssdeep': '384:jW3+XiQMTXgazaO/J9m1DfN6abiiYEVB0T0DM:Ph+fJSE2r0j'}],\n",
        " 'md5': 'df4f4a2e2bf020be50a12554942edb88',\n",
        " 'n': 1,\n",
        " 'ok': 1.0,\n",
        " 'updatedExisting': False}"
       ]
      }
     ],
     "prompt_number": 13
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: left; margin: 0px 30px 0px 30px\"><img src=\"files/images/treasure.jpg\" width=\"250px\"></div>\n",
      "## Workbench default mode is to run Bro with file extraction.\n",
      "### We can see that there's quite a few embedded files in this PCAP.\n",
      "\n",
      "- **5 Java JAR files (all the same)**\n",
      "- **2 Flash files (same)**\n",
      "- **1 PE executable file**\n",
      "- **1 PDF file**"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# We'll grab the md5s for those files and do some kewl stuff with them later\n",
      "file_md5s = list(set([item['md5'] for item in output['extracted_files']]))\n",
      "pe_md5 = '4410133f571476f2e76e29e61767b557'\n",
      "file_md5s"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 8,
       "text": [
        "['16cf037b8c8caad6759afc8c309de0f9',\n",
        " '40b8c3c98f50e078251ec272620dfb5b',\n",
        " 'c762b6ba4f560692b6b84ac212cd3ec2',\n",
        " '4410133f571476f2e76e29e61767b557']"
       ]
      }
     ],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Grab the Bro logs that we want\n",
      "dns_log = c.stream_sample(output['bro_logs']['dns_log'])\n",
      "http_log = c.stream_sample(output['bro_logs']['http_log'])\n",
      "files_log = c.stream_sample(output['bro_logs']['files_log'])\n",
      "dns_log"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 9,
       "text": [
        "<generator object iterator at 0x107e46d70>"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: left; margin: 0px 0px 0px 30px\"><img src=\"files/images/head_explode.jpg\" width=\"350px\"></div>\n",
      "## Yep they are generators\n",
      "#### Yes generators are awesome but getting one from a server request! Are u serious?!  Yes, dead serious.. like chopping off your head and kicking your body into a shallow grave and putting your head on a stick... serious.\n",
      "\n",
      "#### For more on client/server generators and client-contructed/server-executed generator pipelines see our super spiffy [Generator Pipelines](http://nbviewer.ipython.org/url/raw.github.com/SuperCowPowers/workbench/master/notebooks/Generator_Pipelines.ipynb) notebook.\n",
      "\n",
      "#### Now that we have a server generator from workbench we can push it into a Pandas Dataframe without a copy, fast and memory efficient..."
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: right; padding: 0px 0px 0px 30px;\"><img src=\"files/images/transformers.png\" width=\"200px\"></div>\n",
      "### Data Transformation: One line of code to put workbench output into a Pandas Dataframe!\n",
      "Putting our data into a Pandas Dataframe opens up a new world and enables tons of functionality for data, temporal and statistical analysis.\n",
      "<pre>df = pd.DataFrame(output)</pre>"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "\n",
      "# Okay take the generators returned by stream_sample and efficiently create dataframes\n",
      "# LIKE BUTTER I TELL YOU!\n",
      "dns_df = pd.DataFrame(dns_log)\n",
      "http_df = pd.DataFrame(http_log)\n",
      "files_df = pd.DataFrame(files_log)\n",
      "files_df.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>analyzers</th>\n",
        "      <th>conn_uids</th>\n",
        "      <th>depth</th>\n",
        "      <th>duration</th>\n",
        "      <th>extracted</th>\n",
        "      <th>filename</th>\n",
        "      <th>fuid</th>\n",
        "      <th>is_orig</th>\n",
        "      <th>local_orig</th>\n",
        "      <th>md5</th>\n",
        "      <th>mime_type</th>\n",
        "      <th>missing_bytes</th>\n",
        "      <th>overflow_bytes</th>\n",
        "      <th>parent_fuid</th>\n",
        "      <th>rx_hosts</th>\n",
        "      <th>seen_bytes</th>\n",
        "      <th>sha1</th>\n",
        "      <th>sha256</th>\n",
        "      <th>source</th>\n",
        "      <th>timedout</th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> SHA256,MD5,SHA1</td>\n",
        "      <td> CBsm7k2L1ReG6MzFbj</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0.168808</td>\n",
        "      <td> -</td>\n",
        "      <td> -</td>\n",
        "      <td>  FL607G2jztRHr8Xbz</td>\n",
        "      <td> F</td>\n",
        "      <td> -</td>\n",
        "      <td> bfd039047ebd33f25ffe16d7832d6ceb</td>\n",
        "      <td>  text/html</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> -</td>\n",
        "      <td> 192.168.22.10</td>\n",
        "      <td> 13944</td>\n",
        "      <td> 29fba8185043e6c3748ce6c85f9b1b70fe9c4326</td>\n",
        "      <td> e7517bd0b1654151b5e7284bb8e8b9ec49ce411ff5e1f1...</td>\n",
        "      <td> HTTP</td>\n",
        "      <td> F</td>\n",
        "      <td>...</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> SHA256,MD5,SHA1</td>\n",
        "      <td> Cz5NkR1gnqOMWUSbB8</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0.000000</td>\n",
        "      <td> -</td>\n",
        "      <td> -</td>\n",
        "      <td> FH1gD44gUcIfJgq2J2</td>\n",
        "      <td> F</td>\n",
        "      <td> -</td>\n",
        "      <td> 14625ee5228c694cf0767e09d12a8d1f</td>\n",
        "      <td> text/plain</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> -</td>\n",
        "      <td> 192.168.22.10</td>\n",
        "      <td>    98</td>\n",
        "      <td> 5676b357553c6d55f3361dbfab460e3268cb3b55</td>\n",
        "      <td> dfaa8766fad53785e137643e5c685926338f274ec21508...</td>\n",
        "      <td> HTTP</td>\n",
        "      <td> F</td>\n",
        "      <td>...</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> SHA256,MD5,SHA1</td>\n",
        "      <td>  CnHfPx8bQQg2aeWo5</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0.000056</td>\n",
        "      <td> -</td>\n",
        "      <td> -</td>\n",
        "      <td>  FC8A2X2Jla1xMWhr8</td>\n",
        "      <td> F</td>\n",
        "      <td> -</td>\n",
        "      <td> 892a543f3abb54e8ec1ada55be3b0649</td>\n",
        "      <td> text/plain</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> -</td>\n",
        "      <td> 192.168.22.10</td>\n",
        "      <td> 10220</td>\n",
        "      <td> 5847ed101f55d51c53538a7078971e7de8fb6762</td>\n",
        "      <td> 8677971b119ccdb82af697ff0e08f218490d15116f221d...</td>\n",
        "      <td> HTTP</td>\n",
        "      <td> F</td>\n",
        "      <td>...</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> SHA256,MD5,SHA1</td>\n",
        "      <td> CQUPQx2b0CMrWv1Dag</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0.000000</td>\n",
        "      <td> -</td>\n",
        "      <td> -</td>\n",
        "      <td> F3fwSC4WmD0PKQ6HHg</td>\n",
        "      <td> F</td>\n",
        "      <td> -</td>\n",
        "      <td> 0ce8f355891c26c28f057e195e97dcd5</td>\n",
        "      <td>  text/html</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> -</td>\n",
        "      <td> 192.168.22.10</td>\n",
        "      <td>  2429</td>\n",
        "      <td> 3c7b369485cadd585d24be44701e459c8aa54d60</td>\n",
        "      <td> 8c7a9c0470563367ab00307b4fb9bb3052d0a27f0b94e6...</td>\n",
        "      <td> HTTP</td>\n",
        "      <td> F</td>\n",
        "      <td>...</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> SHA256,MD5,SHA1</td>\n",
        "      <td> CnB5Oj1YktY5UFZu3k</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0.000047</td>\n",
        "      <td> -</td>\n",
        "      <td> -</td>\n",
        "      <td> FLK1KW2a1Bwh6FE6hg</td>\n",
        "      <td> F</td>\n",
        "      <td> -</td>\n",
        "      <td> 641cad6161527eb7cdabd4485637634e</td>\n",
        "      <td>  text/html</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> -</td>\n",
        "      <td> 192.168.22.10</td>\n",
        "      <td>  4022</td>\n",
        "      <td> 4bc9306998175f909b167734dad41bd5a6589c82</td>\n",
        "      <td> 97b0566bfad0e84bc0eb0db538e66b5dc103a878eb142e...</td>\n",
        "      <td> HTTP</td>\n",
        "      <td> F</td>\n",
        "      <td>...</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>5 rows \u00d7 23 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 10,
       "text": [
        "         analyzers           conn_uids  depth  duration extracted filename  \\\n",
        "0  SHA256,MD5,SHA1  CBsm7k2L1ReG6MzFbj      0  0.168808         -        -   \n",
        "1  SHA256,MD5,SHA1  Cz5NkR1gnqOMWUSbB8      0  0.000000         -        -   \n",
        "2  SHA256,MD5,SHA1   CnHfPx8bQQg2aeWo5      0  0.000056         -        -   \n",
        "3  SHA256,MD5,SHA1  CQUPQx2b0CMrWv1Dag      0  0.000000         -        -   \n",
        "4  SHA256,MD5,SHA1  CnB5Oj1YktY5UFZu3k      0  0.000047         -        -   \n",
        "\n",
        "                 fuid is_orig local_orig                               md5  \\\n",
        "0   FL607G2jztRHr8Xbz       F          -  bfd039047ebd33f25ffe16d7832d6ceb   \n",
        "1  FH1gD44gUcIfJgq2J2       F          -  14625ee5228c694cf0767e09d12a8d1f   \n",
        "2   FC8A2X2Jla1xMWhr8       F          -  892a543f3abb54e8ec1ada55be3b0649   \n",
        "3  F3fwSC4WmD0PKQ6HHg       F          -  0ce8f355891c26c28f057e195e97dcd5   \n",
        "4  FLK1KW2a1Bwh6FE6hg       F          -  641cad6161527eb7cdabd4485637634e   \n",
        "\n",
        "    mime_type  missing_bytes  overflow_bytes parent_fuid       rx_hosts  \\\n",
        "0   text/html              0               0           -  192.168.22.10   \n",
        "1  text/plain              0               0           -  192.168.22.10   \n",
        "2  text/plain              0               0           -  192.168.22.10   \n",
        "3   text/html              0               0           -  192.168.22.10   \n",
        "4   text/html              0               0           -  192.168.22.10   \n",
        "\n",
        "   seen_bytes                                      sha1  \\\n",
        "0       13944  29fba8185043e6c3748ce6c85f9b1b70fe9c4326   \n",
        "1          98  5676b357553c6d55f3361dbfab460e3268cb3b55   \n",
        "2       10220  5847ed101f55d51c53538a7078971e7de8fb6762   \n",
        "3        2429  3c7b369485cadd585d24be44701e459c8aa54d60   \n",
        "4        4022  4bc9306998175f909b167734dad41bd5a6589c82   \n",
        "\n",
        "                                              sha256 source timedout      \n",
        "0  e7517bd0b1654151b5e7284bb8e8b9ec49ce411ff5e1f1...   HTTP        F ...  \n",
        "1  dfaa8766fad53785e137643e5c685926338f274ec21508...   HTTP        F ...  \n",
        "2  8677971b119ccdb82af697ff0e08f218490d15116f221d...   HTTP        F ...  \n",
        "3  8c7a9c0470563367ab00307b4fb9bb3052d0a27f0b94e6...   HTTP        F ...  \n",
        "4  97b0566bfad0e84bc0eb0db538e66b5dc103a878eb142e...   HTTP        F ...  \n",
        "\n",
        "[5 rows x 23 columns]"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: left; margin: 0px 0px 0px 0px\"><img src=\"files/images/confused.jpg\" width=\"350px\"></div>\n",
      "\n",
      "## So I'm confused what just happened? \n",
      "\n",
      "- We sent a PCAP file to the workbench server\n",
      "- Workbench stores it in a database (MongoDB now, maybe Vertica later)\n",
      "- We made a work request 'view_pcap_details' on the PCAP (runs Bro and other stuff)\n",
      "- We pulled back just the parts of the Bro output that we specifically wanted\n",
      "- We got a set of client/server generators, we populated Pandas dataframes\n",
      "- **In like a dozen lines of python!**\n",
      "\n",
      "** Image below shows the Workbench database, each worker stores data in a separate collection. The data is transparent, organized and accessible **\n",
      "<div style=\"float: right; margin: 0px 50px 0px 0px\"><img src=\"files/images/mongo_data.png\" width=\"700px\"></div>\n",
      "\n"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: left; margin: 0px 30px 0px 0px\"><img src=\"files/images/eyeball_2.jpg\" width=\"250px\"></div>\n",
      "# Lets look at the DNS Data\n",
      "Given that this exercise started because the SIEM was flagging DNS traffic, we start there. Now that our Bro log data has been streamed into a Pandas Dataframe we can do all kinds of wonderful things. See [Pandas](http://pandas.pydata.org/) to behold the awesome."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dns_df.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>AA</th>\n",
        "      <th>RA</th>\n",
        "      <th>RD</th>\n",
        "      <th>TC</th>\n",
        "      <th>TTLs</th>\n",
        "      <th>Z</th>\n",
        "      <th>answers</th>\n",
        "      <th>id.orig_h</th>\n",
        "      <th>id.orig_p</th>\n",
        "      <th>id.resp_h</th>\n",
        "      <th>id.resp_p</th>\n",
        "      <th>proto</th>\n",
        "      <th>qclass</th>\n",
        "      <th>qclass_name</th>\n",
        "      <th>qtype</th>\n",
        "      <th>qtype_name</th>\n",
        "      <th>query</th>\n",
        "      <th>rcode</th>\n",
        "      <th>rcode_name</th>\n",
        "      <th>rejected</th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> F</td>\n",
        "      <td> T</td>\n",
        "      <td> T</td>\n",
        "      <td> F</td>\n",
        "      <td>                     14372</td>\n",
        "      <td> 0</td>\n",
        "      <td>                                   111.223.225.83</td>\n",
        "      <td> 192.168.22.10</td>\n",
        "      <td> 1035</td>\n",
        "      <td> 4.2.2.3</td>\n",
        "      <td> 53</td>\n",
        "      <td> udp</td>\n",
        "      <td> 1</td>\n",
        "      <td> C_INTERNET</td>\n",
        "      <td> 1</td>\n",
        "      <td> A</td>\n",
        "      <td>       kitchenboss.com.au</td>\n",
        "      <td> 0</td>\n",
        "      <td> NOERROR</td>\n",
        "      <td> F</td>\n",
        "      <td>...</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> F</td>\n",
        "      <td> T</td>\n",
        "      <td> T</td>\n",
        "      <td> F</td>\n",
        "      <td> 14371.000000,14371.000000</td>\n",
        "      <td> 0</td>\n",
        "      <td>                kitchenboss.com.au,111.223.225.83</td>\n",
        "      <td> 192.168.22.10</td>\n",
        "      <td> 1035</td>\n",
        "      <td> 4.2.2.3</td>\n",
        "      <td> 53</td>\n",
        "      <td> udp</td>\n",
        "      <td> 1</td>\n",
        "      <td> C_INTERNET</td>\n",
        "      <td> 1</td>\n",
        "      <td> A</td>\n",
        "      <td>   www.kitchenboss.com.au</td>\n",
        "      <td> 0</td>\n",
        "      <td> NOERROR</td>\n",
        "      <td> F</td>\n",
        "      <td>...</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> F</td>\n",
        "      <td> T</td>\n",
        "      <td> T</td>\n",
        "      <td> F</td>\n",
        "      <td>    2601.000000,128.000000</td>\n",
        "      <td> 0</td>\n",
        "      <td>            googleapis.l.google.com,74.125.128.95</td>\n",
        "      <td> 192.168.22.10</td>\n",
        "      <td> 1035</td>\n",
        "      <td> 4.2.2.3</td>\n",
        "      <td> 53</td>\n",
        "      <td> udp</td>\n",
        "      <td> 1</td>\n",
        "      <td> C_INTERNET</td>\n",
        "      <td> 1</td>\n",
        "      <td> A</td>\n",
        "      <td>     fonts.googleapis.com</td>\n",
        "      <td> 0</td>\n",
        "      <td> NOERROR</td>\n",
        "      <td> F</td>\n",
        "      <td>...</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> F</td>\n",
        "      <td> T</td>\n",
        "      <td> T</td>\n",
        "      <td> F</td>\n",
        "      <td>    2587.000000,128.000000</td>\n",
        "      <td> 0</td>\n",
        "      <td>            googleapis.l.google.com,74.125.128.95</td>\n",
        "      <td> 192.168.22.10</td>\n",
        "      <td> 1035</td>\n",
        "      <td> 4.2.2.3</td>\n",
        "      <td> 53</td>\n",
        "      <td> udp</td>\n",
        "      <td> 1</td>\n",
        "      <td> C_INTERNET</td>\n",
        "      <td> 1</td>\n",
        "      <td> A</td>\n",
        "      <td>      ajax.googleapis.com</td>\n",
        "      <td> 0</td>\n",
        "      <td> NOERROR</td>\n",
        "      <td> F</td>\n",
        "      <td>...</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> F</td>\n",
        "      <td> T</td>\n",
        "      <td> T</td>\n",
        "      <td> F</td>\n",
        "      <td>   36507.000000,271.000000</td>\n",
        "      <td> 0</td>\n",
        "      <td> googlecode.l.googleusercontent.com,74.125.128.82</td>\n",
        "      <td> 192.168.22.10</td>\n",
        "      <td> 1042</td>\n",
        "      <td> 4.2.2.3</td>\n",
        "      <td> 53</td>\n",
        "      <td> udp</td>\n",
        "      <td> 1</td>\n",
        "      <td> C_INTERNET</td>\n",
        "      <td> 1</td>\n",
        "      <td> A</td>\n",
        "      <td> html5shim.googlecode.com</td>\n",
        "      <td> 0</td>\n",
        "      <td> NOERROR</td>\n",
        "      <td> F</td>\n",
        "      <td>...</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>5 rows \u00d7 23 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 11,
       "text": [
        "  AA RA RD TC                       TTLs  Z  \\\n",
        "0  F  T  T  F                      14372  0   \n",
        "1  F  T  T  F  14371.000000,14371.000000  0   \n",
        "2  F  T  T  F     2601.000000,128.000000  0   \n",
        "3  F  T  T  F     2587.000000,128.000000  0   \n",
        "4  F  T  T  F    36507.000000,271.000000  0   \n",
        "\n",
        "                                            answers      id.orig_h  id.orig_p  \\\n",
        "0                                    111.223.225.83  192.168.22.10       1035   \n",
        "1                 kitchenboss.com.au,111.223.225.83  192.168.22.10       1035   \n",
        "2             googleapis.l.google.com,74.125.128.95  192.168.22.10       1035   \n",
        "3             googleapis.l.google.com,74.125.128.95  192.168.22.10       1035   \n",
        "4  googlecode.l.googleusercontent.com,74.125.128.82  192.168.22.10       1042   \n",
        "\n",
        "  id.resp_h  id.resp_p proto  qclass qclass_name  qtype qtype_name  \\\n",
        "0   4.2.2.3         53   udp       1  C_INTERNET      1          A   \n",
        "1   4.2.2.3         53   udp       1  C_INTERNET      1          A   \n",
        "2   4.2.2.3         53   udp       1  C_INTERNET      1          A   \n",
        "3   4.2.2.3         53   udp       1  C_INTERNET      1          A   \n",
        "4   4.2.2.3         53   udp       1  C_INTERNET      1          A   \n",
        "\n",
        "                      query  rcode rcode_name rejected      \n",
        "0        kitchenboss.com.au      0    NOERROR        F ...  \n",
        "1    www.kitchenboss.com.au      0    NOERROR        F ...  \n",
        "2      fonts.googleapis.com      0    NOERROR        F ...  \n",
        "3       ajax.googleapis.com      0    NOERROR        F ...  \n",
        "4  html5shim.googlecode.com      0    NOERROR        F ...  \n",
        "\n",
        "[5 rows x 23 columns]"
       ]
      }
     ],
     "prompt_number": 11
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dns_df[['query','answers','qtype_name']]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>query</th>\n",
        "      <th>answers</th>\n",
        "      <th>qtype_name</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>time</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:38:54.505653</th>\n",
        "      <td>                   kitchenboss.com.au</td>\n",
        "      <td>                                    111.223.225.83</td>\n",
        "      <td>   A</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:38:55.370111</th>\n",
        "      <td>               www.kitchenboss.com.au</td>\n",
        "      <td>                 kitchenboss.com.au,111.223.225.83</td>\n",
        "      <td>   A</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:38:56.773263</th>\n",
        "      <td>                 fonts.googleapis.com</td>\n",
        "      <td>             googleapis.l.google.com,74.125.128.95</td>\n",
        "      <td>   A</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:38:56.814285</th>\n",
        "      <td>                  ajax.googleapis.com</td>\n",
        "      <td>             googleapis.l.google.com,74.125.128.95</td>\n",
        "      <td>   A</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:38:56.816699</th>\n",
        "      <td>             html5shim.googlecode.com</td>\n",
        "      <td>  googlecode.l.googleusercontent.com,74.125.128.82</td>\n",
        "      <td>   A</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:38:58.021865</th>\n",
        "      <td>         themes.googleusercontent.com</td>\n",
        "      <td> googlehosted.l.googleusercontent.com,173.194.1...</td>\n",
        "      <td>   A</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:38:58.874743</th>\n",
        "      <td>             www.google-analytics.com</td>\n",
        "      <td> www-google-analytics.l.google.com,74.125.128.1...</td>\n",
        "      <td>   A</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:00.805155</th>\n",
        "      <td>           fpdownload2.macromedia.com</td>\n",
        "      <td> fpdownload2.wip4.adobe.com,fpdownload.macromed...</td>\n",
        "      <td>   A</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:00.709656</th>\n",
        "      <td>                  advertdedicated.com</td>\n",
        "      <td>                                    217.12.199.174</td>\n",
        "      <td>   A</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:02.607899</th>\n",
        "      <td> p22x62n0yr63872e-qh6.focondteavrt.ru</td>\n",
        "      <td>                                      142.4.194.92</td>\n",
        "      <td>   A</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:07.436915</th>\n",
        "      <td>         2496128308-6.focondteavrt.ru</td>\n",
        "      <td>                                      142.4.194.92</td>\n",
        "      <td>   A</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:10.103892</th>\n",
        "      <td>            92.194.4.142.in-addr.arpa</td>\n",
        "      <td>                                                 -</td>\n",
        "      <td> PTR</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>12 rows \u00d7 3 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 61,
       "text": [
        "                                                           query  \\\n",
        "time                                                               \n",
        "2014-04-09 06:38:54.505653                    kitchenboss.com.au   \n",
        "2014-04-09 06:38:55.370111                www.kitchenboss.com.au   \n",
        "2014-04-09 06:38:56.773263                  fonts.googleapis.com   \n",
        "2014-04-09 06:38:56.814285                   ajax.googleapis.com   \n",
        "2014-04-09 06:38:56.816699              html5shim.googlecode.com   \n",
        "2014-04-09 06:38:58.021865          themes.googleusercontent.com   \n",
        "2014-04-09 06:38:58.874743              www.google-analytics.com   \n",
        "2014-04-09 06:39:00.805155            fpdownload2.macromedia.com   \n",
        "2014-04-09 06:39:00.709656                   advertdedicated.com   \n",
        "2014-04-09 06:39:02.607899  p22x62n0yr63872e-qh6.focondteavrt.ru   \n",
        "2014-04-09 06:39:07.436915          2496128308-6.focondteavrt.ru   \n",
        "2014-04-09 06:39:10.103892             92.194.4.142.in-addr.arpa   \n",
        "\n",
        "                                                                      answers  \\\n",
        "time                                                                            \n",
        "2014-04-09 06:38:54.505653                                     111.223.225.83   \n",
        "2014-04-09 06:38:55.370111                  kitchenboss.com.au,111.223.225.83   \n",
        "2014-04-09 06:38:56.773263              googleapis.l.google.com,74.125.128.95   \n",
        "2014-04-09 06:38:56.814285              googleapis.l.google.com,74.125.128.95   \n",
        "2014-04-09 06:38:56.816699   googlecode.l.googleusercontent.com,74.125.128.82   \n",
        "2014-04-09 06:38:58.021865  googlehosted.l.googleusercontent.com,173.194.1...   \n",
        "2014-04-09 06:38:58.874743  www-google-analytics.l.google.com,74.125.128.1...   \n",
        "2014-04-09 06:39:00.805155  fpdownload2.wip4.adobe.com,fpdownload.macromed...   \n",
        "2014-04-09 06:39:00.709656                                     217.12.199.174   \n",
        "2014-04-09 06:39:02.607899                                       142.4.194.92   \n",
        "2014-04-09 06:39:07.436915                                       142.4.194.92   \n",
        "2014-04-09 06:39:10.103892                                                  -   \n",
        "\n",
        "                           qtype_name  \n",
        "time                                   \n",
        "2014-04-09 06:38:54.505653          A  \n",
        "2014-04-09 06:38:55.370111          A  \n",
        "2014-04-09 06:38:56.773263          A  \n",
        "2014-04-09 06:38:56.814285          A  \n",
        "2014-04-09 06:38:56.816699          A  \n",
        "2014-04-09 06:38:58.021865          A  \n",
        "2014-04-09 06:38:58.874743          A  \n",
        "2014-04-09 06:39:00.805155          A  \n",
        "2014-04-09 06:39:00.709656          A  \n",
        "2014-04-09 06:39:02.607899          A  \n",
        "2014-04-09 06:39:07.436915          A  \n",
        "2014-04-09 06:39:10.103892        PTR  \n",
        "\n",
        "[12 rows x 3 columns]"
       ]
      }
     ],
     "prompt_number": 61
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: left; margin: 0px 30px 0px 30px\"><img src=\"files/images/anxious.jpg\" width=\"200px\"></div>\n",
      "## Two things make us nervous about this list\n",
      "\n",
      "- They RU domains look like they might be DGA domains [DGA Notebook](http://nbviewer.ipython.org/github/ClickSecurity/data_hacking/blob/master/dga_detection/DGA_Domain_Detection.ipynb)\n",
      "- Also it's a bit weird that the PTR queries didn't come back. [Wikipedia: Forward_Confirmed_reverse_DNS](http://en.wikipedia.org/wiki/Forward_Confirmed_reverse_DNS) has the following: 'Common DNS misconfigurations are outlined in RFC 1912, of particular note is section 2.1 that states, under the heading \"Inconsistent, Missing or Bad Data\", \"Make sure your PTR and A records match\".'"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: left; margin: 0px 30px 0px 0px\"><img src=\"files/images/eyeball_2.jpg\" width=\"250px\"></div>\n",
      "# Lets look at the HTTP Data\n",
      "We want to look at the HTTP data to see what kind of data was transferred from the domains of interest. Now that our Bro log data has been streamed into a Pandas Dataframe we can do all kinds of wonderful things. See [Pandas](http://pandas.pydata.org/) to behold the awesome."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Now we group by host and show the different response mime types for each host\n",
      "group_host = http_df.groupby(['host','uid','resp_mime_types','uri'])[['response_body_len']].sum()\n",
      "group_host.head(10)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th>response_body_len</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>host</th>\n",
        "      <th>uid</th>\n",
        "      <th>resp_mime_types</th>\n",
        "      <th>uri</th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th rowspan=\"5\" valign=\"top\">2496128308-6.focondteavrt.ru</th>\n",
        "      <th rowspan=\"3\" valign=\"top\">C430GXDwsHcaJQOJa</th>\n",
        "      <th>-</th>\n",
        "      <th>/f/1397004360/2/2</th>\n",
        "      <td>      0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>application/jar</th>\n",
        "      <th>/1397004360.jar</th>\n",
        "      <td>  37286</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>application/x-dosexec</th>\n",
        "      <th>/f/1397004360/2</th>\n",
        "      <td> 273920</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>CqcNvW15fd2wz3n943</th>\n",
        "      <th>application/jar</th>\n",
        "      <th>/1397004360.jar</th>\n",
        "      <td>  55929</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Csbo4X2kd5yinra0Df</th>\n",
        "      <th>application/pdf</th>\n",
        "      <th>/1397004360.pdf</th>\n",
        "      <td>  13006</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>advertdedicated.com</th>\n",
        "      <th>CAPqz5JkJVfWC7h6</th>\n",
        "      <th>text/plain</th>\n",
        "      <th>/jQuery.js?id=AJAX&amp;PID=1i&amp;cache=91938.89965358726</th>\n",
        "      <td>  13160</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"4\" valign=\"top\">ajax.googleapis.com</th>\n",
        "      <th>C0jJNB2DFc48BN6IUe</th>\n",
        "      <th>text/plain</th>\n",
        "      <th>/ajax/libs/swfobject/2.2/swfobject.js</th>\n",
        "      <td>  10220</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>C2Oaxl1r99xWV2Du</th>\n",
        "      <th>-</th>\n",
        "      <th>/ajax/libs/jquery/1.8.3/jquery.min.js</th>\n",
        "      <td>      0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>CKE2fs4wR6cxeEpx2g</th>\n",
        "      <th>text/plain</th>\n",
        "      <th>/ajax/libs/jquery/1.8.3/jquery.min.js</th>\n",
        "      <td>  93637</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>CecMiUQirdvhCW0ja</th>\n",
        "      <th>-</th>\n",
        "      <th>/ajax/libs/swfobject/2.2/swfobject.js</th>\n",
        "      <td>      0</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>10 rows \u00d7 1 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 27,
       "text": [
        "                                                                                                                         response_body_len\n",
        "host                         uid                resp_mime_types       uri                                                                 \n",
        "2496128308-6.focondteavrt.ru C430GXDwsHcaJQOJa  -                     /f/1397004360/2/2                                                  0\n",
        "                                                application/jar       /1397004360.jar                                                37286\n",
        "                                                application/x-dosexec /f/1397004360/2                                               273920\n",
        "                             CqcNvW15fd2wz3n943 application/jar       /1397004360.jar                                                55929\n",
        "                             Csbo4X2kd5yinra0Df application/pdf       /1397004360.pdf                                                13006\n",
        "advertdedicated.com          CAPqz5JkJVfWC7h6   text/plain            /jQuery.js?id=AJAX&PID=1i&cache=91938.89965358726              13160\n",
        "ajax.googleapis.com          C0jJNB2DFc48BN6IUe text/plain            /ajax/libs/swfobject/2.2/swfobject.js                          10220\n",
        "                             C2Oaxl1r99xWV2Du   -                     /ajax/libs/jquery/1.8.3/jquery.min.js                              0\n",
        "                             CKE2fs4wR6cxeEpx2g text/plain            /ajax/libs/jquery/1.8.3/jquery.min.js                          93637\n",
        "                             CecMiUQirdvhCW0ja  -                     /ajax/libs/swfobject/2.2/swfobject.js                              0\n",
        "\n",
        "[10 rows x 1 columns]"
       ]
      }
     ],
     "prompt_number": 27
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: left; margin: 0px 30px 0px 30px\"><img src=\"files/images/anxious.jpg\" width=\"200px\"></div>\n",
      "## Okay so now that we're looking at the http data we're not feeling any better\n",
      "\n",
      "- The grouped dataframe above shows clearly that four files were downloaded from the '2496128308-6.focondteavrt.ru' host. \n",
      "- A JAR file (twice), a PE file and a PDF file. In fact the JAR/PE file combination was pulled in the same http connection (highly suspicious).\n",
      "- Also the URIs look super odd, they all match like they are part of some 'attack bundle'."
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: left; margin: 0px 30px 0px 0px\"><img src=\"files/images/eyeball_2.jpg\" width=\"250px\"></div>\n",
      "# Now lets look at the Files Data\n",
      "Okay last but certainly not least we want to do a deep dive into the files that were downloaded to the computer, so we pull out the list of md5 that we saved earlier in the notebook from the workbench 'view_pcap_details' output. Note that the batch request below also returns a client/server generator (see [Generator_Pipelines Notebook](http://nbviewer.ipython.org/github/SuperCowPowers/workbench/blob/master/notebooks/Generator_Pipelines.ipynb) for more information).\n",
      "\n",
      "**Note:** Workbench is in desperate need of workers for PDF, SWF, and JAR files. If you'd like to contribute please contact briford@supercowpowers.com :)  \n",
      "\n",
      "For now we'll just look at some Virus Total results and take a quick peek at the SWF and PE files."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Get Meta-data for each of the extracted files from the PCAP\n",
      "file_views = c.batch_work_request('meta_deep',{'md5_list':file_md5s})\n",
      "[view for view in file_views]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 28,
       "text": [
        "[{'encoding': 'binary',\n",
        "  'entropy': 7.9751436379093406,\n",
        "  'file_size': 7724,\n",
        "  'file_type': 'Macromedia Flash data (compressed), version 9',\n",
        "  'filename': 'HTTP-FhsNCh3n8L16lFIiXe.swf',\n",
        "  'import_time': '2014-04-15T16:34:48.142000Z',\n",
        "  'md5': '16cf037b8c8caad6759afc8c309de0f9',\n",
        "  'mime_type': 'application/x-shockwave-flash',\n",
        "  'sha1': 'e60071cf2e1460c26449f3a464ef8861043146ed',\n",
        "  'sha256': '8af130ffe1140894895225e265d5d9a753ad9d85883db742c88f13bae01e2c30',\n",
        "  'ssdeep': '192:v27CdZAdM5nmNvipKRRkLozlrEKcgTtpEV3+5jxChbNT3:v27QZAAVpKRRkLu/cOEa9ORD',\n",
        "  'type_tag': 'swf'},\n",
        " {'encoding': 'binary',\n",
        "  'entropy': 7.284164074455011,\n",
        "  'file_size': 13006,\n",
        "  'file_type': 'PDF document, version 1.6',\n",
        "  'filename': 'HTTP-FwHJvw10MLj8tTj9O8.pdf',\n",
        "  'import_time': '2014-04-15T16:34:48.153000Z',\n",
        "  'md5': '40b8c3c98f50e078251ec272620dfb5b',\n",
        "  'mime_type': 'application/pdf',\n",
        "  'sha1': '1200d521f453fe03596a50cfc401963f3fd15d76',\n",
        "  'sha256': '5dac98c66c7440992de9de860dd4312790bcc7bbcbb4aebfdce8b3fdfcfc56af',\n",
        "  'ssdeep': '384:jW3+XiQMTXgazaO/J9m1DfN6abiiYEVB0T0DM:Ph+fJSE2r0j',\n",
        "  'type_tag': 'pdf'},\n",
        " {'encoding': 'binary',\n",
        "  'entropy': 7.889679107924797,\n",
        "  'file_size': 18643,\n",
        "  'file_type': 'Java Jar file data (zip)',\n",
        "  'filename': 'HTTP-F5XuvS22aJWC2yHiRl.jar',\n",
        "  'import_time': '2014-04-15T16:34:48.128000Z',\n",
        "  'md5': 'c762b6ba4f560692b6b84ac212cd3ec2',\n",
        "  'mime_type': 'application/jar',\n",
        "  'sha1': '81721697e7d4538137bab8efd7e29b4003694294',\n",
        "  'sha256': 'c776c5f3b979233c8466fc521e38271bbd59081538e126273fe1a75a228bd25d',\n",
        "  'ssdeep': '384:7SXliKrIvBZFzoceSZNZ2wLk588eHYBAXGIsMeV:AiKrKySZNxg5892SGII',\n",
        "  'type_tag': 'jar'},\n",
        " {'encoding': 'binary',\n",
        "  'entropy': 7.515107193655836,\n",
        "  'file_size': 273920,\n",
        "  'file_type': 'PE32 executable (GUI) Intel 80386, for MS Windows',\n",
        "  'filename': 'HTTP-F6daIS1TRjI9X6r873.exe',\n",
        "  'import_time': '2014-04-15T16:34:48.135000Z',\n",
        "  'md5': '4410133f571476f2e76e29e61767b557',\n",
        "  'mime_type': 'application/x-dosexec',\n",
        "  'sha1': '035db69cc80fc56717a42646911d9aa95b2ff39e',\n",
        "  'sha256': 'e4bbdc8f869502183293797f51d6d64cc6c49d39b82effbcb738abe511054b51',\n",
        "  'ssdeep': '6144:RSxqC+ayi6eWLj622ARbJFMQzynbJDxL3oPlRa:oxqC+ayi6p6EmQz+bf3otA',\n",
        "  'type_tag': 'exe'}]"
       ]
      }
     ],
     "prompt_number": 28
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Virus Total Queries (as of 4-20-2014)\n",
      "vt_output = c.batch_work_request('vt_query', {'md5_list':file_md5s})\n",
      "[output for output in vt_output]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 29,
       "text": [
        "[{'file_type': 'Macromedia Flash data (compressed), version 9',\n",
        "  'md5': '16cf037b8c8caad6759afc8c309de0f9',\n",
        "  'positives': 0,\n",
        "  'scan_date': '2014-04-14 21:15:29',\n",
        "  'scan_results': [],\n",
        "  'total': 51},\n",
        " {'file_type': 'PDF document, version 1.6',\n",
        "  'md5': '40b8c3c98f50e078251ec272620dfb5b',\n",
        "  'not_found': True},\n",
        " {'file_type': 'Java Jar file data (zip)',\n",
        "  'md5': 'c762b6ba4f560692b6b84ac212cd3ec2',\n",
        "  'positives': 11,\n",
        "  'scan_date': '2014-04-11 16:29:27',\n",
        "  'scan_results': [['Exploit-FUG!C762B6BA4F56', 2],\n",
        "   ['TROJ_GEN.F47V0408', 1],\n",
        "   ['Exploit:Java/CVE-2012-1723', 1],\n",
        "   ['UnclassifiedMalware', 1],\n",
        "   ['Exploit.Zip.CVE20121723.crxrbn', 1]],\n",
        "  'total': 50},\n",
        " {'file_type': 'PE32 executable (GUI) Intel 80386, for MS Windows',\n",
        "  'md5': '4410133f571476f2e76e29e61767b557',\n",
        "  'not_found': True}]"
       ]
      }
     ],
     "prompt_number": 29
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Well VirusTotal only found two of the files (SWF and JAR). The SWF has\n",
      "# zero positives (we're going to take that with a grain of salt). The PDF\n",
      "# and PE files don't even show up. So we'll take a closer look at the SWF\n",
      "# and PE file with some of the workers in workbench.\n",
      "swf_view = c.work_request('swf_meta','16cf037b8c8caad6759afc8c309de0f9')\n",
      "swf_view"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 30,
       "text": [
        "{'swf_meta': {'compressed': True,\n",
        "  'encoding': 'binary',\n",
        "  'file_length': 13215,\n",
        "  'file_size': 7724,\n",
        "  'file_type': 'Macromedia Flash data (compressed), version 9',\n",
        "  'filename': 'HTTP-FhsNCh3n8L16lFIiXe.swf',\n",
        "  'frame_count': 1,\n",
        "  'frame_rate': 20.0,\n",
        "  'frame_size': '[xmin: 0 xmax: 300 ymin: 0 ymax: 250]',\n",
        "  'import_time': '2014-04-15T16:34:48.142000Z',\n",
        "  'md5': '16cf037b8c8caad6759afc8c309de0f9',\n",
        "  'mime_type': 'application/x-shockwave-flash',\n",
        "  'tags': ['[69:FileAttributes] useDirectBlit: 0, useGPU: 0, hasMetadata: 1, actionscript3: 1, useNetwork: 1',\n",
        "   '[77:Metadata]',\n",
        "   '[09:SetBackgroundColor] Color: #ffffffff',\n",
        "   '[43:FrameLabel]',\n",
        "   '[39:DefineSprite] ID: 2',\n",
        "   '[82:DoABC]',\n",
        "   '[76:SymbolClass]',\n",
        "   '[01:ShowFrame]',\n",
        "   '[00:End]'],\n",
        "  'type_tag': 'swf',\n",
        "  'version': 9}}"
       ]
      }
     ],
     "prompt_number": 30
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: left; margin: 0px 30px 0px 0px\"><img src=\"files/images/monkey.jpg\" width=\"200px\"></div>\n",
      "# Hmmm... not sure...\n",
      "We need better SWF workers obviously, but naively we can see that actionscript and useNetwork are enabled, also the DoABC (Actionscript ByteCode) tag is there. The SWF file warrants further investigation."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pe_view = c.work_request('pe_indicators', '4410133f571476f2e76e29e61767b557')\n",
      "pe_view"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 31,
       "text": [
        "{'pe_indicators': {'indicator_list': [{'attributes': ['gettickcount',\n",
        "     'queryperformancecounter',\n",
        "     'isdebuggerpresent'],\n",
        "    'category': 'ANTI_DEBUG',\n",
        "    'description': 'Imported symbols related to anti-debugging',\n",
        "    'severity': 3},\n",
        "   {'attributes': ['cocreateinstance'],\n",
        "    'category': 'COM_SERVICES',\n",
        "    'description': 'Imported symbols related to COM or Services',\n",
        "    'severity': 3},\n",
        "   {'attributes': ['.malina', '.ndata', '.mdata'],\n",
        "    'category': 'MALFORMED',\n",
        "    'description': 'Section(s) with a non-standard name, tamper indication',\n",
        "    'severity': 3},\n",
        "   {'attributes': ['getmodulefilenamea',\n",
        "     'getmodulefilenamew',\n",
        "     'getmodulehandlea',\n",
        "     'getstartupinfow',\n",
        "     'getmodulehandlew'],\n",
        "    'category': 'PROCESS_MANIPULATION',\n",
        "    'description': 'Imported symbols related to process manipulation/injection',\n",
        "    'severity': 3},\n",
        "   {'attributes': ['filetimetosystemtime', 'getsystemtimeasfiletime'],\n",
        "    'category': 'PROCESS_SPAWN',\n",
        "    'description': 'Imported symbols related to spawning a new process',\n",
        "    'severity': 2},\n",
        "   {'attributes': ['loadlibraryw', 'getprocaddress'],\n",
        "    'category': 'STEALTH_LOAD',\n",
        "    'description': 'Imported symbols related to loading libraries, resources, etc in a sneaky way',\n",
        "    'severity': 2},\n",
        "   {'attributes': ['findfirstfilea', 'findnextfilea'],\n",
        "    'category': 'SYSTEM_PROBE',\n",
        "    'description': 'Imported symbols related to probing the system',\n",
        "    'severity': 2},\n",
        "   {'attributes': ['setfiletime', 'createfilea', 'createfilew'],\n",
        "    'category': 'SYSTEM_STATE',\n",
        "    'description': 'Imported symbols related to changing system state',\n",
        "    'severity': 1}],\n",
        "  'md5': '4410133f571476f2e76e29e61767b557'}}"
       ]
      }
     ],
     "prompt_number": 31
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: left; margin: 0px 30px 0px 0px\"><img src=\"files/images/whoa_nelly.jpeg\" width=\"200px\"></div>\n",
      "# Whoa there's a lot of 'interesting' stuff in that executable\n",
      "Clearly we're not making a definitive statement here but with all the indicators above we highly suspect the executable as being malicious and it at least warrants further investigation."
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: right; margin: 0px 0px 0px 30px\"><img src=\"files/images/full_graph.png\" width=\"400px\"></div>\n",
      "# Network Context\n",
      "## Neo4j: Origin of the four files\n",
      "Okay we're now pretty sure that at least two of the four files are bad, but exactly where did the files come from within the context of the network information that we can extract from the PCAP file. The image on the right shows all of the relevant information that we gather using the 'pcap_graph' worker called below. The blue nodes are the four files (a close-up image is given below). All graph images were pulled by simply going to the Neo4j graphical interface at http://localhost:7474/browser/."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "graph = c.work_request('pcap_graph', pcap_md5)\n",
      "graph"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 37,
       "text": [
        "{'pcap_graph': {'md5': 'df4f4a2e2bf020be50a12554942edb88',\n",
        "  'output': 'graph_complete'}}"
       ]
      }
     ],
     "prompt_number": 37
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Full Graph\n",
      "This graph image below was generated by going to  http://localhost:7474/browser and executing this query \n",
      "    \n",
      "    match (n)-[r]-() return n,r\n",
      "<div style=\"margin: 0px 0px 0px 0px\"><img src=\"files/images/full_graph.png\" width=\"900px\"></div>"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# File Graph\n",
      "This graph image below which focuses on the files themselves and the path they took to get to our infected host (orange node in the middle) was generated by going to  http://localhost:7474/browser and executing this query \n",
      "    \n",
      "    match (s:file),(t{name:'192.168.22.10'}), p=shortestPath((s)--(t)) return p\n",
      "\n",
      "<div style=\"margin: 0px 0px 0px 0px\"><img src=\"files/images/files_graph.png\" width=\"900px\"></div>"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Timing: File Downloads and DNS requests\n",
      "Looking at the file graph above, the SWF even looks more suspicious (even though VT has 0 hits out of 51 as of 4-20-2014). So we want to take a look at the timing of the file downloads and DNS requests.\n",
      "\n",
      "<div style=\"float: left; margin: 0px 30px 0px 0px\"><img src=\"files/images/help.jpeg\" width=\"150px\"></div>\n",
      "<br><br>\n",
      "Okay what you'd really do here is an RE analysis of the SWF file to see if indeed is using something like navigateToURL or one of the many other network functions to hit the focondteavrt.ru domain. We would ^love^ for some super nice person to help us out with this and put the results into this notebook. :) You can get the SWF File here [SWF_File](https://github.com/SuperCowPowers/workbench/blob/master/data/swf/unknown.swf) (obviously proceed with caution!).\n",
      "\n",
      "But for now we're going to look at timing data which is admittedly a bit more circumstantial."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Lets look at the timing of the dns requests and the file downloads\n",
      "\n",
      "# Make a new column in both dataframe with a proper datetime stamp\n",
      "dns_df['time'] = pd.to_datetime(dns_df['ts'], unit='s')\n",
      "files_df['time'] = pd.to_datetime(files_df['ts'], unit='s')\n",
      "\n",
      "# Now make time the new index for both dataframes\n",
      "dns_df.set_index(['time'], inplace=True)\n",
      "files_df.set_index(['time'], inplace=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 22
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<div style=\"float: right; margin: 0px 30px 0px 0px\"><img src=\"files/images/subset.jpeg\" width=\"200px\"></div>\n",
      "# Dataframes are great for subsetting and filtering/masking\n",
      "### Below we show several examples where we're just interested in particular columns and rows.\n",
      "\n",
      "- First: We're only interested in the set of files above (captured in the python list called 'file_mds') and the domains of interest (captured below in the 'domains' list.\n",
      "- Second: We want to look at only a few columns from both the files dataframe and the dns dataframe.\n",
      "- Third: The results of the operations are placed into the 'interesting_files' and 'interesting_dns' dataframes and then concatenated together."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "interesting_files = files_df[files_df['md5'].isin(file_md5s)]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 54
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "domains = ['kitchenboss.com.au','www.kitchenboss.com.au','p22x62n0yr63872e-qh6.focondteavrt.ru',\n",
      "           '2496128308-6.focondteavrt.ru','92.194.4.142.in-addr.arpa']\n",
      "interesting_dns = dns_df[dns_df['query'].isin(domains)]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 62
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "all_time = pd.concat([interesting_dns[['query','answers','qtype_name']], interesting_files[['md5','mime_type','tx_hosts']]])\n",
      "all_time.sort_index(inplace=True)\n",
      "all_time"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>answers</th>\n",
        "      <th>md5</th>\n",
        "      <th>mime_type</th>\n",
        "      <th>qtype_name</th>\n",
        "      <th>query</th>\n",
        "      <th>tx_hosts</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>time</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:38:54.505653</th>\n",
        "      <td>                    111.223.225.83</td>\n",
        "      <td>                              NaN</td>\n",
        "      <td>                           NaN</td>\n",
        "      <td>   A</td>\n",
        "      <td>                   kitchenboss.com.au</td>\n",
        "      <td>            NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:38:55.370111</th>\n",
        "      <td> kitchenboss.com.au,111.223.225.83</td>\n",
        "      <td>                              NaN</td>\n",
        "      <td>                           NaN</td>\n",
        "      <td>   A</td>\n",
        "      <td>               www.kitchenboss.com.au</td>\n",
        "      <td>            NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:00.511352</th>\n",
        "      <td>                               NaN</td>\n",
        "      <td> 16cf037b8c8caad6759afc8c309de0f9</td>\n",
        "      <td> application/x-shockwave-flash</td>\n",
        "      <td> NaN</td>\n",
        "      <td>                                  NaN</td>\n",
        "      <td> 111.223.225.83</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:00.511405</th>\n",
        "      <td>                               NaN</td>\n",
        "      <td> 16cf037b8c8caad6759afc8c309de0f9</td>\n",
        "      <td> application/x-shockwave-flash</td>\n",
        "      <td> NaN</td>\n",
        "      <td>                                  NaN</td>\n",
        "      <td> 111.223.225.83</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:02.607899</th>\n",
        "      <td>                      142.4.194.92</td>\n",
        "      <td>                              NaN</td>\n",
        "      <td>                           NaN</td>\n",
        "      <td>   A</td>\n",
        "      <td> p22x62n0yr63872e-qh6.focondteavrt.ru</td>\n",
        "      <td>            NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:07.436915</th>\n",
        "      <td>                      142.4.194.92</td>\n",
        "      <td>                              NaN</td>\n",
        "      <td>                           NaN</td>\n",
        "      <td>   A</td>\n",
        "      <td>         2496128308-6.focondteavrt.ru</td>\n",
        "      <td>            NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:08.690350</th>\n",
        "      <td>                               NaN</td>\n",
        "      <td> c762b6ba4f560692b6b84ac212cd3ec2</td>\n",
        "      <td>               application/jar</td>\n",
        "      <td> NaN</td>\n",
        "      <td>                                  NaN</td>\n",
        "      <td>   142.4.194.92</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:09.285793</th>\n",
        "      <td>                               NaN</td>\n",
        "      <td> c762b6ba4f560692b6b84ac212cd3ec2</td>\n",
        "      <td>               application/jar</td>\n",
        "      <td> NaN</td>\n",
        "      <td>                                  NaN</td>\n",
        "      <td>   142.4.194.92</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:09.862145</th>\n",
        "      <td>                               NaN</td>\n",
        "      <td> c762b6ba4f560692b6b84ac212cd3ec2</td>\n",
        "      <td>               application/jar</td>\n",
        "      <td> NaN</td>\n",
        "      <td>                                  NaN</td>\n",
        "      <td>   142.4.194.92</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:10.103892</th>\n",
        "      <td>                                 -</td>\n",
        "      <td>                              NaN</td>\n",
        "      <td>                           NaN</td>\n",
        "      <td> PTR</td>\n",
        "      <td>            92.194.4.142.in-addr.arpa</td>\n",
        "      <td>            NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:12.944153</th>\n",
        "      <td>                               NaN</td>\n",
        "      <td> 40b8c3c98f50e078251ec272620dfb5b</td>\n",
        "      <td>               application/pdf</td>\n",
        "      <td> NaN</td>\n",
        "      <td>                                  NaN</td>\n",
        "      <td>   142.4.194.92</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:34.991542</th>\n",
        "      <td>                               NaN</td>\n",
        "      <td> c762b6ba4f560692b6b84ac212cd3ec2</td>\n",
        "      <td>               application/jar</td>\n",
        "      <td> NaN</td>\n",
        "      <td>                                  NaN</td>\n",
        "      <td>   142.4.194.92</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:35.784163</th>\n",
        "      <td>                               NaN</td>\n",
        "      <td> c762b6ba4f560692b6b84ac212cd3ec2</td>\n",
        "      <td>               application/jar</td>\n",
        "      <td> NaN</td>\n",
        "      <td>                                  NaN</td>\n",
        "      <td>   142.4.194.92</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2014-04-09 06:39:36.369731</th>\n",
        "      <td>                               NaN</td>\n",
        "      <td> 4410133f571476f2e76e29e61767b557</td>\n",
        "      <td>         application/x-dosexec</td>\n",
        "      <td> NaN</td>\n",
        "      <td>                                  NaN</td>\n",
        "      <td>   142.4.194.92</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>14 rows \u00d7 6 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 64,
       "text": [
        "                                                      answers  \\\n",
        "time                                                            \n",
        "2014-04-09 06:38:54.505653                     111.223.225.83   \n",
        "2014-04-09 06:38:55.370111  kitchenboss.com.au,111.223.225.83   \n",
        "2014-04-09 06:39:00.511352                                NaN   \n",
        "2014-04-09 06:39:00.511405                                NaN   \n",
        "2014-04-09 06:39:02.607899                       142.4.194.92   \n",
        "2014-04-09 06:39:07.436915                       142.4.194.92   \n",
        "2014-04-09 06:39:08.690350                                NaN   \n",
        "2014-04-09 06:39:09.285793                                NaN   \n",
        "2014-04-09 06:39:09.862145                                NaN   \n",
        "2014-04-09 06:39:10.103892                                  -   \n",
        "2014-04-09 06:39:12.944153                                NaN   \n",
        "2014-04-09 06:39:34.991542                                NaN   \n",
        "2014-04-09 06:39:35.784163                                NaN   \n",
        "2014-04-09 06:39:36.369731                                NaN   \n",
        "\n",
        "                                                         md5  \\\n",
        "time                                                           \n",
        "2014-04-09 06:38:54.505653                               NaN   \n",
        "2014-04-09 06:38:55.370111                               NaN   \n",
        "2014-04-09 06:39:00.511352  16cf037b8c8caad6759afc8c309de0f9   \n",
        "2014-04-09 06:39:00.511405  16cf037b8c8caad6759afc8c309de0f9   \n",
        "2014-04-09 06:39:02.607899                               NaN   \n",
        "2014-04-09 06:39:07.436915                               NaN   \n",
        "2014-04-09 06:39:08.690350  c762b6ba4f560692b6b84ac212cd3ec2   \n",
        "2014-04-09 06:39:09.285793  c762b6ba4f560692b6b84ac212cd3ec2   \n",
        "2014-04-09 06:39:09.862145  c762b6ba4f560692b6b84ac212cd3ec2   \n",
        "2014-04-09 06:39:10.103892                               NaN   \n",
        "2014-04-09 06:39:12.944153  40b8c3c98f50e078251ec272620dfb5b   \n",
        "2014-04-09 06:39:34.991542  c762b6ba4f560692b6b84ac212cd3ec2   \n",
        "2014-04-09 06:39:35.784163  c762b6ba4f560692b6b84ac212cd3ec2   \n",
        "2014-04-09 06:39:36.369731  4410133f571476f2e76e29e61767b557   \n",
        "\n",
        "                                                mime_type qtype_name  \\\n",
        "time                                                                   \n",
        "2014-04-09 06:38:54.505653                            NaN          A   \n",
        "2014-04-09 06:38:55.370111                            NaN          A   \n",
        "2014-04-09 06:39:00.511352  application/x-shockwave-flash        NaN   \n",
        "2014-04-09 06:39:00.511405  application/x-shockwave-flash        NaN   \n",
        "2014-04-09 06:39:02.607899                            NaN          A   \n",
        "2014-04-09 06:39:07.436915                            NaN          A   \n",
        "2014-04-09 06:39:08.690350                application/jar        NaN   \n",
        "2014-04-09 06:39:09.285793                application/jar        NaN   \n",
        "2014-04-09 06:39:09.862145                application/jar        NaN   \n",
        "2014-04-09 06:39:10.103892                            NaN        PTR   \n",
        "2014-04-09 06:39:12.944153                application/pdf        NaN   \n",
        "2014-04-09 06:39:34.991542                application/jar        NaN   \n",
        "2014-04-09 06:39:35.784163                application/jar        NaN   \n",
        "2014-04-09 06:39:36.369731          application/x-dosexec        NaN   \n",
        "\n",
        "                                                           query  \\\n",
        "time                                                               \n",
        "2014-04-09 06:38:54.505653                    kitchenboss.com.au   \n",
        "2014-04-09 06:38:55.370111                www.kitchenboss.com.au   \n",
        "2014-04-09 06:39:00.511352                                   NaN   \n",
        "2014-04-09 06:39:00.511405                                   NaN   \n",
        "2014-04-09 06:39:02.607899  p22x62n0yr63872e-qh6.focondteavrt.ru   \n",
        "2014-04-09 06:39:07.436915          2496128308-6.focondteavrt.ru   \n",
        "2014-04-09 06:39:08.690350                                   NaN   \n",
        "2014-04-09 06:39:09.285793                                   NaN   \n",
        "2014-04-09 06:39:09.862145                                   NaN   \n",
        "2014-04-09 06:39:10.103892             92.194.4.142.in-addr.arpa   \n",
        "2014-04-09 06:39:12.944153                                   NaN   \n",
        "2014-04-09 06:39:34.991542                                   NaN   \n",
        "2014-04-09 06:39:35.784163                                   NaN   \n",
        "2014-04-09 06:39:36.369731                                   NaN   \n",
        "\n",
        "                                  tx_hosts  \n",
        "time                                        \n",
        "2014-04-09 06:38:54.505653             NaN  \n",
        "2014-04-09 06:38:55.370111             NaN  \n",
        "2014-04-09 06:39:00.511352  111.223.225.83  \n",
        "2014-04-09 06:39:00.511405  111.223.225.83  \n",
        "2014-04-09 06:39:02.607899             NaN  \n",
        "2014-04-09 06:39:07.436915             NaN  \n",
        "2014-04-09 06:39:08.690350    142.4.194.92  \n",
        "2014-04-09 06:39:09.285793    142.4.194.92  \n",
        "2014-04-09 06:39:09.862145    142.4.194.92  \n",
        "2014-04-09 06:39:10.103892             NaN  \n",
        "2014-04-09 06:39:12.944153    142.4.194.92  \n",
        "2014-04-09 06:39:34.991542    142.4.194.92  \n",
        "2014-04-09 06:39:35.784163    142.4.194.92  \n",
        "2014-04-09 06:39:36.369731    142.4.194.92  \n",
        "\n",
        "[14 rows x 6 columns]"
       ]
      }
     ],
     "prompt_number": 64
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Discussion: Sequence of File Downloads and DNS requests \n",
      "### Looking at the table above we see the following sequence of events: \n",
      "<pre>\n",
      "- **(Beginning)    DNS Queries for the kitchenboss.com.au**\n",
      "- **(+5 seconds)   SWF file downloaded**\n",
      "- **(+2 seconds)   DNS Query to p22x62n0yr63872e-qh6.focondteavrt.ru**\n",
      "- **(+5 seconds)   DNS Query to 2496128308-6.focondteavrt.ru**\n",
      "- **(+1 seconds)   3 JAR files downloads (same one) very close together**\n",
      "- **(+0.3 seconds) DNS reverse(PTR) query for 142.4.194.92 with no answer**\n",
      "- **(+2 seconds)   PDF file download**\n",
      "- **(+24 seconds)  2 JAR files downloads (same one)**\n",
      "- **(+0.6 seconds) PE Exec file download**\n",
      "</pre>\n",
      "<div style=\"float: left; margin: 0px 30px 0px 0px\"><img src=\"files/images/help.jpeg\" width=\"150px\"></div>\n",
      "<br><br>\n",
      "So given that the PCAP was captured on a VM that isn't doing anything else and looking at the timing above we could surmise that perhaps the SWF intiated the connection to the focondteavrt.ru domains (circumstantial obviously). As mentioned above the right thing to do here would be to conduct an RE on the SWF file and we would ^love^ for some super nice person to help us out with this and put the results into this notebook. :)"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#Wrap Up\n",
      "Well that's it for this notebook. We hope this exercise showed some neato functionality using [Workbench](https://github.com/SuperCowPowers/workbench), we encourage you to check out the GitHub repository and our other notebooks:\n",
      "\n",
      "* **<a href=\"http://nbviewer.ipython.org/url/raw.github.com/SuperCowPowers/workbench/master/notebooks/Workbench_Demo.ipynb\">Workbench Demo</a>**\n",
      "* **<a href=\"http://nbviewer.ipython.org/url/raw.github.com/SuperCowPowers/workbench/master/notebooks/PCAP_DriveBy.ipynb\">WIP: PCAP DriveBy Analysis</a>**\n",
      "* **<a href=\"http://nbviewer.ipython.org/url/raw.github.com/SuperCowPowers/workbench/master/notebooks/PE_SimGraph.ipynb\">Using Neo4j for PE File Sim Graph</a>**\n",
      "* **<a href=\"http://nbviewer.ipython.org/url/raw.github.com/SuperCowPowers/workbench/master/notebooks/Generator_Pipelines.ipynb\">Generator Pipelines Notebook</a>**"
     ]
    }
   ],
   "metadata": {}
  }
 ]
}