Update your scripts to point to a new folder for Server level audit logs

Question

I received this notice from MS and I logged int my Azure portal looking for this but unable to locate. Where can I find this to update the required information?

You're receiving this notice because you use Azure SQL Database Server Audit.

In February 2024, we re-architected the server audit for Azure SQL Database to make audit more available and reliable. With the new change, the feature is more on par with SQL Server and SQL Managed Instance. With this, there’s a change in the folder structure for audit logs for storage account target. Today, server audit logs are written to different folders for each database with database name as folder name in storage account containers However, with this change, all server audit logs will be written to the Master folder.

For read only replicas, we have a read only folder where we store the audit logs. With this change, logs for all replicas will be written to the same Master folder and you can retrieve the audit logs by filtering the new column "is_secondary_replica_true".

Required action

We’re implementing the change region wise, and you’ll receive a notification for each region. You’ll need to change the scripts that refer to the folder structure to retrieve audit logs based on database name for the specific region. This is applicable only for server level auditing and database audit isn’t changed.

• Update the reference for audit logs folder from db name folder to master and use more specific filters to filter for a required database.
• Update the reference for audit logs from read-only replica to master folder and filter using the new parameter as described in example.

This is even applicable for manual queries wherever you use fn_get_audit_table and retrieve

Answer 1

@Phong Nguyen (DA) Thank you for reaching out.

The notice you received is about changes to the Azure SQL Database Server Audit. Here’s how you can update the required information:

You can use the time-based filters using the new function that we release along with database filter to optimize the performance https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-v2-transact-sql

Please see the below sample queries  

To review audit logs for database called test filter using where clause with database_name 

 select * from sys.fn_get_audit_file  

('https://testaudit.blob.core.windows.net/sqldbauditlogs/auditpoc/master/SqlDbAuditing_ServerAudit_NoRetention/2023-01-29/07_06_40_590_0.xel', default, default ) 

where database_name ='test' 

 to review logs for read only replica use the below filter  

 select * from sys.fn_get_audit_file  

('https://testaudit.blob.core.windows.net/sqldbauditlogs/auditpoc/master/SqlDbAuditing_ServerAudit_NoRetention/2023-01-29/07_06_40_590_0.xel', default, default ) 

where is_secondary_replica_true ='true' 

Note: the old audit logs will continue to stay in the previous folders till the retention periods.  

This change is applicable only for Server audit for Azure SQL Database, there is no change for SQL Server and SQL MI  

Regards,

Oury

Answer 2

Thank you for posting this. no changes required from the portal. this is applicable only manual queries where you retrieve audit logs using sys.fn_get_audit_file. if you don't locate the audit files in database name folders, please look at master folder and filter it with database name, like below example.

select * from sys.fn_get_audit_file

('https://testaudit.blob.core.windows.net/sqldbauditlogs/auditpoc/master/SqlDbAuditing_ServerAudit_NoRetention/2023-01-29/07_06_40_590_0.xel', default, default )

where database_name ='test'