OSQuery-Tables-interactive_Jupyterthon.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Untangling the Osquery❓ tables web🕸 using Jupyter Notebooks📓\n",
    "## JOINing Osquery tables using graphing techniques\n",
    "-----------------------------------------\n",
    "* **Author:** Sevickson Kwidama \n",
    "    * [Twitter](https://twitter.com/SKwid345)\n",
    "    * [LinkedIn](https://nl.linkedin.com/in/sevickson)\n",
    "    * [GitHub Repo](https://github.com/sevickson/osquery_tables_graph)\n",
    "* **Version:** 1.0_Jupyterthon"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Install and Upgrade `pip` packages if needed\n",
    "I start by getting the needed dependencies if any, only need to be run once"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install --upgrade --user pip\n",
    "!pip install --user pyvis\n",
    "!pip install --user networkx"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import modules in this Jupyter Notebook\n",
    "Requirements:\n",
    "- Python >= 3.6 (There are some changes in the open() function that will give error in older versions)  \n",
    "\n",
    "Below I import the needed modules, each time I need a new module I add it to the list below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Standard modules to use and manipulate dataframes\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "# Used to download from Osquery repository and unzip needed files\n",
    "import requests, zipfile, io\n",
    "# Used to be able to access locations on disk\n",
    "import pathlib\n",
    "# Regular expression based extracts and filtering\n",
    "import re\n",
    "# Module to copy same value in a dataframe, didn't find an easier way\n",
    "from itertools import cycle\n",
    "# Modules to create the graphs and computations on the graphs\n",
    "import networkx as nx\n",
    "#import matplotlib.pyplot as plt\n",
    "from pyvis.network import Network\n",
    "# For the dropdown box interactions\n",
    "from ipywidgets import interact"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# INGEST AND MANIPULATE DATA\n",
    "-----------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Download the latest zip and extract only the tables folder\n",
    "Below I wrote some code based on a [tweet](https://twitter.com/curi0usJack/status/1255702362225811457?s=20) I saw from @curi0usJack.  \n",
    "In the code below I first download the complete zipped release, I iterate over the zipped file to extract only files from the `specs/` folder, this is the location for all the Osquery table definitions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the latest release from the API data of GitHub \n",
    "url_github_latest = \"https://api.github.com/repos/osquery/osquery/releases/latest\"\n",
    "response = requests.get(url_github_latest).json() \n",
    "dir_tables = 'osquery-tables'\n",
    "p = pathlib.Path(dir_tables)\n",
    "  \n",
    "# Response was in json and put in dict so can be called easily\n",
    "url_github_dl = response['zipball_url']\n",
    "\n",
    "# Get zipped content and unzip\n",
    "github_content = requests.get(url_github_dl, stream=True)\n",
    "zippedcontent = zipfile.ZipFile(io.BytesIO(github_content.content))\n",
    "listOfFileNames = zippedcontent.namelist()\n",
    "\n",
    "# Iterate over the file names\n",
    "for fileName in listOfFileNames:\n",
    "    # Check if file is located in the 'specs/' folder\n",
    "    if 'specs/' in fileName:\n",
    "       # Extract file from zip\n",
    "        zippedcontent.extract(fileName, dir_tables)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract table names and columns from the Osquery table files\n",
    "Below is the function to check all the files with extension `.table` to extract the Table and Column names.  \n",
    "I filter out `example.table` and the hidden Column names as they are used for internal Osquery accounting as far as I could see.  \n",
    "Here I use a bit of a hack `list(zip(cycle))` to get both lists the same length."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def osquery_table_extract(dir_tables):\n",
    "    table_columns = []\n",
    "    for path in p.rglob(\"*.table\"):\n",
    "        if path.is_file() and 'example' not in path.stem:\n",
    "            cf = open(path, \"r\", encoding=\"utf-8\").read()\n",
    "            tline = re.findall(r'table_name\\(\\\"(\\w+)\\\".*\\)',cf)\n",
    "            #below line is used to find all columns that do not have attribute hidden=True\n",
    "            clines = re.findall(r'Column\\(\\\"(\\w+?)\\\".+?(\\n)?.+? (?!hidden=True)\\S+\\),$',cf,re.M)\n",
    "            #regex returns tuples because of the multiline matching so with list comprehesion turning it back in a list\n",
    "            clines = [i[0] for i in clines]\n",
    "            tcList =  list(zip(cycle(tline),clines))\n",
    "        table_columns.append(tcList)\n",
    "    return(table_columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below I call the function above to iterate over the files I exported from the zip. All the data is than put in a DataFrame (DF).  \n",
    "I also have some checks to make sure the data is correct."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "extract = osquery_table_extract(dir_tables)\n",
    "extract_df = pd.DataFrame([t for lst in extract for t in lst], columns = ['Table','Column'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check count of table names to be sure all have been processed.  \n",
    "Check difference against Osquery website, I filtered out example.table as it is just an example table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Tables', extract_df.Table.nunique())\n",
    "extract_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add OS into DataFrame based on cMakelists file\n",
    "The `cMakelists.txt` contains all the Table/OS combinations, so I needed to do some regexing to parse out the Table/OS combination."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def osquery_table_os(tname):\n",
    "    # For tables that are not present in cMakelists.txt\n",
    "    tname_cmake = 0\n",
    "    for path in p.rglob(\"cMakelists.txt\"):\n",
    "        with open(path, 'r') as read_obj:\n",
    "            for line in read_obj:\n",
    "                if tname in line:\n",
    "                    if '/' in line:\n",
    "                        #used to delete part of string that can give double matches bases on tables names that have same start or end.\n",
    "                        tname_cmake = re.search( r'^.*/(.*)$', line, re.M|re.I).group(1)\n",
    "                    else:\n",
    "                        tname_cmake = line.strip()\n",
    "    return(tname_cmake)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The function above is called from here and I iterate over each Table name and based on the result I append the OS and if receive `0` it means Table is not present in the file but if it is present but no OS combination it means it is for all OS platforms."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tname_list = []\n",
    "for tname in extract_df['Table']:\n",
    "    table_os = osquery_table_os(tname)\n",
    "    if ':' in str(table_os):\n",
    "        t_os = re.search(r'^.+?:(.+?)\"$', table_os, re.M|re.I).group(1)\n",
    "        tname_list.append(t_os)\n",
    "    elif '0' in str(table_os):\n",
    "        # Not in cMakeLists.txt file but on the website so later manually add the OS\n",
    "        t_os = 'no_os'\n",
    "        tname_list.append(t_os)\n",
    "    else:\n",
    "        t_os = 'linux,macos,freebsd,windows'\n",
    "        tname_list.append(t_os)\n",
    "    \n",
    "extract_df_os = extract_df\n",
    "extract_df_os['OS'] = tname_list \n",
    "extract_df_os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Workaround for issue that some tables did not get the correct OS assignment, fix this later in the re.search in the osquery_table_os function.  \n",
    "Issue seems to be if another table contains a part of the name of one of the prior tables it takes the value from the last table, so matching is not specific enough.  \n",
    "\n",
    "Tables that have `no_os` need manual assignment too."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "extract_df_os.loc[extract_df_os.Table == 'crashes', 'OS'] = 'macos'\n",
    "extract_df_os.loc[extract_df_os.Table == 'azure_instance_metadata', 'OS'] = 'linux,macos,freebsd,windows'\n",
    "extract_df_os.loc[extract_df_os.Table == 'azure_instance_tags', 'OS'] = 'linux,macos,freebsd,windows'\n",
    "extract_df_os.loc[extract_df_os.Table == 'wifi_survey', 'OS'] = 'macos'\n",
    "extract_df_os.loc[extract_df_os.Table == 'processes', 'OS'] = 'linux,macos,freebsd,windows'\n",
    "extract_df_os.loc[extract_df_os.Table == 'groups', 'OS'] = 'linux,macos,freebsd,windows'\n",
    "extract_df_os.loc[extract_df_os.Table == 'hash', 'OS'] = 'linux,macos,freebsd,windows'\n",
    "extract_df_os.loc[extract_df_os.Table == 'time', 'OS'] = 'linux,macos,freebsd,windows'\n",
    "extract_df_os.loc[extract_df_os.Table == 'certificates', 'OS'] = 'macos,windows'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check if all `no_os` have been assigned, if there is output below check the website to manually add OS based on osquery schema."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "extract_df_os.loc[extract_df_os.OS == 'no_os']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below is just some code that I do a quick check if a table has the correct association."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "extract_df_os.loc[extract_df_os['Table'] == 'logon_sessions']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create separate DataFrames based on OS\n",
    "Here I could have probably used a for loop with a list to get the same result.  \n",
    "I check in the DataFrame `extract_df_os` for the OS name in the `OS` column and if present put that row in a new DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Windows\n",
    "windows_df = extract_df_os.loc[pd.np.where(extract_df_os.OS.str.contains(\"windows\"))]\n",
    "\n",
    "#Linux\n",
    "linux_df = extract_df_os.loc[pd.np.where(extract_df_os.OS.str.contains(\"linux\"))]\n",
    "\n",
    "#macOS\n",
    "macos_df = extract_df_os.loc[pd.np.where(extract_df_os.OS.str.contains(\"macos\"))]\n",
    "\n",
    "#FreeBSD\n",
    "freebsd_df = extract_df_os.loc[pd.np.where(extract_df_os.OS.str.contains(\"freebsd\"))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# GRAPHS 🕸\n",
    "----------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to create the graph and all its properties\n",
    "`create_OS_graph` function ingests a DF and creates the graph from this DF.  \n",
    "I iterate twice over the graph to first remove Columns with only one connection as this implies it is only connected to one Table and after that iterate to remove orphaned Tables.  \n",
    "I also assing different colors and sized dependent on the properties of each node.  \n",
    "- Tables with only one connection are **ORANGE**\n",
    "- Tables with more than one connection are **GREEN**\n",
    "- Columns are **RED**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_OS_graph(df_OS):\n",
    "    # Create nx node graph from DF\n",
    "    G = nx.from_pandas_edgelist(df=df_OS, source='Table', target='Column')\n",
    "    # Initiliaze lists to use for appending\n",
    "    colors = []\n",
    "    sizes = []\n",
    "    selected_nodes_list = []\n",
    "    selected_nodes_list_H = []\n",
    "    \n",
    "    # Calculate all degrees of separation for the nodes, so how many connection does each node have\n",
    "    degree = nx.degree(G)\n",
    "\n",
    "    # Iterate through nodes, if node is Table than check if node has connections, if so add to list, if no connections discard.\n",
    "    # If node not a Table than it would be a Column and if it has more than 1 connection than add to list\n",
    "    for node in G:\n",
    "        if node in df_OS.Table.values:\n",
    "            if (degree(node) > 0):\n",
    "                selected_nodes_list.append(node)\n",
    "        else: \n",
    "            # Column has always at least connection to it's own table that is why need to check for more than 1 connection\n",
    "            if (degree(node) > 1):\n",
    "                selected_nodes_list.append(node)\n",
    "\n",
    "    # Create subgraph and degress based on filtering above\n",
    "    H = G.subgraph(selected_nodes_list)\n",
    "    degree_H = nx.degree(H)\n",
    "\n",
    "    # Run the same logic as above to filter out Tables and Columns that were orphaned\n",
    "    # Also add color and size to Table or Column dependent on how many connections\n",
    "    for node in H:\n",
    "        if node in df_OS.Table.values:\n",
    "            if (degree_H(node) == 1):\n",
    "                selected_nodes_list_H.append(node)\n",
    "                colors.append(\"orange\")\n",
    "                sizes.append(300)\n",
    "            elif (degree_H(node) > 1):\n",
    "                selected_nodes_list_H.append(node)\n",
    "                colors.append(\"green\")\n",
    "                sizes.append(H.degree(node) * 700)\n",
    "        else: \n",
    "            if (degree_H(node) > 1):\n",
    "                selected_nodes_list_H.append(node)\n",
    "                colors.append(\"red\")\n",
    "                sizes.append(H.degree(node) * 1000)\n",
    "\n",
    "    I = H.subgraph(selected_nodes_list_H)\n",
    "    return(I, colors, sizes, selected_nodes_list_H)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Column Filtering\n",
    "Check the most common `Columns` to filter out common names that will not be able to JOIN.  \n",
    "Used below to start creating the ignore_list, finished it visually by walking the graph.  \n",
    "Each OS has a different `ignore_list` but the basis is from the information below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "column_count = extract_df['Column'].value_counts()\n",
    "column_for_joins = column_count[column_count > 1]\n",
    "column_for_joins.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a folder location to place the created graphs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "path_graphs = pathlib.Path.cwd() / 'graphs'\n",
    "\n",
    "try:\n",
    "    path_graphs.mkdir()\n",
    "except:\n",
    "    print (\"Creation of the directory %s failed, location probably already exists\" % path_graphs)\n",
    "else:\n",
    "    print (\"Successfully created the directory %s \" % path_graphs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## All OS Graph\n",
    "By using `pyvis.network` module I could create beautiful interactive graphs.  \n",
    "The graph is created from the graph I created in the `create_OS_graph` function the colors and sizes are also taken from that function.  \n",
    "`barnes_hut` is the type of visualization used."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the needed data from the function\n",
    "OS_graph, colors, sizes, nodelist = create_OS_graph(extract_df_os)\n",
    "\n",
    "gr=Network(height=800, width=1200, notebook=True, bgcolor=\"#222222\", font_color=\"white\")\n",
    "# First add the nodes with its properties to the graph\n",
    "gr.add_nodes(nodelist, value=sizes, title=nodelist, color=colors)\n",
    "gr.barnes_hut()\n",
    "# Now connect the nodes based on the graph returned from the function\n",
    "gr.from_nx(OS_graph)\n",
    "gr.show(\"graphs/osquery_tables_OS_ALL_graph.html\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Windows Graph\n",
    "Filtering used is OS dependent. I noticed that filtering needs some more fine-tuning this will be in the next release of this Notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ignore_list_w = ['name','path','type','version','size','version','description','status','state','label','class','source','device','mode','value','result','hardware_model','manufacturer','query','model','device_id','action','script_text','time','enabled',\n",
    "               'date','caption','publisher','active','autoupdate','flags','comment','data','registry','author','directory','license','summary','permissions'] \n",
    "#maybe filter out key not the same meaning in all tables\n",
    "#path can be used for some good joins but too noisy for now, example http://www.osdfcon.org/presentations/2016/Facebook-osquery.pdf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below we create the Windows-only graph, we first filter down the OS DataFrame we created earlier with the `ignore_list_w`.\n",
    "\n",
    "After that, we run the same code used to create the 'All OS Graph', we do the same for each OS."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "windows_df_filtered = windows_df[~windows_df['Column'].isin(ignore_list_w)]\n",
    "OS_graph, colors, sizes, nodelist = create_OS_graph(windows_df_filtered)\n",
    "\n",
    "print('Nodes:', OS_graph.number_of_nodes(), 'Edges:', OS_graph.number_of_edges())\n",
    "\n",
    "gr=Network(height=800, width=1200, notebook=True, bgcolor=\"#222222\", font_color=\"white\")\n",
    "gr.add_nodes(nodelist, value=sizes, title=nodelist, color=colors)\n",
    "gr.barnes_hut()\n",
    "gr.from_nx(OS_graph)\n",
    "gr.show(\"graphs/osquery_tables_OS_win_graph.html\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Determine shortest path from table A to table B\n",
    "Another part of this Notebook is to create a function to easily see connections between 2 different tables.  \n",
    "I actually use the same code that is in `create_OS_graph` but a trimmed down version with an extra function, `shortest_path`, to retun the shortest path between Table A and B based on Dijkstra's algorithm."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def shortest_path(df_OS,s,t):\n",
    "    P = nx.from_pandas_edgelist(df=df_OS, source='Table', target='Column')\n",
    "    path_list = []\n",
    "    colors_sp = []\n",
    "    sizes_sp = []\n",
    "    \n",
    "    # If there is a path between Table A and B, return the list with the nodes.\n",
    "    if nx.has_path(P, s, t):\n",
    "        path_list = nx.shortest_path(P, source=s, target=t) \n",
    "    else:\n",
    "        print('No path available.')\n",
    "    \n",
    "    # Create subgraph based on the shortest path.\n",
    "    Q = P.subgraph(path_list)\n",
    "    degree_Q = nx.degree(Q)\n",
    "    \n",
    "    # Below is used to add colors and sizes\n",
    "    for node in path_list:\n",
    "        if node in df_OS.Table.values:\n",
    "            if (degree_Q(node) == 1):\n",
    "                colors_sp.append(\"orange\")\n",
    "                sizes_sp.append(300)\n",
    "            elif (degree_Q(node) > 1):\n",
    "                colors_sp.append(\"green\")\n",
    "                sizes_sp.append(Q.degree(node) * 700)\n",
    "        else: \n",
    "                colors_sp.append(\"red\")\n",
    "                sizes_sp.append(Q.degree(node) * 1000)\n",
    "\n",
    "    return (Q, path_list, colors_sp, sizes_sp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`@interact` is an easy way to create user interface controls for exploring data interactively.  \n",
    "The definition of `corr_graph` automatically creates the controls. The dropdown box is based on unique values in the OS DataFrame.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "windows_df_filtered_tb = windows_df[~windows_df['Column'].isin(ignore_list_w)].sort_values('Table')\n",
    "\n",
    "@interact\n",
    "def corr_graph(Source=list(windows_df_filtered_tb.Table.unique()), Destination=list(windows_df_filtered_tb.Table.unique())):\n",
    "    sp_graph, sp_list, sp_colors, sp_sizes = shortest_path(windows_df_filtered_tb,Source,Destination)  \n",
    "    \n",
    "    # Check if the sp_list returned is not NULL which means there is no path and if sp_list == 1 this means that the Table name in Source and Destination is the same\n",
    "    if sp_list is not None and len(sp_list) > 1:\n",
    "        sp_gr=Network(notebook=True, bgcolor=\"#222222\", font_color=\"white\")\n",
    "        sp_gr.add_nodes(sp_list, value=sp_sizes, title=sp_list, color=sp_colors)\n",
    "        sp_gr.barnes_hut()\n",
    "        sp_gr.from_nx(sp_graph)\n",
    "        return(sp_gr.show(\"graphs/shortest_path_graph.html\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sources:\n",
    "https://towardsdatascience.com/getting-started-with-graph-analysis-in-python-with-pandas-and-networkx-5e2d2f82f18e  \n",
    "https://stackoverflow.com/questions/55342586/assign-color-to-networkx-node-based-on-column-name  \n",
    "https://pyvis.readthedocs.io/en/latest/tutorial.html  \n",
    "https://github.com/osquery/osquery  "
   ]
  }
 ],
 "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
}