How can I use sys.fn_get_audit_file_v2 (or sys.fn_get_audit_file) to read .sqlaudit files stored in blob storage?

Question

How can I query my .sqlaudit files stored in a blob container within an azure storage account?

Details:

I have thousands of .sqlaudit files created from Server Audit configurations on over a hundred SQL 2016 and SQL 2019 servers.

When I place these files on another server with SQL 2016, I can run a query within SSMS to see the data contained within the .sqlaudit files[1]. This is okay, however, I'm really hoping to store these .sqlaudit files in an azure storage account in a blob container.

But when I place these .sqlaudit files into a blob container and then use sys.fn_get_audit_file_v2 within an Azure SQL database to query the files, I get an empty result set [2].

Thus: Is there a way to use Azure SQL database or SQL Server 2016/2019 to query .sqlaudit files stored in a blob container in an azure storage account?

Thank you,

Nathan

PS: Upon reading the documentation, https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-v2-transact-sql?view=sql-server-ver16 , and focusing on the file_path parameter, it appears that the function is looking for .xel files and also the server name and database name in the path. There is also a link to setup auditing for azure sql databases. This leads me to believe that perhaps sys.fn_get_audit_file_v2 is intended to read .xel files created from an azure sql auditing configuration, and not .sqlaudit files from SQL Server 2016 or 2019? This is only speculation, since I can't find any conclusive information.

[1] Successful querying of locally stored .sqlaudit files from traditional SQL Server 2016 and SSMS

https://i.imgur.com/OGChoNS.png

Successful query syntax:

SELECT * FROM sys.fn_get_audit_file('G:\auditing\Touchstone\*.sqlaudit', DEFAULT, DEFAULT)

[2] No results when I use Azure SQL to try to query a blob storage container that has the exact same .sqlaudit files

https://i.imgur.com/BVTi0Fx.png

Query syntax I'm trying to use:

SELECT *

FROM sys.fn_get_audit_file_v2(

'https://redacted.blob.core.windows.net/container-name/MyAudit_3E764A9C-AAA2-4BCC-BB68-8C563CE2F766_0_133740627016170000.sqlaudit'

,NULL

,NULL

,NULL

,NULL

)

Answer 1

Hello Nathan Given,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you would like to use sys.fn_get_audit_file_v2 (or sys.fn_get_audit_file) to read .sqlaudit files stored in blob storage.

It seems that the sys.fn_get_audit_file_v2 function is designed to work with .xel files, which are typically generated by Azure SQL Database auditing, rather than .sqlaudit files from SQL Server 2016 or 2019. This likely explains why you’re getting an empty result set when querying .sqlaudit files stored in Azure Blob Storage. One workaround is to download the .sqlaudit files locally and use SQL Server Management Studio (SSMS) to query them, ensuring compatibility with the file format.

Alternatively, you could consider using Azure Log Analytics to send and analyze your audit logs, or even Power BI for a more visual approach. For better reference for these recommendations : https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-analyze-audit-logs?view=azuresql

If these options aren’t feasible, developing a custom solution to read and convert the .sqlaudit files into a compatible format for Azure SQL Database might be necessary. These methods can help you effectively manage and query your audit data stored in Azure Blob Storage.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.