# SIEM Query Utils
<!-- WARNING: THIS FILE WAS AUTOGENERATED! DO NOT EDIT! -->
## Install
[](https://github.com/wagov/nbdev-squ/actions/workflows/deploy.yaml)
[](https://pypi.org/project/nbdev-squ/)
[](https://securityscorecards.dev/viewer/?uri=github.com/wagov/nbdev-squ)
Below is how to install in a plain python 3.11+ environment
``` sh
pip install nbdev-squ
```
The installation can also be run in a notebook (we tend to use
[JupyterLab Desktop](https://github.com/jupyterlab/jupyterlab-desktop)
for local dev). The `SQU_CONFIG` env var indicates to nbdev_squ it
should load the json secret *squconfig-`my_keyvault_tenantid`* from the
`my_kevault_name` keyvault.
``` python
%pip install nbdev-squ
import os; os.environ["SQU_CONFIG"] = "{{ my_keyvault_name }}/{{ my_keyvault_tenantid }}"
from nbdev_squ import api
# do cool notebook stuff with api
```
### Security considerations
The contents of the keyvault secret are loaded into memory and cached in
the
[user_cache_dir](https://platformdirs.readthedocs.io/en/latest/api.html#cache-directory)
which should be a temporary secure directory restricted to the single
user. Please ensure that the system this library is used on disallows
access and/or logging of the user cache directory to external locations,
and is on an encrypted disk (a common approach is to use isolated VMs
and workstations for sensitive activities).
## How to use
*Note: If you create/use a Github Codespace on any of the wagov repos,
SQU_CONFIG should be configured automatically.*
Before using, config needs to be loaded into `squ.core.cache`, which can
be done automatically from json in a keyvault by setting the env var
`SQU_CONFIG` to `"keyvault/tenantid"`.
``` bash
export SQU_CONFIG="{{ keyvault }}/{{ tenantid }}"
```
Can be done in python before import from nbdev_squ as well:
``` python
import os; os.environ["SQU_CONFIG"] = "{{ keyvault }}/{{ tenantid }}"
```
``` python
from nbdev_squ import api
import io, pandas
# Load workspace info from datalake blob storage
df = api.list_workspaces(fmt="df"); print(df.shape)
# Load workspace info from introspection of azure graph
df = api.list_securityinsights(); print(df.shape)
# Kusto query to Sentinel workspaces via Azure Lighthouse
df = api.query_all("SecurityIncident | take 20", fmt="df"); print(df.shape)
# Kusto queries to Sentinel workspaces via Azure Lighthouse (batches up to 100 queries at a time)
df = api.query_all(["SecurityAlert | take 20" for a in range(10)]); print(df.shape)
# Kusto query to ADX
#df = api.adxtable2df(api.adx_query("kusto query | take 20"))
# General azure cli cmd
api.azcli(["config", "set", "extension.use_dynamic_install=yes_without_prompt"])
print(len(api.azcli(["account", "list"])))
# Various pre-configured api clients
# RunZero
response = api.clients.runzero.get("/export/org/assets.csv", params={"search": "has_public:t AND alive:t AND (protocol:rdp OR protocol:vnc OR protocol:teamviewer OR protocol:telnet OR protocol:ftp)"})
pandas.read_csv(io.StringIO(response.text)).head(10)
# Jira
pandas.json_normalize(api.clients.jira.jql("updated > -1d")["issues"]).head(10)
# AbuseIPDB
api.clients.abuseipdb.check_ip("1.1.1.1")
# TenableIO
pandas.DataFrame(api.clients.tio.scans.list()).head(10)
```
``` python
badips_df = api.query_all("""
SecurityIncident
| where Classification == "TruePositive"
| mv-expand AlertIds
| project tostring(AlertIds)
| join SecurityAlert on $left.AlertIds == $right.SystemAlertId
| mv-expand todynamic(Entities)
| project Entities.Address
| where isnotempty(Entities_Address)
| distinct tostring(Entities_Address)
""", timespan=pandas.Timedelta("45d"))
```
``` python
df = api.query_all("find where ClientIP startswith '172.16.' | evaluate bag_unpack(pack_) | take 40000")
```
``` python
df = api.query_all("""union withsource="_table" *
| extend _ingestion_time_bin = bin(ingestion_time(), 1h)
| summarize take_any(*) by _table, _ingestion_time_bin
| project pack=pack_all(true)""")
```
``` python
import json
pandas.DataFrame(list(df["pack"].apply(json.loads)))
```
## Secrets template
The below json can be used as a template for saving your own json into
*`my_keyvault_name`/squconfig-`my_keyvault_tenantid`* to use with this
library:
``` json
{
"config_version": "20240101 - added ??? access details",
"datalake_blob_prefix": "https://???/???",
"datalake_subscription": "???",
"datalake_account": "???.blob.core.windows.net",
"datalake_container": "???",
"kql_baseurl": "https://raw.githubusercontent.com/???",
"azure_dataexplorer": "https://???.???.kusto.windows.net/???",
"tenant_id": "???",
"jira_url": "https://???.atlassian.net",
"jira_username": "???@???",
"jira_password": "???",
"runzero_apitoken": "???",
"abuseipdb_api_key": "???",
"tenable_access_key": "???",
"tenable_secret_key": "???",
}
```
Raw data
{
"_id": null,
"home_page": "https://github.com/wagov/nbdev-squ",
"name": "nbdev-squ",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.10",
"maintainer_email": null,
"keywords": "nbdev jupyter notebook python",
"author": "Adon Metcalfe",
"author_email": "adonm@fastmail.fm",
"download_url": "https://files.pythonhosted.org/packages/b6/ed/a8911d7cc316597aced0883eaa9699b094f065f77ff8a570efbcd50b3a1c/nbdev_squ-1.3.5.tar.gz",
"platform": null,
"description": "# SIEM Query Utils\n\n\n<!-- WARNING: THIS FILE WAS AUTOGENERATED! DO NOT EDIT! -->\n\n## Install\n\n[](https://github.com/wagov/nbdev-squ/actions/workflows/deploy.yaml)\n[](https://pypi.org/project/nbdev-squ/)\n[](https://securityscorecards.dev/viewer/?uri=github.com/wagov/nbdev-squ)\n\nBelow is how to install in a plain python 3.11+ environment\n\n``` sh\npip install nbdev-squ\n```\n\nThe installation can also be run in a notebook (we tend to use\n[JupyterLab Desktop](https://github.com/jupyterlab/jupyterlab-desktop)\nfor local dev). The `SQU_CONFIG` env var indicates to nbdev_squ it\nshould load the json secret *squconfig-`my_keyvault_tenantid`* from the\n`my_kevault_name` keyvault.\n\n``` python\n%pip install nbdev-squ\nimport os; os.environ[\"SQU_CONFIG\"] = \"{{ my_keyvault_name }}/{{ my_keyvault_tenantid }}\" \n\nfrom nbdev_squ import api\n# do cool notebook stuff with api\n```\n\n### Security considerations\n\nThe contents of the keyvault secret are loaded into memory and cached in\nthe\n[user_cache_dir](https://platformdirs.readthedocs.io/en/latest/api.html#cache-directory)\nwhich should be a temporary secure directory restricted to the single\nuser. Please ensure that the system this library is used on disallows\naccess and/or logging of the user cache directory to external locations,\nand is on an encrypted disk (a common approach is to use isolated VMs\nand workstations for sensitive activities).\n\n## How to use\n\n*Note: If you create/use a Github Codespace on any of the wagov repos,\nSQU_CONFIG should be configured automatically.*\n\nBefore using, config needs to be loaded into `squ.core.cache`, which can\nbe done automatically from json in a keyvault by setting the env var\n`SQU_CONFIG` to `\"keyvault/tenantid\"`.\n\n``` bash\nexport SQU_CONFIG=\"{{ keyvault }}/{{ tenantid }}\"\n```\n\nCan be done in python before import from nbdev_squ as well:\n\n``` python\nimport os; os.environ[\"SQU_CONFIG\"] = \"{{ keyvault }}/{{ tenantid }}\"\n```\n\n``` python\nfrom nbdev_squ import api\nimport io, pandas\n\n# Load workspace info from datalake blob storage\ndf = api.list_workspaces(fmt=\"df\"); print(df.shape)\n\n# Load workspace info from introspection of azure graph\ndf = api.list_securityinsights(); print(df.shape)\n\n# Kusto query to Sentinel workspaces via Azure Lighthouse\ndf = api.query_all(\"SecurityIncident | take 20\", fmt=\"df\"); print(df.shape)\n\n# Kusto queries to Sentinel workspaces via Azure Lighthouse (batches up to 100 queries at a time)\ndf = api.query_all([\"SecurityAlert | take 20\" for a in range(10)]); print(df.shape)\n\n# Kusto query to ADX\n#df = api.adxtable2df(api.adx_query(\"kusto query | take 20\"))\n\n# General azure cli cmd\napi.azcli([\"config\", \"set\", \"extension.use_dynamic_install=yes_without_prompt\"])\nprint(len(api.azcli([\"account\", \"list\"])))\n\n# Various pre-configured api clients\n\n# RunZero\nresponse = api.clients.runzero.get(\"/export/org/assets.csv\", params={\"search\": \"has_public:t AND alive:t AND (protocol:rdp OR protocol:vnc OR protocol:teamviewer OR protocol:telnet OR protocol:ftp)\"})\npandas.read_csv(io.StringIO(response.text)).head(10)\n\n# Jira\npandas.json_normalize(api.clients.jira.jql(\"updated > -1d\")[\"issues\"]).head(10)\n\n# AbuseIPDB\napi.clients.abuseipdb.check_ip(\"1.1.1.1\")\n\n# TenableIO\npandas.DataFrame(api.clients.tio.scans.list()).head(10)\n```\n\n``` python\nbadips_df = api.query_all(\"\"\"\nSecurityIncident\n| where Classification == \"TruePositive\"\n| mv-expand AlertIds\n| project tostring(AlertIds)\n| join SecurityAlert on $left.AlertIds == $right.SystemAlertId\n| mv-expand todynamic(Entities)\n| project Entities.Address\n| where isnotempty(Entities_Address)\n| distinct tostring(Entities_Address)\n\"\"\", timespan=pandas.Timedelta(\"45d\"))\n```\n\n``` python\ndf = api.query_all(\"find where ClientIP startswith '172.16.' | evaluate bag_unpack(pack_) | take 40000\")\n```\n\n``` python\ndf = api.query_all(\"\"\"union withsource=\"_table\" *\n| extend _ingestion_time_bin = bin(ingestion_time(), 1h)\n| summarize take_any(*) by _table, _ingestion_time_bin\n| project pack=pack_all(true)\"\"\")\n```\n\n``` python\nimport json\npandas.DataFrame(list(df[\"pack\"].apply(json.loads)))\n```\n\n## Secrets template\n\nThe below json can be used as a template for saving your own json into\n*`my_keyvault_name`/squconfig-`my_keyvault_tenantid`* to use with this\nlibrary:\n\n``` json\n{\n \"config_version\": \"20240101 - added ??? access details\",\n \"datalake_blob_prefix\": \"https://???/???\",\n \"datalake_subscription\": \"???\",\n \"datalake_account\": \"???.blob.core.windows.net\",\n \"datalake_container\": \"???\",\n \"kql_baseurl\": \"https://raw.githubusercontent.com/???\",\n \"azure_dataexplorer\": \"https://???.???.kusto.windows.net/???\",\n \"tenant_id\": \"???\",\n \"jira_url\": \"https://???.atlassian.net\",\n \"jira_username\": \"???@???\",\n \"jira_password\": \"???\",\n \"runzero_apitoken\": \"???\",\n \"abuseipdb_api_key\": \"???\",\n \"tenable_access_key\": \"???\",\n \"tenable_secret_key\": \"???\",\n}\n```\n",
"bugtrack_url": null,
"license": "Apache Software License 2.0",
"summary": "Python SIEM Query Utils nbdev edition",
"version": "1.3.5",
"project_urls": {
"Homepage": "https://github.com/wagov/nbdev-squ"
},
"split_keywords": [
"nbdev",
"jupyter",
"notebook",
"python"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "fd8ccc4c37d889172ec4c749493f6b2a63e626a91fb12014a51636b1240fa632",
"md5": "4dbd873490fb54d797d789177ef0e172",
"sha256": "a0d3a5141716dd03e1da1dc15f73aa4093269126200b54b667bd2ba25813a51a"
},
"downloads": -1,
"filename": "nbdev_squ-1.3.5-py3-none-any.whl",
"has_sig": false,
"md5_digest": "4dbd873490fb54d797d789177ef0e172",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.10",
"size": 266809,
"upload_time": "2024-04-16T22:44:35",
"upload_time_iso_8601": "2024-04-16T22:44:35.640212Z",
"url": "https://files.pythonhosted.org/packages/fd/8c/cc4c37d889172ec4c749493f6b2a63e626a91fb12014a51636b1240fa632/nbdev_squ-1.3.5-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "b6eda8911d7cc316597aced0883eaa9699b094f065f77ff8a570efbcd50b3a1c",
"md5": "834589db1808fb95354231424d7cd255",
"sha256": "cf6359a6715628a619ffdcb8bb50e382fb9e02c783d672eb37061270b6a25210"
},
"downloads": -1,
"filename": "nbdev_squ-1.3.5.tar.gz",
"has_sig": false,
"md5_digest": "834589db1808fb95354231424d7cd255",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.10",
"size": 510763,
"upload_time": "2024-04-16T22:45:32",
"upload_time_iso_8601": "2024-04-16T22:45:32.954180Z",
"url": "https://files.pythonhosted.org/packages/b6/ed/a8911d7cc316597aced0883eaa9699b094f065f77ff8a570efbcd50b3a1c/nbdev_squ-1.3.5.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-04-16 22:45:32",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "wagov",
"github_project": "nbdev-squ",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "nbdev-squ"
}
JSON
