{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# Finding Correlations in a CSV of Malware Events via Hypergraph Views\n",
    "\n",
    "To find patterns and outliers in CSVs and event data, Graphistry provides the hypergraph transform. \n",
    "\n",
    "As an example, this notebook examines different malware files reported to a security vendor. It reveals phenomena such as:\n",
    "\n",
    "* The malware files cluster into several families\n",
    "* The nodes central to a cluster reveal attributes specific to a strain of malware\n",
    "* The nodes bordering a cluster reveal attributes that show up in a strain, but are unique to each instance in that strain\n",
    "* Several families have attributes connecting them, suggesting they had the same authors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import graphistry as g\n",
    "#graphistry.register(key='...')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('# samples', 999)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'Campaign': 'TRANSFORMICE',\n",
       " 'Date': '2015-11-19 14:04:23',\n",
       " 'Domain': 'spynet1.ddns.net',\n",
       " 'InstallDir': 'TEMP',\n",
       " 'InstallFlag': 'True',\n",
       " 'InstallName': 'svchost.exe',\n",
       " 'NetworkSeparator': \"|'|'|\",\n",
       " 'Origin': 'vt',\n",
       " 'Port': '1177',\n",
       " 'RegistryValue': 'ba4c12bee3027d94da5c81db2d196bfd',\n",
       " 'Version': '0.6.4',\n",
       " 'compile_date': '2015-11-18 21:25:59',\n",
       " 'imphash': 'f34d5f2d4577ed6d9ceec516c1f5a744',\n",
       " 'magic': 'PE32 executable for MS Windows (GUI) Intel 80386 32-bit Mono/.Net assembly',\n",
       " 'md5': '007a8403b3281fd4d48c69f4c96da0b8',\n",
       " 'rat_name': 'njRat',\n",
       " 'section_.RELOC': '7905c1aa858eb5484ad08a2e10b7e50e',\n",
       " 'section_.RSRC': '5b346ed223699f15252c1fdad182859f',\n",
       " 'section_.TEXT': 'f414cace41511d02fb8e278cf36fd2a3',\n",
       " 'sha1': 'd215edec90c5487800d961cc1ac2808e221818fa',\n",
       " 'sha256': '2beb53ca652d9d4f73516ce45365ae824370d2408d6b0d5a809cf3cd177ba694'}"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('barncat.1k.csv', encoding = \"utf8\")\n",
    "print(\"# samples\", len(df))\n",
    "eval(df[:10]['value'].tolist()[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>uuid</th>\n",
       "      <th>event_id</th>\n",
       "      <th>category</th>\n",
       "      <th>type</th>\n",
       "      <th>value</th>\n",
       "      <th>to_ids</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>56e1af55-22f4-4b76-881a-50feac1f3af3</td>\n",
       "      <td>417</td>\n",
       "      <td>External analysis</td>\n",
       "      <td>comment</td>\n",
       "      <td>{\"InstallFlag\": \"True\", \"RegistryValue\": \"ba4c...</td>\n",
       "      <td>0</td>\n",
       "      <td>20160310</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   uuid  event_id           category     type  \\\n",
       "0  56e1af55-22f4-4b76-881a-50feac1f3af3       417  External analysis  comment   \n",
       "\n",
       "                                               value  to_ids      date  \n",
       "0  {\"InstallFlag\": \"True\", \"RegistryValue\": \"ba4c...       0  20160310  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#avoid double counting\n",
    "df3 = df[df['value'].str.contains(\"{\")]\n",
    "df3[:1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ActivateKeylogger</th>\n",
       "      <th>ActiveXKey</th>\n",
       "      <th>ActiveXStartup</th>\n",
       "      <th>BackupDNSServer</th>\n",
       "      <th>BypassUAC</th>\n",
       "      <th>Campaign</th>\n",
       "      <th>ChangeCreationDate</th>\n",
       "      <th>ClearAccessControl</th>\n",
       "      <th>ClearZoneIdentifier</th>\n",
       "      <th>ConnectDelay</th>\n",
       "      <th>...</th>\n",
       "      <th>section_.TEXT</th>\n",
       "      <th>section_.TLS</th>\n",
       "      <th>section_BSS</th>\n",
       "      <th>section_CODE</th>\n",
       "      <th>section_DATA</th>\n",
       "      <th>sha1</th>\n",
       "      <th>sha256</th>\n",
       "      <th>to_ids</th>\n",
       "      <th>type</th>\n",
       "      <th>uuid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>comment</td>\n",
       "      <td>56e1af55-22f4-4b76-881a-50feac1f3af3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 116 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  ActivateKeylogger ActiveXKey ActiveXStartup BackupDNSServer BypassUAC  \\\n",
       "0               NaN        NaN            NaN             NaN       NaN   \n",
       "\n",
       "  Campaign ChangeCreationDate ClearAccessControl ClearZoneIdentifier  \\\n",
       "0      NaN                NaN                NaN                 NaN   \n",
       "\n",
       "   ConnectDelay                  ...                  section_.TEXT  \\\n",
       "0           NaN                  ...                            NaN   \n",
       "\n",
       "  section_.TLS section_BSS  section_CODE  section_DATA sha1 sha256 to_ids  \\\n",
       "0          NaN         NaN           NaN           NaN  NaN    NaN    0.0   \n",
       "\n",
       "      type                                  uuid  \n",
       "0  comment  56e1af55-22f4-4b76-881a-50feac1f3af3  \n",
       "\n",
       "[1 rows x 116 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Unpack 'value' json\n",
    "import json\n",
    "df4 = pd.concat([df3.drop('value', axis=1), df3.value.apply(json.loads).apply(pd.Series)])\n",
    "len(df4)\n",
    "df4[:1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Default Hypergraph Transform\n",
    "\n",
    "The hypergraph transform creates:\n",
    "* A node for every row, \n",
    "* A node for every unique value in a columns (so multiple if found across columns)\n",
    "* An edge connecting a row to its values\n",
    "\n",
    "When multiple rows share similar values, they will cluster together. When a row has unique values, those will form a ring around only that node."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('# links', 200)\n",
      "('# event entities', 50)\n",
      "('# attrib entities', 102)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "            <iframe id=\"adbf2fda-8a9d-4763-9f87-3763ffc7b7c9\" src=\"https://labs.graphistry.com/graph/graph.html?dataset=PyGraphistry/2GTR5HMS1G&type=vgraph&viztoken=3f696db6e3d4e44c529b8e2af2ce09a96f87da9f&usertag=5806a903-pygraphistry-0.9.47+0.g362df16.dirty&splashAfter=1498360513&info=true\"\n",
       "                    allowfullscreen=\"true\" webkitallowfullscreen=\"true\" mozallowfullscreen=\"true\"\n",
       "                    oallowfullscreen=\"true\" msallowfullscreen=\"true\"\n",
       "                    style=\"width:100%; height:500px; border: 1px solid #DDD\">\n",
       "            </iframe>\n",
       "        \n",
       "            <script>\n",
       "                $(\"#adbf2fda-8a9d-4763-9f87-3763ffc7b7c9\").bind('mousewheel', function(e) {\n",
       "                e.preventDefault();\n",
       "                });\n",
       "            </script>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g.hypergraph(df4[:50])['graph'].plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Configured Hypergraph Transform\n",
    "We clean up the visualization in a few ways:\n",
    "\n",
    "1. Categorize hash codes as in the same family. This simplifies coloring by the generated 'category' field. If columns share the same value, such as two columns using md5 values, this would also cause them to only create 1 node per hash, instead of per-column instance.\n",
    "\n",
    "2. Not show a lot of attributes as nodes, such as numbers and dates\n",
    "\n",
    "Running `help(graphistry.hypergraph)` reveals more options."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('# links', 2350)\n",
      "('# event entities', 204)\n",
      "('# attrib entities', 1156)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "            <iframe id=\"c044bba5-9d29-49e3-8e81-d9afc5268fd1\" src=\"https://labs.graphistry.com/graph/graph.html?dataset=PyGraphistry/3ESVXWIKFN&type=vgraph&viztoken=03ce9afefd099b65622c5c159d6b73f3b5fa4c52&usertag=5806a903-pygraphistry-0.9.47+0.g362df16.dirty&splashAfter=1498360787&info=true\"\n",
       "                    allowfullscreen=\"true\" webkitallowfullscreen=\"true\" mozallowfullscreen=\"true\"\n",
       "                    oallowfullscreen=\"true\" msallowfullscreen=\"true\"\n",
       "                    style=\"width:100%; height:500px; border: 1px solid #DDD\">\n",
       "            </iframe>\n",
       "        \n",
       "            <script>\n",
       "                $(\"#c044bba5-9d29-49e3-8e81-d9afc5268fd1\").bind('mousewheel', function(e) {\n",
       "                e.preventDefault();\n",
       "                });\n",
       "            </script>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g.hypergraph(\n",
    "    df4,\n",
    "    opts={\n",
    "        'CATEGORIES': {\n",
    "            'hash': ['sha1', 'sha256', 'md5'],\n",
    "            'section': [x for x in df4.columns if 'section_' in x]\n",
    "        },\n",
    "        'SKIP': ['event_id', 'InstallFlag', 'type', 'val', 'Date', 'date', 'Port', 'FTPPort', 'Origin', 'category', 'comment', 'to_ids']\n",
    "    })['graph'].plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}