How to query .xel log files in Azure SQL DB using T-sql
Issue
A recent issue was brought to our attention that customers could not query .xel log files in an Azure SQL DB using t-sql command. The customers complained that when they ran the command, they received column headers but no content whereas they know that there is content in the logs because they were able to open them with SSMS using Merge Extended Event Files. Here was the T-sql command used by the customer:
select * from sys.fn_get_audit_file (‘https://mydbastorage.blob.core.windows.net/sqldbauditlogs/servername/dbname/SqlDbAuditing_Audit_NoRetention/*.xel‘, NULL, NULL);
select * from sys.fn_get_audit_file (‘https://mydbastorage.blob.core.windows.net/sqldbauditlogs/servername/dbname/SqlDbAuditing_Audit_NoRetention/2023-06-29/17_20_28_*.xel‘, NULL, NULL);
Error
While no error is generated, the customers only received column headers but no data.
Permissions
Per our official documentation, CONTROL DATABASE permission is needed to query the .xel log files:
Workaround
In this case the customer confirmed he had CONTROL DATABASE permission (as noted above) on his SQL server login, however he was still unable to query the .xel files using the t-sql mentioned above. We finally recommended him to change his t-sql query slightly and he was able to successfully query the .xel log files after that. Please note that the asterisk wildcard ‘*’ doesn’t work in Azure SQL DB, hence if you use ‘*’ in your T-sql command for auditing, it won’t work. Here’s the updated T-sql command we recommended to the customer after which he was able to query the database:
select * from sys.fn_get_audit_file (‘https://mydbastorage.blob.core.windows.net/sqldbauditlogs/servername/dbname/SqlDbAuditing_Audit_NoRetention/2022-06-28/‘, NULL, NULL);
References
Microsoft Tech Community – Latest Blogs –Read More
