Reading sql pool auditing files (.xel)

Question

I am trying to read audit files in Synapse analytics, made by automated auditing, using query

select * from
 sys.fn_get_audit_file('https://datalake.dfs.core.windows.net/path/10_00_24_565_6.xel'
    ,DEFAULT
    ,DEFAULT
);

But it just gives me empty table with column names. I downloaded that file and it show data in SSMS. What is wrong wiht my query?

Answer

Hello Jani Hämäläinen,

Welcome to the Microsoft Q&A forum.

You can extract audit log using ‘sys.fn_get_audit_file’ function when connection is established on synaspe dedicated sql pool or master databaase.

The issue you are facing is due to permissions. Please check the RBAC roles on the storage account. Providing "Storage Blob Contributor" to the managed identify of the workspace should resolve the issue.

The behavior of returning an empty set (because the file is skipped) is expected (as opposed to an error) when the file can't be read.

https://video2.skills-academy.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql?view=sql-server-ver16&tabs=sqlserver#permissions

I hope this answers your question.

Share via

Reading sql pool auditing files (.xel)

1 answer