{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "odata000",
   "metadata": {},
   "source": [
    "# Query Public Data with OData\n",
    "\n",
    "Reads live data from [opteryx.app](https://opteryx.app), the hosted service, through its [OData v4](https://www.odata.org/) feed, using nothing more than `requests` and pandas. It works through the public **National Vulnerability Database (NVD)** and **CISA Known Exploited Vulnerabilities (KEV)** datasets: discovering what's there, filtering and sorting server-side, paging through a large result, and aggregating with `$apply`.\n",
    "\n",
    "This notebook accompanies [Querying via OData](https://docs.opteryx.app/docs/guides/querying-via-odata) on docs.opteryx.app.\n",
    "\n",
    "**No account needed.** The `demo` workspace allows anonymous reads over OData, so every cell runs without a token. The last section shows how to add one for everything else, including the `public` workspace."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata001",
   "metadata": {},
   "source": [
    "## 1. Install\n",
    "\n",
    "Colab already has both packages; this line is for running the notebook elsewhere."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata002",
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -q requests pandas matplotlib"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata003",
   "metadata": {},
   "source": [
    "## 2. A small helper\n",
    "\n",
    "Every request is a `GET` against `https://odata.opteryx.app/api/v4/`. The helper below takes a path and the query options as keyword arguments, adds the `$` to each option name, and prints the URL it requested so you can see exactly what was sent.\n",
    "\n",
    "A failed read is never a `200` with an empty result: the service returns a non-2xx status with a JSON body shaped `{\"error\": {\"code\": ..., \"message\": ...}}`. The helper raises that message as an exception."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata004",
   "metadata": {},
   "outputs": [],
   "source": [
    "from urllib.parse import unquote, urljoin\n",
    "\n",
    "import pandas as pd\n",
    "import requests\n",
    "\n",
    "HOST = \"https://odata.opteryx.app\"\n",
    "BASE = HOST + \"/api/v4/\"\n",
    "\n",
    "session = requests.Session()\n",
    "\n",
    "\n",
    "class ODataError(Exception):\n",
    "    pass\n",
    "\n",
    "\n",
    "def fetch(path, **options):\n",
    "    \"\"\"GET a path (or a full URL) under the service root; `top=5` is sent as `$top=5`.\"\"\"\n",
    "    params = {f\"${name}\": str(value).lower() if isinstance(value, bool) else value for name, value in options.items()}\n",
    "    response = session.get(urljoin(BASE, path), params=params, timeout=60)\n",
    "    print(\"GET\", unquote(response.url).replace(\"+\", \" \"))\n",
    "    if not response.ok:\n",
    "        raise ODataError(f\"{response.status_code}: {response.json()['error']['message']}\")\n",
    "    return response\n",
    "\n",
    "\n",
    "def odata(path, **options):\n",
    "    return fetch(path, **options).json()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata005",
   "metadata": {},
   "source": [
    "## 3. Discover datasets\n",
    "\n",
    "The service document at the root lists every EntitySet your credentials can read. With no token, that's the `demo` workspace. Each entry's `url` is the path to query, shaped `{workspace}/{collection}/{dataset}`, and `source` says whether it's a table, a view, or something else."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata006",
   "metadata": {},
   "outputs": [],
   "source": [
    "service = odata(\"\")\n",
    "datasets = pd.DataFrame(service[\"value\"])\n",
    "datasets[[\"name\", \"url\", \"source\", \"role\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata007",
   "metadata": {},
   "source": [
    "## 4. Read a dataset's schema from `$metadata`\n",
    "\n",
    "`$metadata` is the CSDL document that Power BI and Excel read to learn column names and types. Request it for a single dataset by adding `/$metadata` to its path. It's XML, so parse it with the standard library."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata008",
   "metadata": {},
   "outputs": [],
   "source": [
    "import xml.etree.ElementTree as ET\n",
    "\n",
    "EDM = \"{http://docs.oasis-open.org/odata/ns/edm}\"\n",
    "\n",
    "metadata = ET.fromstring(fetch(\"demo/security/nvd_vulnerabilities/$metadata\").content)\n",
    "\n",
    "columns = pd.DataFrame(\n",
    "    [\n",
    "        {\"column\": prop.get(\"Name\"), \"type\": prop.get(\"Type\"), \"nullable\": prop.get(\"Nullable\")}\n",
    "        for prop in metadata.iter(f\"{EDM}Property\")\n",
    "    ]\n",
    ")\n",
    "columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata009",
   "metadata": {},
   "source": [
    "Alongside the columns, the metadata carries annotations about the dataset itself, such as its description, the snapshot you're reading and how many rows it holds:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata010",
   "metadata": {},
   "outputs": [],
   "source": [
    "for annotation in metadata.iter(f\"{EDM}Annotation\"):\n",
    "    term = annotation.get(\"Term\")\n",
    "    if term in (\"Custom.Description\", \"Custom.Snapshot.Id\", \"Custom.Snapshot.TotalRecords\", \"Custom.LastModified\", \"Custom.Tags.Count\"):\n",
    "        print(f\"{term:<30} {annotation.get('String') or annotation.get('Int')}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata011",
   "metadata": {},
   "source": [
    "## 5. Query a dataset\n",
    "\n",
    "### `$top`\n",
    "\n",
    "Limit the number of rows returned. Without `$top` you get the default page of 100 rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata012",
   "metadata": {},
   "outputs": [],
   "source": [
    "first = odata(\"demo/security/nvd_vulnerabilities\", top=5)\n",
    "pd.DataFrame(first[\"value\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata013",
   "metadata": {},
   "source": [
    "### `$select`\n",
    "\n",
    "Return only the columns you need. The descriptions are long, so leaving them out keeps responses small."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata014",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = odata(\"demo/security/nvd_vulnerabilities\", select=\"cve_id,published_at,severity,cvss_score,vendor,product\", top=5)\n",
    "pd.DataFrame(result[\"value\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata015",
   "metadata": {},
   "source": [
    "### `$filter`\n",
    "\n",
    "Restrict rows with an OData filter expression. Strings are in single quotes. This is the guide's example on `demo.geopolitics.countries`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata016",
   "metadata": {},
   "outputs": [],
   "source": [
    "europe = odata(\"demo/geopolitics/countries\", filter=\"region eq 'Europe'\", orderby=\"country_name_common\", select=\"country_name_common,capital,subregion\", top=5)\n",
    "pd.DataFrame(europe[\"value\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata017",
   "metadata": {},
   "source": [
    "Dates and timestamps are written **unquoted**, in ISO 8601. Entries CISA has added to its known-exploited catalog since the start of 2025:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata018",
   "metadata": {},
   "outputs": [],
   "source": [
    "kev_recent = odata(\n",
    "    \"demo/security/cisa_kev\",\n",
    "    filter=\"date_added ge 2025-01-01\",\n",
    "    select=\"cve_id,vendor_project,product,vulnerability_name,date_added\",\n",
    "    top=5,\n",
    ")\n",
    "pd.DataFrame(kev_recent[\"value\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata019",
   "metadata": {},
   "source": [
    "Quoting the date makes it a string, and comparing a string to a `DATE` column is rejected with a `400` that says what to write instead:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata020",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    odata(\"demo/security/cisa_kev\", filter=\"date_added ge '2025-01-01'\", top=5)\n",
    "except ODataError as error:\n",
    "    print(error)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata021",
   "metadata": {},
   "source": [
    "`now()` and an ISO 8601 duration express a rolling window, evaluated on the server, so the query stays correct when you re-run it tomorrow. Critical CVEs published in the last 30 days:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata022",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_30_days = odata(\n",
    "    \"demo/security/nvd_vulnerabilities\",\n",
    "    filter=\"severity eq 'CRITICAL' and published_at ge now() sub duration'P30D'\",\n",
    "    select=\"cve_id,published_at,cvss_score,vendor,product\",\n",
    "    orderby=\"published_at desc\",\n",
    "    top=10,\n",
    ")\n",
    "pd.DataFrame(last_30_days[\"value\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata023",
   "metadata": {},
   "source": [
    "### `$orderby`\n",
    "\n",
    "Sort by one or more columns, each `asc` (the default) or `desc`. The highest-scoring CVEs, newest first among ties:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata024",
   "metadata": {},
   "outputs": [],
   "source": [
    "top_scores = odata(\n",
    "    \"demo/security/nvd_vulnerabilities\",\n",
    "    select=\"cve_id,published_at,cvss_score,vendor,product\",\n",
    "    orderby=\"cvss_score desc,published_at desc\",\n",
    "    top=10,\n",
    ")\n",
    "pd.DataFrame(top_scores[\"value\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata025",
   "metadata": {},
   "source": [
    "### `$count`\n",
    "\n",
    "`$count=true` adds `@odata.count`, the number of rows the filter matched before `$top` was applied. With `$top=0` you get just the count and no rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata026",
   "metadata": {},
   "outputs": [],
   "source": [
    "critical = odata(\"demo/security/nvd_vulnerabilities\", filter=\"severity eq 'CRITICAL'\", count=True, top=0)\n",
    "print(f\"{critical['@odata.count']:,} critical CVEs\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata027",
   "metadata": {},
   "source": [
    "## 6. Page through a result with `@odata.nextLink`\n",
    "\n",
    "When a query matches more rows than `$top`, the response has exactly `$top` rows plus an `@odata.nextLink` pointing at the next page. Keep following it until a response comes back without one.\n",
    "\n",
    "Two details from the guide matter here:\n",
    "\n",
    "- The link is **relative**, so resolve it against `https://odata.opteryx.app`.\n",
    "- The `$` in its query string arrives percent-encoded as `%24`. That's equivalent; pass the link through unchanged rather than rewriting it.\n",
    "\n",
    "There are more than 30,000 critical CVEs, so this loop stops after `MAX_PAGES` pages. Remove the limit to read the whole result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata028",
   "metadata": {},
   "outputs": [],
   "source": [
    "MAX_PAGES = 3\n",
    "\n",
    "page = odata(\n",
    "    \"demo/security/nvd_vulnerabilities\",\n",
    "    filter=\"severity eq 'CRITICAL'\",\n",
    "    select=\"cve_id,published_at,cvss_score\",\n",
    "    orderby=\"published_at desc\",\n",
    "    top=500,\n",
    ")\n",
    "pages = [pd.DataFrame(page[\"value\"])]\n",
    "\n",
    "while \"@odata.nextLink\" in page and len(pages) < MAX_PAGES:\n",
    "    print(\"  nextLink:\", page[\"@odata.nextLink\"])\n",
    "    page = odata(urljoin(HOST, page[\"@odata.nextLink\"]))\n",
    "    pages.append(pd.DataFrame(page[\"value\"]))\n",
    "\n",
    "critical_pages = pd.concat(pages, ignore_index=True)\n",
    "more = \"more pages remain\" if \"@odata.nextLink\" in page else \"that was the last page\"\n",
    "print(f\"Read {len(critical_pages):,} rows in {len(pages)} pages; {more}.\")\n",
    "critical_pages.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata029",
   "metadata": {},
   "source": [
    "## 7. Aggregate server-side with `$apply`\n",
    "\n",
    "`$apply` groups and aggregates on the server, so only the summary comes back. `$count`, `sum`, `average`, `min` and `max` are available. The guide's example counts countries per region:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata030",
   "metadata": {},
   "outputs": [],
   "source": [
    "regions = odata(\"demo/geopolitics/countries\", apply=\"groupby((region),aggregate($count as country_count))\")\n",
    "pd.DataFrame(regions[\"value\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata031",
   "metadata": {},
   "source": [
    "The same pattern over all ~400,000 NVD records, counting CVEs and averaging their CVSS score per severity band. `$orderby` can sort by an aggregate alias on the server, but here the summary is sorted in pandas:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata032",
   "metadata": {},
   "outputs": [],
   "source": [
    "by_severity = odata(\n",
    "    \"demo/security/nvd_vulnerabilities\",\n",
    "    apply=\"groupby((severity),aggregate($count as cves,cvss_score with average as avg_cvss))\",\n",
    ")\n",
    "pd.DataFrame(by_severity[\"value\"]).sort_values(\"cves\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata033",
   "metadata": {},
   "source": [
    "`filter()` inside `$apply` narrows the rows before grouping. The vendors with the most known-exploited vulnerabilities used in ransomware campaigns. One row comes back per vendor, so `$top` is raised above the default 100 to get every group in one page:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata034",
   "metadata": {},
   "outputs": [],
   "source": [
    "ransomware = odata(\n",
    "    \"demo/security/cisa_kev\",\n",
    "    apply=\"filter(known_ransomware_campaign_use eq 'Known')/groupby((vendor_project),aggregate($count as kevs))\",\n",
    "    top=1000,\n",
    ")\n",
    "by_vendor = pd.DataFrame(ransomware[\"value\"]).sort_values(\"kevs\", ascending=False).head(10)\n",
    "by_vendor.plot.barh(x=\"vendor_project\", y=\"kevs\", legend=False, figsize=(8, 4), title=\"Known ransomware-linked KEVs by vendor\").invert_yaxis()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata035",
   "metadata": {},
   "source": [
    "## 8. Datasets that need a token\n",
    "\n",
    "Everything outside `demo` - your own datasets, and the `public` workspace - needs a bearer token on every request:\n",
    "\n",
    "```\n",
    "Authorization: Bearer <token>\n",
    "```\n",
    "\n",
    "It must be a JWT access token. A personal access token from Opteryx Studio (**Settings → API Tokens**) gives you a client ID and secret; exchange that pair for an access token at the [Authentication API](https://docs.opteryx.app/docs/reference/api/authentication-api), then send the access token. The raw client ID and secret aren't accepted by the OData service.\n",
    "\n",
    "Keep the secret out of the notebook. In Colab, add `OPTERYX_CLIENT_SECRET` under the key icon in the left sidebar (Colab Secrets) and turn on notebook access; elsewhere, the cell asks for it in a hidden prompt. Set `CLIENT_ID` and `MY_DATASET` to run it; while `CLIENT_ID` is empty, the cell does nothing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "odata036",
   "metadata": {},
   "outputs": [],
   "source": [
    "from getpass import getpass\n",
    "\n",
    "CLIENT_ID = \"\"    # from Settings → API Tokens\n",
    "MY_DATASET = \"\"   # path from the service document, e.g. \"acme/sales/orders\"\n",
    "\n",
    "if CLIENT_ID:\n",
    "    try:\n",
    "        from google.colab import userdata\n",
    "\n",
    "        CLIENT_SECRET = userdata.get(\"OPTERYX_CLIENT_SECRET\")\n",
    "    except Exception:\n",
    "        CLIENT_SECRET = getpass(\"Client secret: \")\n",
    "\n",
    "    token = requests.post(\n",
    "        \"https://authenticate.opteryx.app/token\",\n",
    "        data={\"grant_type\": \"client_credentials\", \"client_id\": CLIENT_ID, \"client_secret\": CLIENT_SECRET},\n",
    "        timeout=30,\n",
    "    )\n",
    "    token.raise_for_status()\n",
    "    # Every later request from this session carries the token. It's short-lived: re-run this cell when it expires.\n",
    "    session.headers[\"Authorization\"] = f\"Bearer {token.json()['access_token']}\"\n",
    "\n",
    "    mine = pd.DataFrame(odata(\"\")[\"value\"])\n",
    "    display(mine[[\"name\", \"url\", \"source\", \"role\"]])\n",
    "    if MY_DATASET:\n",
    "        display(pd.DataFrame(odata(MY_DATASET, top=10)[\"value\"]))\n",
    "else:\n",
    "    print(\"Set CLIENT_ID (and optionally MY_DATASET), then run this cell again.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "odata037",
   "metadata": {},
   "source": [
    "## Things to know\n",
    "\n",
    "- **Read-only.** OData here has no `INSERT`, `UPDATE` or `DELETE`.\n",
    "- **Empty means empty.** A `200` with an empty `value` means the query matched no rows; any failure is a non-2xx status with an error body.\n",
    "- **Not implemented.** `$search` and `$expand` return `501`.\n",
    "- **From a shell.** Wrap the URL in single quotes so the shell doesn't expand `$top`, and encode spaces as `%20` (or use `curl -G --data-urlencode '$filter=...'`).\n",
    "- **Power BI and Excel.** Point either at `https://odata.opteryx.app/api/v4/` or at a dataset URL; both follow `@odata.nextLink` for you."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}