topic Warehouse Audit: How to audit SELECT actions? in Data Warehouse

Warehouse Audit: How to audit SELECT actions?

frithjof_v — Sat, 05 Apr 2025 15:05:33 GMT

I'm running this code in a Fabric Notebook to enable warehouse audit for a newly created Fabric warehouse:

import sempy.fabric as fabric client = fabric.FabricRestClient() payload = { "state": "Enabled", "retentionDays": "0" } url = https://api.fabric.microsoft.com/v1/workspaces/<workspaceId>/warehouses/<warehouseId>/settings/sqlAudit response = client.patch(url, json=payload) print(response.json()) response = client.get(url) print(response.json())

I inserted the actual workspace id and warehouse id in the url.

Everything above runs successfully.

Next, I wish to configure audit action groups. I try to do this:

data = [ "DATABASE_OBJECT_PERMISSION_CHANGE_GROUP" ] response = client.post(url + "/setAuditActionsAndGroups", json=data)

The above also works.

Next, I tried to do the following, to specifically audit SELECT statements:

data = [ "SELECT" ] response = client.post(url + "/setAuditActionsAndGroups", json=data)

Then I get the following error:

FabricHTTPException: 500 Internal Server Error for url: https://api.fabric.microsoft.com/v1/workspaces/.../warehouses/.../settings/sqlAudit/setAuditActionsAndGroups Error: {"requestId":"...","errorCode":"InternalServerError","message":"An internal error occurred."} Headers: {'Cache-Control': 'no-store, must-revalidate, no-cache', 'Pragma': 'no-cache', 'Transfer-Encoding': 'chunked', 'Content-Type': 'application/json; charset=utf-8', 'x-ms-public-api-error-code': 'InternalServerError', 'Strict-Transport-Security': 'max-age=31536000; includeSubDomains', 'X-Frame-Options': 'deny', 'X-Content-Type-Options': 'nosniff', 'RequestId': '...', 'Access-Control-Expose-Headers': 'RequestId', 'Date': 'Sat, 05 Apr 2025 14:11:14 GMT'}

My goal is to enable Audit on SELECT actions (just for test purposes, I guess this can accumulate a lot of data, but I just want to enable it for test purposes).

https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-audit-logs#database-level-audit-action-groups-and-actions

The following does work, but this setting also audits many other actions:

data = [ 'BATCH_COMPLETED_GROUP' ] response = client.post(url + "/setAuditActionsAndGroups", json=data)

Here is the blog and docs I followed:

Introducing SQL Audit Logs for Fabric Data Warehouse | Microsoft Fabric-blogg | Microsoft Fabric

Configure SQL Audit Logs in Fabric Data Warehouse (Preview) - Microsoft Fabric | Microsoft Learn

Re: Warehouse Audit: How to audit SELECT actions?

nilendraFabric — Sat, 05 Apr 2025 23:13:58 GMT

Check this

https://github.com/MicrosoftDocs/fabric-docs/blob/main/docs/data-warehouse/configure-sql-audit-logs.md

https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-audit-logs

try:

data = ["SCHEMA_OBJECT_ACCESS_GROUP"]
response = client.post(url + "/setAuditActionsAndGroups", json=data)

-----

SELECT * FROM sys.fn_get_audit_file_v2(
'https://onelake.blob.fabric.microsoft.com/<workspaceId>/<warehouseId>/Audit/sqldbauditlogs/',
default, default, default, default)
WHERE statement_type = 'SELECT'

Other option you can try is , not documented though:

data = [{
"auditActionName": "SELECT",
"objectName": "YourTableName",
"objectType": "TABLE",
"objectSchema": "dbo"
}]
response = client.post(url + "/setAuditActionsAndGroups", json=data)

Re: Warehouse Audit: How to audit SELECT actions?

Anonymous — Fri, 11 Apr 2025 12:26:27 GMT

Hi frithjof_v ,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
B Manikanteswara Reddy

Re: Warehouse Audit: How to audit SELECT actions?

Anonymous — Tue, 15 Apr 2025 05:06:27 GMT

Hi @frithjof_v ,

Re: Warehouse Audit: How to audit SELECT actions?

Anonymous — Mon, 21 Apr 2025 13:50:07 GMT

Hi @frithjof_v ,