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

How to 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.

or going to –> Auditing –>View Dashboard –>logs

or going to logs

How to 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:

  •  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.
  • Or search for storage account –> Container –> sqldbauditlogs
  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. Audit records page opens, and you’re able to view the logs.

4. or Use the system function sys.fn_get_audit_file (T-SQL) to return the audit log data in tabular format.

SELECT *
FROM sys.fn_get_audit_file( 'https://xxxuwxxx.blob.core.windows.net/sqldbauditlogs/xxxxx/xxxxxx-d/SqlDbAuditing_ServerAudit_NoRetention/2023-12-21/19_57_28_512_1.xel', DEFAULT, DEFAULT);

How to 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 or similar tools that process this format.