azure_threat_hunting.ipynb

{
  "cells": [
    {
      "metadata": {
        "slideshow": {
          "slide_type": "slide"
        }
      },
      "cell_type": "markdown",
      "source": "# Azure Threat Hunting"
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "** by Maarten Goet, MVP & RD **"
    },
    {
      "metadata": {
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "markdown",
      "source": "Install the Kqlmagic extension"
    },
    {
      "metadata": {
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "markdown",
      "source": "(optional, because Azure Notebooks container has this installed by default)"
    },
    {
      "metadata": {
        "trusted": true,
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "code",
      "source": "!pip install Kqlmagic --no-cache-dir --upgrade",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "markdown",
      "source": "Load the Kqlmagic extension"
    },
    {
      "metadata": {
        "trusted": true,
        "slideshow": {
          "slide_type": "slide"
        }
      },
      "cell_type": "code",
      "source": "%reload_ext Kqlmagic",
      "execution_count": 34,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": "<IPython.core.display.HTML object>",
            "text/html": "<html>\n            <head>\n            <style>\n            .kql-magic-banner {\n                display: flex; \n                background-color: #d9edf7;\n            }\n            .kql-magic-banner > div {\n                margin: 10px; \n                padding: 20px; \n                color: #3a87ad; \n                font-size: 13px;\n            }\n            </style>\n            </head>\n            <body>\n                <div class='kql-magic-banner'>\n                    <div><img src='data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAALsAAACcCAYAAAAnOdrkAAAQeklEQVR42u2di5MVxRXGe5cFFhEQQcGVx4KgLg8XLFCDCigqkvgCSVQqIgRNTP6R/AcmMRpjaWLQEEWRCGrEIKXIGxUMIvJUkKcisryWfB9zVm8w1Hbfnefe76vq6ru70zO9c3/Tc6bnnNM14347z0lSJahGp0AS7JIk2CVJsEuSYJckwS5Jgl2SBLskCXZJEuySJNglwS5Jgl2SBLskVQbsp0+fvgRVT8/Nv66qqtqp0y6Bm1pUdSi1HpsfR9kDdg5nBjs63A/Vb1DGeDZZjzZPotMb9XVXNOhVqKag3G3At6Z9KK+h3fNgpymrkf0ilInowI88/0luvwhFsFe2uqIMR7kD7PTy4OYIqt0EHiUz2Dt43oZaxG2r9V1XvDiyd0Tp7Ll9F5RO1k4PqJIk2CVJsEuCXZIEuyQJdkkS7JIk2CVJsEuSYJckwS5Jgl2SBLsk2CVJsEuSYJckwS5Jgl2SBLskCXZJEuySJNjbiSyJUA8XpZ1gFP7ZkfUnUI66KPHU1zpjFQ47gGG6htoymjYBoBMZwF1npT/KAKsvRunmorQl323uovwpe1E+R9ttqDehbEfZhb6fDDxuR49NT2G/3wr2fILeiOp6g8U33wgh4ii5Bu2XhkDThn72cVHCIGZVG41ymfWZo3oP9KGqlfZMIHSQ0Lso+dT7+N37/IymR1ppy2MywVVvj65+he1XYJ9LBXu+QCfkc1AmoVwY0JS5BJcZKCcT7iNzZI5FmYgyDmUojnlh6H7QpquZOkxFeA32exvqdShv4fObvHCxTfM5RnSC/gjKII9DMSPX+Wj3IfZ3ULDnA/RrUf0SZTq+lPMC225B9TZHxwT7180gu91AH45+dopr/9gXL6JLcJyrUV+H8jI+L8bvPz9r0442og/C38736Pdgu+N0KiobNe0MdJoBD6NMKwP09aj+iPIc2u5PqH8NqO60MhbH6ZzUucC+CeZUHPNK1JejnovfrdMDavsAfZTdkqf7jFRpgm5ZazmKz0D5iY2+qQjHasDxCX5f1E8V2eYW7P8L+v34Mi/IGegcvX+MMhtlcpwmSwDwvdCPGWZzV+Pnt10bk4QK9mxBfwBfYs/AtqtQ/QllbkKg05S6h6YV9n9TlueJJhP6w3zoTTaD87lgLxbojW0AfTmqP6C8iLaHEjJdOKI/iv3fGNM+Obd+quUBM/Quwe0NeF7Yi51/ymjBnjHofNibY6ZLKOjvGujz0PabhLo4AeWhtoCOfrJvfGH0mY3EnPI7ZibIefh7X/f9y6dLcayOHsB3R7tpbI/S3VXQW/SagoI+FNUslPtC56bTAN36dz/KrWW2/xLVGpT3UFaibDHQObKfdN8n8+eDOB8+ebwxaMepxsbWZqLw9wE2wh/B51rBnm/QOaI/aNNreQOdo+VdNGHKmVq0Ps5H+RfKx60smrXfRv4VaPcGQecFhs+c8RnWCvB9ZLMXA/SZodN3KZku1ESUe3GM/oH9I9QLOStE0ENXhsP2vBu8zjecqD9C/QDqSfi9nP2KBnsRQMdxhtjsy3VlmC0voDyN/q1o46zLF9jfX8zGP0BzJfQFm2DPFnS+qp6d8xGdok/O+Nact87q3yEbzR9Hsw1xdML8ejjK00uxGfX0JN/WCvb4QKc58HOWPIOOY9Fb8WYc57LAplzu8Nm4QD8L+mV8iYSPnGO/N+QiFOzpg05PvpkumsLrn1fQTfTLGRvYR3pY/hX9W5lUp+gegONw1oazVjcL9vyC/iDNF3xhg/MMuvmk04QZEtBmB6q/26xL0uIxON04sIw7j2BPCfQ5oV9OBiM6Rc/Ca3xe6lgfGSTCqcKFrQVYxDS6H8MhX8fHRtSPVOoMTY1Ab3N/+cqevuOXBzTj9OCr6OOmtM4rjrUFfV3kIl/6UYI9e9BpDtA77xdlgE5PPnovzk9xRKf4LEHf9N4BbeiXszKDU8xpzXdwrkZU4uhekyPQe6H6GcosfBFDAtsy/Oz3KAvQtinlrvN5oiGgr5z/Xo5+bkv7HDNaCcd/Cx/pgTlcsGcHOt/40RW2oQzQH0N5JYOMANVmvtQFNNuKsiHD081opdWCPVvQ+eB0VVFAN12EcoXzi85v0acouzI85Uy3sRrnjr47vQS7QPcV564H4/jVnn2mv8snLnLgykQ8V+gH7yw7UQR7SqDTB/2+AoNOXRxowhDyzSk/QJ9rdKc51SjYk1VHCwC+02z0UNDXuiiULmvQKQZPhASOfGmgZS1mEtuMc3k8i5jYSoGdxxvoouk6ugCMLmMfvP1uzBp0S61HX52QTAZ7sjRhSsRAEEY/HbK7k2BP6LZ/B0dEwFruiw1OS47kCI99nM7w3HUxmzfEfXYfStYmDO12ekJ+4aIMaII9oZPMKJ7b27iPK/FF0Wf8Y5dg5i5P2Hta7XMnOI7qgIsy7+ZBe/Nw4VXEA2obxZciG5i9FvDvyagP9A/vHuA2e8zMhqacnMPDVgR7nsVESACdD7j0MflbRt3oGGjCEPavrM6DmgR7cYC/iikhUBiUvLYAsNOM+SaNNNgBsH8l2ItlznwA4LcDogMpH7uDC0sydDJHo/p3F59gL87o3hugM20F3wjOy2Bkry0w7MwsRj/3Zt83wII9RvF1Ok58t0DgmRxoqpkzH6U8sncKhL0pR6e75eI76Qqcc72QsANWho5xiZRRgDZ0enKSmTM7UlxMq8qFZcJtNrDyNLIfFezpg97i68J58/FcggXQevttYNu+ls6NI/uCFGEJgbeD81uoK03YT9lFKJs9JdAZG8nAi1fMI4+xmUygPyAwYSmXl7kH7TalFO5G0ENWjyPoXXL03bdcfNWCPXnI+aqf6d4YSrewxdeFadzwp5ddFFwwNWB0r0a7yS5K/bYrhUDm0AfODq68ZSqT/O675Oxu0/5g5wwAR3Ib0Rf9Hx8Xxme+xPWAQiKXsG0/Pqy6aHZmUcL/xonAkb0mZ7CfudP4ZkQQ7G0D/TGc6MXngPa0mTMjmBEscI0k5lm8G+3oN/5pgv8KZ1ZClkjkQyCXeemEfh3PyXdfMemqsxrZOeL+7lyglwDP4GCmbh6BMiVgdKe//BQzZ55gzpSE/g+O6vtxjGOeeRQJFtd74rZ5gP3MxSfYkxvVd9uo/qZnk/fNnLk8JLUGtq0vmZ1ZkiTsVvvAXuolmQeflDOObII9OdHrb6uvf4jNzvAOMNIyWYW8nr/RZme2oF3s0UFM2YF90+PyiPOIVrIH6F45mpHp5sKirAR7GWoOhGorIHnJZmduCmhXyxUozJz5c0KRTQyAoDNVP8/tmQSqR06++94a2fMpprV7kWmhuR5QAPBDSmZnliXQL8aUhjignVl8F2V9Ds5pX9nsORSg/RbQvmbmzOzA1G032uzMZ3zoTcAs2xkIOy/ANxgal9X5xPFpwvTXyJ5f4D+x2RmaM+MC2nG6j3GvH6J+Jua4VY7qjNLnqnNdPbZnUqUGq/dkeDppq9eHLnsv2NPVUpSXuewM/WECgG+wuNWNLkruGdcF+DX2y30ynrOrx/bVtn5rXcawX+oCcskL9mxGd8K1wEb3BwObT7SH1W22slxc2uyihEP1ntufyY7gonVOsxLzyQ8Q7PkHnsBy7r2BvuwB7Xpa3Crn3uOMW+W0Jt2Lx/sEQWCbgdj2epQlSUyJetjrtNXpNNdPsBdDSzi644urD8mLTrdhmjNxxq0yuwH2t8JGd9/lcOjSMNplkx2MCydcV4mLiRU1u8ABM2doDvw0sPkke1iNM251nd0xfGGn3X4L+rAafdiR4qheZ///MFeBKnJ2gTVmztAzcmRAu97mSsC593/EaLdzRYtxPmmgzX+H0NEd4pkUTxuXmJlQSZ6O7QJ2E0P5RtgqcN0DgB9j5sx/4ohbtfcAXOaGSy9O9mzTYLGzDDZZnsKoztH8DrsbOsFevNF9d4ln5J2BzW+12Zm44lZpyizC/hoDpkVvQdmGNnu5wFeCoNNNYRovxEpe+Lc9LCLFh0O6EgxlHsiACyXWuFVzDOOdhi+8pnu26cZET/jIh9ynkkjlh/12N9AfCF0hXLDnb3RvtjjWlkCPrgHNOQV3d4xxq/R5WWCr0V3p2X8uxstVvI+jfg4/fxEj6PSfv9dFCycPcxWudrE8IL7InSVxq5MD2rXErW6II27VIqw4ujeGXHhmvz+Kj91Rz8XPG2MAfYCN6DPLzIMv2HOs91DmmzkzOADQ/iXmzOIYLrwd2B9neRhscldAu6Fo9yt8rEPNZd6XlXPx2SLEfNlG94hplbx8e7uF3ZYsX2TmzMOBy6dwSo6zM5/GFLdKd+I+2F9v7C/EaY3PEQ+5aLnJhTbDQxProAfkdOqiK8INLsqBPz40u5pgLxbwW0o8IycEtOtkcat82fRkW+NWzZyhSzJdFM4LWWXELtIJaDfcZmtW4jNnevi2lS7FjF9lcqNq+/74AErHLq5NdQ3K1dhHndBu57CXjKrzLdCjXwBk9SUvm5bEcOEdMXOmC+pfhy5mbG4Qt9KPxkVBIvSsZMwrM+8yrJF5aBjix+Up6TLcVyN5hcFukP3TRYEeswLnlW+w2ZlY4lbNreEFA56m1dAy9sEc8PXO36vS17ZnIAtXy6sX7MUG/mN8mS+6yAfk2hCwzopbPRlDX/ji61mOxpxiDMlhmZRsec3X7WGYd4RawV5sLTVzhp6RfQJnRVoCPZbFdPExB87T+HiQD6D4eUKGoNOP/gmUVbyLuXxlFhbsZQJ2CF/sqy5yJZgR2HyCje5b4nrJg/0wodJztL05p++iV/e9UoScD7ac3eFd5hV7wD3hKkjteWQnYOtpzligx+iAdqVxq8/GFbdKlwIXvWGlW+8nZjKNCgwgLwf0bag4O8Tnh6VMv2dB13pAbWd6yx5W60NSYPP1uqXh4HqrK2K+CNdh38xKsJojPD4zA8IVcS+tjv3ut77zDvca9r9ZszHli6nf+MbQd5aBMxypLlpl5gNdCQahvs15BEabmOqC89VclW9d3MlI2S8XBY/zYZHpAMfhM6OI+GLo0nLXOcI+aINzJP/InjlouqxNMOdlOaL5xNW+P0N/B3lsz7SJnH49niXsvB3P5cn03J5urKmPLviiV+GkPu6iIOcLAi9mjsCnE+wbB4Dt6N+/XeRiwGeMRvM/b8ntwunHH6SXtozIXCqG5tEh+z74AopBIR/QVDLTKW/mJT1E37UffcIqmXVthc+b5MRgx8EPo9PPO//8hU0JpaHz6es7FisaaiocTWPtUhxjn412y83/vM5gp1suXxr1NDub4FfZKHfYID9gF+VWXjgxZ09I6v/lm2Eu2uwTNXWKATKZ2+wGQiFWSrZb+bEC9JN+7SwMPawywDkX3rkEjmb7X5rsgizczIrddVK781TCA2rRZ5RoQh2xIgl2SRLskiTYJcEuSYJdkgS7JAl2SRLskiTYJUmwS5JglyTBLkmCXRLskiTYJUmwS5JglyTBLkmCXZIEuyQJdkkS7JIk2KUfiEmWmFZuo2cKOi5ewERMxwS7VCjZgmvMA8ncmz4pAbndeqYCF+xSEYHn+lFcA9aHg1Nxpe4W7FKW0FfE6huCXdIDqiQJdkkS7JIk2CVJsEuSYJckwS5Jgl2SBLskCXZJsEuSYJckwS5JxdB/ASH5FI/5dHZAAAAAAElFTkSuQmCC'></div>\n                    <div>\n                        <p>Kql Query Language, aka kql, is the query language for advanced analytics on Azure Monitor resources. The current supported data sources are \n                        Azure Data Explorer (Kusto), Log Analytics and Application Insights. To get more information execute '%kql --help \"kql\"'</p>\n                        <p>   &bull; kql reference: Click on 'Help' tab > and Select 'kql referece' or execute '%kql --help \"kql\"'<br>\n                          &bull; Kqlmagic configuarion: execute '%config Kqlmagic'<br>\n                          &bull; Kqlmagic usage: execute '%kql --usage'<br>\n                    </div>\n                </div>\n            </body>\n            </html>"
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": "<IPython.core.display.HTML object>",
            "text/html": "<html>\n        <head>\n        \n        </head>\n        <body>\n        <div><p style='padding: 10px; color: #3a87ad; background-color: #d9edf7; border-color: #bce9f1'>Kqlmagic&nbsppackage&nbspis&nbspupdated&nbspfrequently.&nbspRun&nbsp&apos;!pip&nbspinstall&nbspKqlmagic&nbsp--no-cache-dir&nbsp--upgrade&apos;&nbspto&nbspuse&nbspthe&nbsplatest&nbspversion.<br>Kqlmagic&nbspversion:&nbsp0.1.75,&nbspsource:&nbsphttps://github.com/Microsoft/jupyter-Kqlmagic</p></div>\n        </body>\n        </html>"
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": "<IPython.core.display.HTML object>",
            "text/html": "<html>\n        <head>\n        \n        </head>\n        <body>\n        <div><p style='padding: 10px; color: #8a6d3b; background-color: #fcf8e3; border-color: #faebcc'>You&nbspare&nbspusing&nbspKqlmagic&nbspversion&nbsp0.1.75,&nbsphowever&nbspversion&nbsp0.1.90&nbspis&nbspavailable.&nbspYou&nbspshould&nbspconsider&nbspupgrading,&nbspexecute&nbsp&apos;!pip&nbspinstall&nbspKqlmagic&nbsp--no-cache-dir&nbsp--upgrade&apos;.</p></div>\n        </body>\n        </html>"
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": "<IPython.core.display.Javascript object>",
            "application/javascript": "try {IPython.notebook.kernel.execute(\"NOTEBOOK_URL = '\" + window.location + \"'\");} catch(err) {;}"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "markdown",
      "source": "Connect to our Log Analytics backend (in this case: Azure Security Center)"
    },
    {
      "metadata": {
        "trusted": true,
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "code",
      "source": "%kql loganalytics://tenant='...';clientsecret='...';workspace='...';alias='...'",
      "execution_count": 35,
      "outputs": []
    },
    {
      "metadata": {
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "markdown",
      "source": "Query ASC for any network connections this VM has"
    },
    {
      "metadata": {
        "trusted": true,
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "code",
      "source": "%kql VMConnection | where Computer == ‘APPSERVER’",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "slideshow": {
          "slide_type": "slide"
        }
      },
      "cell_type": "markdown",
      "source": "Filter the query so that we only see connections to IP's outside of The Netherlands"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "%%kql \n    VMConnection\n    | where Computer == 'APPSERVER'\n    | where Direction == 'outbound'\n    | where RemoteCountry != 'Netherlands'",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "markdown",
      "source": "Put the results into a dataframe"
    },
    {
      "metadata": {
        "trusted": true,
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "code",
      "source": "connections = _.to_dataframe()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "slideshow": {
          "slide_type": "slide"
        }
      },
      "cell_type": "markdown",
      "source": "Load the Pandas extensions, then import a list of TOR nodes (IP addresses) from this URL into a list called 'torlist'"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "import pandas as pd\ntorlist = pd.read_csv('https://www.dan.me.uk/torlist',header=0,names=[\"DestinationIp\"])",
      "execution_count": 11,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Compare the KQL output to the TOR list and see if we have any match"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "connections.merge(torlist, on=\"DestinationIp\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true,
        "slideshow": {
          "slide_type": "slide"
        }
      },
      "cell_type": "markdown",
      "source": "Query ASC for locations of outbound IP's and put them into a list called 'locations'"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "%%kql locations <<\n    VMConnection\n    | where Computer == \"APPSERVER\"\n    | where Direction == \"outbound\"\n    | where RemoteCountry != \"Netherlands\"\n    | distinct RemoteLongitude, RemoteLatitude // only get unique records\n    | project RemoteLongitude, RemoteLatitude  // only show these two colums",
      "execution_count": 26,
      "outputs": []
    },
    {
      "metadata": {
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "markdown",
      "source": "Install the Basemap extension"
    },
    {
      "metadata": {
        "trusted": true,
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "code",
      "source": "!conda install basemap -y",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": "Collecting package metadata: done\nSolving environment: \\ ",
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "markdown",
      "source": "Load Basemap to do some geographical visualizations"
    },
    {
      "metadata": {
        "trusted": true,
        "slideshow": {
          "slide_type": "skip"
        }
      },
      "cell_type": "code",
      "source": "from mpl_toolkits.basemap import Basemap\nimport matplotlib.pyplot as plt\n\nmap = Basemap(projection='merc',llcrnrlat=-80,urcrnrlat=80,llcrnrlon=-180,urcrnrlon=180,lat_ts=20,resolution='c')\nmap.drawcoastlines()\n\nlons = locations.RemoteLongitude\nlats = locations.RemoteLatitude\n\nx,y = map(lons, lats)\nmap.plot(x, y, 'bo', markersize=10)\n\nplt.show()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3.5",
      "language": "python"
    },
    "celltoolbar": "Slideshow"
  },
  "nbformat": 4,
  "nbformat_minor": 1
}