Read .xel Files in Blob Storage Using SQL or Python

user_9542989 0
Viewed 94

When auditing is enabled for Azure SQL Database, .xel files are created in a Azure Blob Storage account (when configured to do so).

I know audit logs can be viewed through the Azure Portal by navigating to Auditing on the database server, but I want to be able to read these files using either SQL or Python. My ultimate goal is to read the files into some data structure like a pandas DataFrame in Python, but this can even be handled in SQL, I believe, by creating a VIEW or a STORED PROCEDURE which I can read/call in Python.

How can I go about doing this?

azure-blob-storage azure-sql-database azure sql python
1 Answers

You are looking for sys.fn_get_audit_file, documented here https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql

You may query a single file, or all files beneath a given folder.

Here's an example of querying all files for a given database. Be careful, depending on log volume, this could be expensive.

SELECT * FROM sys.fn_get_audit_file ('https://my_server_name.blob.core.windows.net/sqldbauditlogs/my_server_name/my_database_name/SqlDbAuditing_ServerAudit/',null,null);

Here's an example of just querying the logs from a given day:

SELECT * FROM sys.fn_get_audit_file ('https://my_server_name.blob.core.windows.net/sqldbauditlogs/my_server_name/my_database_name/SqlDbAuditing_ServerAudit/2022-05-27',null,null);
user_77520
Related