How to query .xel log files in Azure SQL DB using T-sql

problem

A recent issue was brought to our attention where customers were unable to query .xel log files in Azure SQL DB using t-sql commands. The customer complained that while they received the column headers when running the command and there was no content, they knew the log had content because they were able to open the log with SSMS using Extended Event File Merge. The T-sql command used by our customer is:

select * from sys.fn_get_audit_file(‘https://mydbastorage.blob.core.windows.net/sqldbauditlogs/servername/dbname/SqlDbAuditing_Audit_NoRe…‘, NULL, NULL);
select * from sys.fn_get_audit_file(‘https://mydbastorage.blob.core.windows.net/sqldbauditlogs/servername/dbname/SqlDbAuditing_Audit_NoRe…‘, NULL, NULL);

error

No error is generated, but the customer only receives column headers and no data.

authority

According to the official document, control database Permission is required to query .xel log files.

How to solve

In this case, the customer control database As mentioned above, I have permission to log in to the SQL server, but I cannot query the .xel file using t-sql as mentioned above. Finally we encouraged him to make some changes to his t-sql query and after that he was able to successfully query the .xel log files. The asterisk wildcard ‘*’ does not work in Azure SQL DB, so using ‘*’ in T-sql commands for auditing will not work.. Below are the updated T-sql commands we recommend for customers to query their database.

select * from sys.fn_get_audit_file(‘https://mydbastorage.blob.core.windows.net/sqldbauditlogs/servername/dbname/SqlDbAuditing_Audit_NoRe…‘, NULL, NULL);

References

https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-tr…

https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-v2…