OSQuery-Jupyter-MacOS.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to OSQuery on MacOS\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 ran and data returned. \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "## Configuration\n",
    "\n",
    "\n",
    "### Install OSQuery\n",
    "\n",
    "These commands will install OSQuery via [Homebrew](https://brew.sh/).  If you do not use Homebrew manual install instructions can be [found here](https://osquery.readthedocs.io/en/stable/installation/install-macos/). \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!brew update\n",
    "!brew install osquery"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### 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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip3 install pandas\n",
    "!pip3 install IPython"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using OSQuery\n",
    "\n",
    "\n",
    "### Import The Needed Python Libraries\n",
    "\n"
   ]
  },
  {
   "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.io.json import json_normalize\n",
    "from IPython.display import JSON,display,HTML"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Basic Commands\n",
    "\n",
    "\n",
    "### Display OS Version\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "outputPrepend"
    ]
   },
   "outputs": [],
   "source": [
    "commands = ['osqueryi --logger_min_status 1 --json \"select * from os_version\"']\n",
    "for command in commands:\n",
    "    data = subprocess.check_output(commands, shell=True).decode('utf-8')\n",
    "    nested = json.loads(data)\n",
    "    df = pd.DataFrame(json_normalize(nested))\n",
    "    df.style.set_properties(**{'text-align': 'right'})\n",
    "    display(df.style.hide_index())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### List Installed Applications\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "outputPrepend"
    ]
   },
   "outputs": [],
   "source": [
    "commands = ['osqueryi --logger_min_status 1 --json \"select bundle_identifier, bundle_short_version  from apps ORDER BY name;\"']\n",
    "for command in commands:\n",
    "    data = subprocess.check_output(commands, shell=True).decode('utf-8')\n",
    "    nested = json.loads(data)\n",
    "    df = pd.DataFrame(json_normalize(nested)).head(50)\n",
    "    df.style.set_properties(**{'text-align': 'right'})\n",
    "    display(df.style.hide_index())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### List Logged In Users\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "outputPrepend"
    ]
   },
   "outputs": [],
   "source": [
    "commands = ['osqueryi --logger_min_status 1 --json \"select * from logged_in_users;\"']\n",
    "for command in commands:\n",
    "    data = subprocess.check_output(commands, shell=True).decode('utf-8')\n",
    "    nested = json.loads(data)\n",
    "    df = pd.DataFrame(json_normalize(nested))\n",
    "    df.style.set_properties(**{'text-align': 'right'})\n",
    "    display(df.style.hide_index())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### List Last 10 Logins\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "commands = ['osqueryi --logger_min_status 1 --json \"select * from last\"']\n",
    "for command in commands:\n",
    "    data = subprocess.check_output(commands, shell=True).decode('utf-8')\n",
    "    nested = json.loads(data)\n",
    "    df = pd.DataFrame(json_normalize(nested)).head(10)\n",
    "    df.style.set_properties(**{'text-align': 'right'})\n",
    "    display(df.style.hide_index())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### List Saved Wifi Networks\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "outputPrepend"
    ]
   },
   "outputs": [],
   "source": [
    "commands = ['osqueryi --logger_min_status 1 --json \"SELECT network_name, security_type FROM wifi_networks;\"']\n",
    "for command in commands:\n",
    "    data = subprocess.check_output(commands, shell=True).decode('utf-8')\n",
    "    nested = json.loads(data)\n",
    "    df = pd.DataFrame(json_normalize(nested)).head(50)\n",
    "    df.style.set_properties(**{'text-align': 'right'})\n",
    "    display(df.style.hide_index())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### List System Uptime\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "commands = ['osqueryi --logger_min_status 1 --json \"SELECT * FROM uptime;\"']\n",
    "for command in commands:\n",
    "    data = subprocess.check_output(commands, shell=True).decode('utf-8')\n",
    "    nested = json.loads(data)\n",
    "    df = pd.DataFrame(json_normalize(nested))\n",
    "    df.style.set_properties(**{'text-align': 'right'})\n",
    "    display(df.style.hide_index())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### List Network Interfaces\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "commands = ['osqueryi --logger_min_status 1 --json \"SELECT a.interface, a.address, d.mac FROM interface_addresses a JOIN interface_details d USING (interface);\"']\n",
    "for command in commands:\n",
    "    data = subprocess.check_output(commands, shell=True).decode('utf-8')\n",
    "    nested = json.loads(data)\n",
    "    df = pd.DataFrame(json_normalize(nested))\n",
    "    df.style.set_properties(**{'text-align': 'right'})\n",
    "    display(df.style.hide_index().set_properties(**{'text-align': 'left'}))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### List Installed Homebrew Packages\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "commands = ['osqueryi --logger_min_status 1 --json \"SELECT * FROM homebrew_packages;\"']\n",
    "for command in commands:\n",
    "    data = subprocess.check_output(commands, shell=True).decode('utf-8')\n",
    "    nested = json.loads(data)\n",
    "    df = pd.DataFrame(json_normalize(nested)).head(50)\n",
    "    df.style.set_properties(**{'text-align': 'right'})\n",
    "    display(df.style.hide_index().set_properties(**{'text-align': 'left'}))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### List System Mounted Devices and Filesystems\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "commands = ['osqueryi --logger_min_status 1 --json \"SELECT device, device_alias, path, type, blocks_size FROM mounts;\"']\n",
    "for command in commands:\n",
    "    data = subprocess.check_output(commands, shell=True).decode('utf-8')\n",
    "    nested = json.loads(data)\n",
    "    df = pd.DataFrame(json_normalize(nested))\n",
    "    df.style.set_properties(**{'text-align': 'right'})\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 = ['osqueryi --logger_min_status 1 --json \"SELECT * FROM startup_items;\"']\n",
    "for command in commands:\n",
    "    data = subprocess.check_output(commands, shell=True).decode('utf-8')\n",
    "    nested = json.loads(data)\n",
    "    df = pd.DataFrame(json_normalize(nested))\n",
    "    df.style.set_properties(**{'text-align': 'right'})\n",
    "    display(df.style.hide_index().set_properties(**{'text-align': 'left'}))"
   ]
  },
  {
   "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 = ['osqueryi --logger_min_status 1 --json \"SELECT * FROM users JOIN user_ssh_keys USING (uid);\"']\n",
    "for command in commands:\n",
    "    data = subprocess.check_output(commands, shell=True).decode('utf-8')\n",
    "    nested = json.loads(data)\n",
    "    df = pd.DataFrame(json_normalize(nested))\n",
    "    df.style.set_properties(**{'text-align': 'right'})\n",
    "    display(df.style.hide_index().set_properties(**{'text-align': 'left'}))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "### List Users\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "commands = ['osqueryi --logger_min_status 1 --json \"SELECT username, description FROM users ORDER BY username;\"']\n",
    "for command in commands:\n",
    "    data = subprocess.check_output(commands, shell=True).decode('utf-8')\n",
    "    nested = json.loads(data)\n",
    "    df = pd.DataFrame(json_normalize(nested)).head(50)\n",
    "    df.style.set_properties(**{'text-align': 'right'})\n",
    "    display(df.style.hide_index().set_properties(**{'text-align': 'left'}))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.4 64-bit",
   "language": "python",
   "name": "python37464bit30fd931474354555883ee472d9afe6f8"
  },
  "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": 2
}