OSQuery-Jupyter-Windows.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to Osquery on Microsoft Windows\n",
    "\n",
    "\n",
    "This is a quick introduction to [Osquery](https://osquery.io/) and a few examples of some of the queries that can be run and data returned. \n",
    "\n",
    "-----------------------------------------\n",
    "* **Authors:** Sevickson Kwidama | Jerry Gamblin\n",
    "* **Date:** 22 May 2020\n",
    "* **Version:** 0.5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Installation and Configuration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Install needed Python libraries\n",
    "\n",
    "We are going to attempt to install the non-standard libraries we use:\n",
    "-  Pandas\n",
    "-  IPython.display\n",
    "\n",
    "***Python version >= 3.5***\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip3 install pandas --upgrade\n",
    "!pip3 install IPython --upgrade"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import the needed Python libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import json\n",
    "import subprocess\n",
    "import pandas as pd\n",
    "from pandas import json_normalize\n",
    "from IPython.display import JSON,display,HTML\n",
    "import requests"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Install Osquery\n",
    "\n",
    "These commands will download and afterward install Osquery.   \n",
    "If version changes, you only need to change the `version` variable.  \n",
    "\n",
    "Installing via Chocolatey can be found a few lines lower.  \n",
    "Manual installation instructions can also be [found here](https://osquery.readthedocs.io/en/stable/installation/install-windows/). \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "version = \"4.3.0\"\n",
    "url = f'https://pkg.osquery.io/windows/osquery-{version}.msi'\n",
    "\n",
    "#First find name of file to be used for installing\n",
    "if url.find('/'):\n",
    "  osquery_filename = url.rsplit('/', 1)[1]\n",
    "\n",
    "#Download file to current location\n",
    "r = requests.get(url, allow_redirects=True)\n",
    "open(osquery_filename, 'wb').write(r.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Install the Osquery MSI without interaction and also write out a log file in the same location to check if anything went wrong."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!msiexec /i $osquery_filename /quiet /log \"osquery-install.log\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Install Osquery via Chocolatey (not tested!)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!choco install osquery"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using Osquery\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we need to find location of Osquery on this system, as in Windows it does not set Osquery binary in the `PATH`.  \n",
    "\n",
    "Below only needs to be run once, it takes a few seconds to come back. If Osquery is installed on another partition than `C:\\` you will need to change `path` variable.  \n",
    "\n",
    "When you get result you can just put the value in the variable `osquery_binary`, if you do not mind the seconds it takes to find the binary you can run the code below everytime."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "path = \"c:\\\\\"\n",
    "name = \"osqueryi.exe\"\n",
    "for (root, dirs, files) in os.walk(path):\n",
    "    if name in files:\n",
    "        osquery_binary = os.path.join(root)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "osquery_binary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function definition to use for the Osquery queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def osquery_run(query):\n",
    "    os.chdir(osquery_binary)\n",
    "    data = subprocess.run(['osqueryi', '--json', query], stdout=subprocess.PIPE, universal_newlines=True)\n",
    "    data = data.stdout\n",
    "    nested = json.loads(data)\n",
    "    df = pd.DataFrame(json_normalize(nested))\n",
    "    df.style.set_properties(**{'text-align': 'right'})\n",
    "    return df.style.hide_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Basic Commands\n",
    "If you want to run the commands on the CLI you only need to use the following, first change directory to `osquery_binary`:  \n",
    "```\n",
    "osqueryi --json \"SELECT query\"\n",
    "```\n",
    "\n",
    "### Display OS Version\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "outputPrepend"
    ]
   },
   "outputs": [],
   "source": [
    "commands = \"select * from os_version;\"\n",
    "result_query = osquery_run(commands)\n",
    "result_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List Installed Applications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "outputPrepend"
    ]
   },
   "outputs": [],
   "source": [
    "commands = \"select name, version from programs ORDER BY name;\"\n",
    "result_query = osquery_run(commands)\n",
    "result_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List Logged In Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "outputPrepend"
    ]
   },
   "outputs": [],
   "source": [
    "commands = \"select * from logged_in_users;\"\n",
    "result_query = osquery_run(commands)\n",
    "result_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List System Uptime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "commands = \"SELECT * FROM uptime;\"\n",
    "result_query = osquery_run(commands)\n",
    "result_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List Network Interfaces"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "commands = \"SELECT a.interface, a.address, d.mac FROM interface_addresses a JOIN interface_details d USING (interface);\"\n",
    "result_query = osquery_run(commands)\n",
    "result_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List Installed Chocolatey Packages (Windows + Chocolatey)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "commands = \"SELECT * FROM chocolatey_packages;\"\n",
    "result_query = osquery_run(commands)\n",
    "result_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### List System Mounted Filesystems (Windows)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "commands = \"SELECT device_id, description, file_system, size, boot_partition FROM logical_drives;\"\n",
    "result_query = osquery_run(commands)\n",
    "result_query\n",
    "#display(df.style.hide_index().set_properties(**{'text-align': 'left'}))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### List Startup Items\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "commands = \"SELECT * FROM startup_items;\"\n",
    "result_query = osquery_run(commands)\n",
    "result_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### List Private Keys in ~/.ssh directory\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "commands = \"SELECT * FROM users JOIN user_ssh_keys USING (uid);\"\n",
    "result_query = osquery_run(commands)\n",
    "result_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### List Users\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "commands = \"SELECT username, description FROM users ORDER BY username;\"\n",
    "result_query = osquery_run(commands)\n",
    "result_query"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}