No support for Basic and Auxiliary tables in Azure Monitor Logs?

[JPvRiel]

Is your feature request related to a problem? Please describe.

I'm unable to query basic tables using the MSSentinel data provider, yet these public cheaper table types are a cost saving feature for Sentinel and Log Analytics.

Describe the solution you'd like

qry_prov_la_basic = mp.QueryProvider(data_environment="MSSentinel")
qry_prov_la_basic.connect(workspace="LogAnalyticsBasicProd")
df = qry_prov_la_basic.exec_query("CommonSecurityLogBasic_CL | take 1")

Describe alternatives you've considered

None in detail. Perhaps there's some alternate plain log analytics data provider for mysticpy? Perhaps KQLMagic doesn't have this limitation?

Additional context

Error message

"message": "'take' operator: Failed to resolve table or column expression named 'CommonSecurityLogBasic_CL'"

Same query runs fine in Azure Monitor or Sentinel

[ianhelle]

Hi @JPvRiel,
Hmm - this is an interesting problem. I don't think basic tables existed when the driver was written and I'm not sure that the azure-monitor-query SDK package supports them.
Seems that have a different API endpoint and only support a restricted set of KQL statements (e.g. no time range supported in the query itself). I'm going to investigate whether there are plans for Azure monitor SDK to support this.

[ianhelle]

I've created an experimental (i.e. not really tested) driver that uses the search endpoint described here
https://github.com/MicrosoftDocs/azure-monitor-docs/blob/main/articles/azure-monitor/logs/basic-logs-query.md

You create it with;

query_prov = QueryProvider("MSSentinelSearch")

Not sure if you can test it from the branch/PR I created but I'll add it to the next release - let me know if it works (it's going to have some rough edges).

[JPvRiel]

@ianhelle thanks for the quick work on adding an enhancement. I did try out the PR.

Seems like the query provider isn't correctly calling/connecting to/with the new search driver you wrote?

mkdir -p ./src/msticpy
git clone --depth 1 https://github.com/microsoft/msticpy.git src/msticpy
cd src/msticpy
git fetch origin pull/825/head:pr-825-branch --depth 1
git checkout pr-825-branch

Hacked in/forced your PR source spec to be found first, e.g.

# First prefix the module search path to prioritise the local source
import sys
sys.path.insert(0, './src/msticpy')

# Show where spec would be found
import importlib
print(importlib.util.find_spec('msticpy'))

# Usual imports
import datetime
import msticpy as mp

# Config
mp.init_notebook()
qry_prov_basic_search = mp.QueryProvider('MSSentinelSearch')
qry_prov_basic_search.connect(workspace='BasicLogs')
print(f'Query provider driver: {qry_prov_basic_search.driver_class}')
print(f'Query provider environment: {qry_prov_basic_search.environment}')
print(f'Query provider connections: {qry_prov_basic_search.list_connections()}')

# Prep a small time range to limit basic logs query costs
lookback_period = datetime.timedelta(hours=1)
ingest_grace_period = datetime.timedelta(minutes=15)
end = datetime.datetime.now(datetime.timezone.utc) - ingest_grace_period
start = end - lookback_period
print(f'Start: {start}, End: {end}')

# Test query
df = qry_prov_basic_search.exec_query('SyslogBasic_CL | take 1', start=start, end=end)
print(df)

Script output

ModuleSpec(name='msticpy', loader=<_frozen_importlib_external.SourceFileLoader object at 0x7f0f45c3cad0>, origin='/home/***/Crypt/Work/Code/Detection/SecurityNotebooks/demo/_bugs/msticpy_cannot_query_azure_monitor_basic_logs/./src/msticpy/msticpy/__init__.py', submodule_search_locations=['/home/***/Crypt/Work/Code/Detection/SecurityNotebooks/demo/_bugs/msticpy_cannot_query_azure_monitor_basic_logs/./src/msticpy/msticpy'])
connected
Query provider driver: <class 'msticpy.data.drivers.azure_search_driver.AzureSearchDriver'>
Query provider environment: MSSentinel
Query provider connections: ['Default: BasicLogs']
Start: 2025-02-06 21:02:26.333435+00:00, End: 2025-02-06 22:02:26.333435+00:00

Exception trace

Traceback (most recent call last):
  File "/home/***/Crypt/Work/Code/Detection/SecurityNotebooks/demo/_bugs/msticpy_cannot_query_azure_monitor_basic_logs/test_pr_data_prov_ms_sentinel_search.py", line 31, in <module>
    df = qry_prov_basic_search.exec_query('SyslogBasic_CL | take 1', start=start, end=end)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/***/Crypt/Work/Code/Detection/SecurityNotebooks/demo/_bugs/msticpy_cannot_query_azure_monitor_basic_logs/./src/msticpy/msticpy/data/core/query_provider_connections_mixin.py", line 99, in exec_query
    return self._query_provider.query(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/***/Crypt/Work/Code/Detection/SecurityNotebooks/demo/_bugs/msticpy_cannot_query_azure_monitor_basic_logs/./src/msticpy/msticpy/data/drivers/azure_monitor_driver.py", line 294, in query
    raise MsticpyNotConnectedError(
msticpy.common.exceptions.MsticpyNotConnectedError: ('Workspace not connected.', 'Please run connect() to connect to the workspace', 'before running a query.', 'https://msticpy.readthedocs.io/en/latest/data_acquisition/DataProv-MSSentinel.html')

I'm fairly confident my msticpyconfig.yaml was correct as the same config worked fine when using the MSSentinel provider with an analytic plan table.

[JPvRiel]

@ianhelle , had another go, this time trying to use the driver directly, given I couldn't get the data provider to use the correct driver, but I'm not winning with it and it seems to trip up on passing the required timespan value to the API.

Importing from a src dir pull to test is tricky as there's some init version check code that depends on the package being installed and breaks importing from a source dir. I used a fresh .venv, where I've only imported the requirements needed, but not pip installed msticpy. I also just pulled and tried main as I see the commit was merged.

Source / setup

if [ ! -d './src/msticpy' ]; then
    mkdir -p ./src/msticpy
    git clone --depth 1 https://github.com/microsoft/msticpy.git src/msticpy
fi
git pull
python3.11 -m venv ./.venv/
. ~/.venv/bin/activte
pip install -r src/msticpy/requirements.txt

Test code

#!/usr/bin/env python

# First prefix the module search path to prioritise the local source
import sys
sys.path.insert(0, './src/msticpy')

# Show where spec would be found
import importlib
print(importlib.util.find_spec('msticpy'))

# Usual imports
import datetime
import msticpy

# Mock the version check which breaks import from source due it expecting a distribution package to be installed
def mock_check_version():
    return 'version check mocked'
import msticpy.init.nbinit
msticpy.init.nbinit._check_msticpy_version = mock_check_version

# Config
msticpy.init_notebook()
import msticpy.data.drivers.azure_search_driver  # Unsure why init_notebook does not get this driver module loaded
drv_basic_search = msticpy.data.drivers.azure_search_driver.AzureSearchDriver(debug=True)
drv_basic_search.connect(workspace='BasicLogs')

# Prep a small time range to limit basic logs query costs
lookback_period = datetime.timedelta(hours=1)
ingest_grace_period = datetime.timedelta(minutes=15)
end = datetime.datetime.now(datetime.timezone.utc) - ingest_grace_period
start = end - lookback_period
time_span=dict(
    start=start.isoformat(timespec='seconds'),
    end=end.isoformat(timespec='seconds')
)
print(f'time_span: {time_span}')

# Test query
df, results = drv_basic_search.query_with_results('SyslogBasic_CL | take 1', time_span=time_span)
print(results)
print(df)

Script output

ModuleSpec(name='msticpy', loader=<_frozen_importlib_external.SourceFileLoader object at 0x7f75a8a63ad0>, origin='/home/***/Crypt/Work/Code/Detection/SecurityNotebooks/demo/_bugs/msticpy_cannot_query_azure_monitor_basic_logs/./src/msticpy/msticpy/__init__.py', submodule_search_locations=['/home/***/Crypt/Work/Code/Detection/SecurityNotebooks/demo/_bugs/msticpy_cannot_query_azure_monitor_basic_logs/./src/msticpy/msticpy'])
2025-02-07 01:39:30,307: INFO - AzureMonitorDriver loaded. connect_str  None, kwargs: {'debug': True} (azure_monitor_driver#144)
2025-02-07 01:39:30,307: INFO - WorkspaceConfig created from workspace name BasicLogs (azure_monitor_driver#446)
2025-02-07 01:39:30,309: INFO - WorkspaceConfig created from workspace name BasicLogs (azure_monitor_driver#446)
connected
time_span: {'start': '2025-02-06T22:24:31+00:00', 'end': '2025-02-06T23:24:31+00:00'}
2025-02-07 01:39:31,594: INFO - Time parameters set TimeSpan(start=2025-02-06 22:24:31+00:00, end=2025-02-06 23:24:31+00:00, period=0 days 01:00:00) (azure_monitor_driver#541)

Exception trace:

Traceback (most recent call last):
  File "/home/***/Crypt/Work/Code/Detection/SecurityNotebooks/demo/_bugs/msticpy_cannot_query_azure_monitor_basic_logs/test_pr_data_driver_azure_search.py", line 39, in <module>
    df, results = drv_basic_search.query_with_results('SyslogBasic_CL | take 1', time_span=time_span)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/***/Crypt/Work/Code/Detection/SecurityNotebooks/demo/_bugs/msticpy_cannot_query_azure_monitor_basic_logs/./src/msticpy/msticpy/data/drivers/azure_search_driver.py", line 137, in query_with_results
    results = self._query_search_endpoint(search_url, query_body, timeout)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/***/Crypt/Work/Code/Detection/SecurityNotebooks/demo/_bugs/msticpy_cannot_query_azure_monitor_basic_logs/./src/msticpy/msticpy/data/drivers/azure_search_driver.py", line 170, in _query_search_endpoint
    raise MsticpyKqlConnectionError(
msticpy.common.exceptions.MsticpyKqlConnectionError: ("we've hit an error while running", 'Error 400 from /search endpoint: {"error":{"message":"The request had some invalid properties","code":"BadArgumentError","correlationId":"f6ba21c8-b1f6-46f0-b578-3a7f6e3d1b86","innererror":{"code":"QueryValidationError","message":"Timespan must be supplied for /search API"}}}', ('Connecting to Microsoft Sentinel', 'https://msticpy.readthedocs.io/en/latest/data_acquisition/DataProviders.html#connecting-to-an-azure-sentinel-workspace'))

Not sure if I'm doing something wrong somewhere to test this out.

Looks like the /search API path requires the timespan in some format that isn't working out with your implementation.

See https://learn.microsoft.com/en-us/rest/api/loganalytics/query/get?view=rest-loganalytics-2022-10-27-preview&viewFallbackFrom=rest-loganalytics-2023-09-01&tabs=HTTP#uri-parameters

The timespan over which to query data. This is an ISO8601 time period value.

And https://github.com/MicrosoftDocs/azure-monitor-docs/blob/main/articles/azure-monitor/logs/basic-logs-query.md#api

https://api.loganalytics.io/v1/workspaces/{workspaceId}/search?timespan=P1D

P1D example means period of 1 day (https://en.wikipedia.org/wiki/ISO_8601#Durations)

Not sure how the parameter is meant to be formatted as start and end / between to timestamps and the API documentation samples only show the 'duration' string version.

[JPvRiel]

Raised a separate issue here: #830

[JPvRiel]

Raise a second bug here: #831