Use Auditing to analyze audit logs and reports - Azure SQL Database & Azure Synapse Analytics (2024)

Applies to: Azure SQL Database Azure Synapse Analytics

This article provides an overview of analyzing audit logs using Auditing for Azure SQL Database and Azure Synapse Analytics. You can use Auditing to analyze audit logs stored in:

• Log Analytics
• Event Hubs
• Azure storage

Analyze logs using Log Analytics

If you chose to write audit logs to Log Analytics:

1. Use the Azure portal.

2. Go to the relevant database resource.

3. At the top of the database's Auditing page, select View audit logs.

   

You have two ways to view the logs:

• Selecting Log Analytics at the top of the Audit records page opens the logs view in the Log Analytics workspace, where you can customize the time range and the search query.

  

• Selecting View dashboard at the top of the Audit records page opens a dashboard displaying audit logs information, where you can drill down into Security Insights or Access to Sensitive Data. This dashboard is designed to help you gain security insights for your data. You can also customize the time range and search query.

  

  

• Alternatively, you can also access the audit logs from the Log Analytics menu. Open your Log Analytics workspace and under the General section, and select Logs. You can start with a simple query, such as: search "SQLSecurityAuditEvents" to view the audit logs. From here, you can also use Azure Monitor logs to run advanced searches on your audit log data. Azure Monitor logs gives you real-time operational insights using integrated search and custom dashboards to readily analyze millions of records across all your workloads and servers. For extra useful information about Azure Monitor logs search language and commands, see Azure Monitor logs search reference.

Analyze logs using Event Hubs

If you chose to write audit logs to Event Hubs:

• To consume audit logs data from Event Hubs, you need to set up a stream to consume events and write them to a target. For more information, see Azure Event Hubs Documentation.
• Audit logs in Event Hubs are captured in the body of Apache Avro events and stored using JSON formatting with UTF-8 encoding. To read the audit logs, you can use Avro Tools, Microsoft Fabric event streams, or similar tools that process this format.

Analyze logs using logs in an Azure storage account

If you chose to write audit logs to an Azure storage account, there are several methods you can use to view the logs:

• Audit logs are aggregated in the account you chose during setup. You can explore audit logs by using a tool such as Azure Storage Explorer. In Azure storage, auditing logs are saved as a collection of blob files within a container named sqldbauditlogs. For more information about the hierarchy of the storage folders, naming conventions, and log format, see the SQL Database Audit Log Format.

  1. Use the Azure portal.

  2. Open the relevant database resource.

  3. At the top of the database's Auditing page, select View audit logs.

     

     The Audit records page opens, and you're able to view the logs.

  4. You can view specific dates by selecting Filter at the top of the Audit records page.

  5. You can switch between audit records that were created by the server audit policy and the database audit policy by toggling Audit Source.

     

• Use the system function sys.fn_get_audit_file (T-SQL) to return the audit log data in tabular format. For more information on using this function, see sys.fn_get_audit_file.

• Use Merge Audit Files in SQL Server Management Studio (starting with SSMS 17):

  1. From the SSMS menu, select File > Open > Merge Audit Files.

     

  2. The Add Audit Files dialog box opens. Select one of the Add options to choose whether to merge audit files from a local disk or import them from Azure Storage. You're required to provide your Azure Storage details and account key.

  3. After all files to merge have been added, select OK to complete the merge operation.

  4. The merged file opens in SSMS, where you can view and analyze it, as well as export it to an XEL or CSV file, or to a table.

• Use Power BI. You can view and analyze audit log data in Power BI. For more information and to access a downloadable template, see Analyze audit log data in Power BI.

• Download log files from your Azure Storage blob container via the portal or by using a tool such as Azure Storage Explorer.

  • After you have downloaded a log file locally, double-click the file to open, view, and analyze the logs in SSMS.
  • You can also download multiple files simultaneously in Azure Storage Explorer. To do so, right-click a specific subfolder and select Save as to save in a local folder.

• More methods:

  • After downloading several files or a subfolder that contains log files, you can merge them locally as described in the SSMS Merge Audit Files instructions described previously.
  • View blob auditing logs programmatically: Query Extended Events Files by using PowerShell.

See also

• Auditing overview
• Data Exposed episode What's New in Azure SQL Auditing
• Auditing for SQL Managed Instance
• Auditing for SQL Server