If you choose to write audit logs to an Azure storage account, you can view them in a few ways:

1. Use a tool like Azure Storage Explorer to access the logs stored in the container named “sqldbauditlogs” within your chosen account.
2. Through the Azure portal:
   • Open the relevant database resource.
   • Go to the Auditing page and select “View audit logs” at the top.

For more details on storage folder structure, naming, and log format, refer to the SQL Database Audit Log Format.

3. Audit records page: Open the page to view logs. Use the Filter option to select specific dates. Switch between server and database audit records using the Audit Source toggle.

4. T-SQL Function: Use sys.fn_get_audit_file to view logs in a table format.

5. SQL Server Management Studio (SSMS):

• Select “File > Open > Merge Audit Files” to combine logs from local storage or Azure Storage. Provide your Azure details.
• View, analyze, and export logs in SSMS.

6. Power BI: Analyze logs using Power BI with a downloadable template.

7. Azure Storage Explorer: Download logs from your Azure Storage container to view them locally.

8. Programmatically: Use PowerShell to query extended event files.

How to use sys.fn_get_audit_file

Syntax

fn_get_audit_file ( file_pattern ,
    { default | initial_file_name | NULL } ,
    { default | audit_record_offset | NULL } )

Tables returned

The following table describes the audit file content that can be returned by this function.

• event_time (datetime2): Date and time when the action occurred.
• sequence_number (int): Tracks the record sequence if it’s too large.
• action_id (varchar): Action ID.
• succeeded (bit): Shows if the action succeeded (1 = success, 0 = fail).
• permission_bitmask (varbinary): Permissions granted, denied, or revoked.
• is_column_permission (bit): If it’s a column-level permission (1 = true, 0 = false).
• session_id (smallint): Session ID of the event.
• server_principal_id (int): Server login context ID.
• database_principal_id (int): Database user context ID (0 if not applicable).
• target_server_principal_id (int): Target server principal ID for actions like GRANT/DENY/REVOKE.
• target_database_principal_id (int): Target database principal ID (0 if not applicable).
• object_id (int): ID of the audited entity (0 if the audit is not object-level).
• class_type (varchar): Type of the audited entity.
• session_server_principal_name (sysname): Original server principal (Nullable).
• server_principal_name (sysname): Current login (Nullable).
• server_principal_sid (varbinary): Current login SID (Nullable).
• database_principal_name (sysname): Current user (Nullable).
• target_server_principal_name (sysname): Target login (Nullable).
• target_server_principal_sid (varbinary): SID of target login (Nullable).
• target_database_principal_name (sysname): Target user (Nullable).
• server_instance_name (sysname): Server instance name.
• database_name (sysname): Database context (Nullable).
• schema_name (sysname): Schema context (Nullable).
• object_name (sysname): Audited entity’s name (Nullable).
• statement (nvarchar): Transact-SQL statement (Nullable).
• additional_information (nvarchar): Event-specific XML data (Nullable).
• file_name (varchar): Audit log file path.
• audit_file_offset (bigint): Buffer offset in the log file.
• user_defined_event_id (smallint): User-defined event ID.
• user_defined_information (nvarchar): Additional user-defined info.
• audit_schema_version (int): Always 1.
• sequence_group_id (varbinary): Unique identifier.
• transaction_id (bigint): Audit events transaction ID.
• client_ip (nvarchar): Client source IP.
• application_name (nvarchar): Client application name.
• duration_milliseconds (bigint): Query execution duration.
• response_rows (bigint): Rows returned in the result set.
• affected_rows (bigint): Rows affected by the statement.
• connection_id (uniqueidentifier): Server connection ID.
• data_sensitivity_information (nvarchar): Data sensitivity labels.
• host_name (nvarchar): Client machine host name.
• session_context (nvarchar): Key-value pairs from session context.
• client_tls_version (bigint): Minimum client TLS version.
• client_tls_version_name (nvarchar): Name of client TLS version.
• database_transaction_id (bigint): Current session transaction ID.
• ledger_start_sequence_number (bigint): Sequence number for a row version.
• external_policy_permissions_checked (nvarchar): External permission check info.
• obo_middle_tier_app_id (varchar): Middle-tier application ID using OBO access.
• is_local_secondary_replica (bit): True if it’s from a read-only local secondary replica.

Examples

This example reads from a file that is named ShiraServer/MayaDB/SqlDbAuditing_Audit/2017-07-14/10_45_22_173_1.xel:

SELECT *
FROM sys.fn_get_audit_file(
    'https://mystorage.blob.core.windows.net/sqldbauditlogs/ShiraServer/MayaDB/SqlDbAuditing_Audit/2017-07-14/10_45_22_173_1.xel',
    DEFAULT,
    DEFAULT
);
GO

This example reads from the same file as previously shown, but with more T-SQL clauses (TOP, ORDER BY, and WHERE clause for filtering the audit records returned by the function):

SELECT TOP 10 *
FROM sys.fn_get_audit_file(
    'https://mystorage.blob.core.windows.net/sqldbauditlogs/ShiraServer/MayaDB/SqlDbAuditing_Audit/2017-07-14/10_45_22_173_1.xel',
    DEFAULT,
    DEFAULT
)
WHERE server_principal_name = 'admin1'
ORDER BY event_time;
GO

How to use sys.fn_get_audit_file_v2

Syntax

fn_get_audit_file_v2 ( file_pattern
    , { default | initial_file_name | NULL }
    , { default | audit_record_offset | NULL }
    , { default | start time | NULL }
    , { default | end time | NULL } )

Table returned

The following table describes the audit file content returned by this function.

• event_time (datetime2): When the action occurred.
• sequence_number (int): Order of records in a large audit.
• action_id (varchar(4)): Action ID.
• succeeded (bit): If the action succeeded (1) or failed (0).
• permission_bitmask (varbinary(16)): Permissions granted, denied, or revoked.
• is_column_permission (bit): If it’s column-level (1 = true, 0 = false).
• session_id (smallint): Session ID.
• server_principal_id (int): ID of the login context.
• database_principal_id (int): Database user ID, 0 if not applicable.
• target_server_principal_id (int): Server principal for GRANT/DENY/REVOKE.
• target_database_principal_id (int): Database principal for GRANT/DENY/REVOKE.
• object_id (int): ID of the audited object.
• class_type (varchar(2)): Type of auditable entity.
• session_server_principal_name (sysname): Original login for the session.
• server_principal_name (sysname): Current login.
• server_principal_sid (varbinary): Current login SID.
• database_principal_name (sysname): Current database user.
• target_server_principal_name (sysname): Target login of the action.
• target_server_principal_sid (varbinary): SID of target login.
• target_database_principal_name (sysname): Target database user.
• server_instance_name (sysname): Name of the server instance.
• database_name (sysname): Database where the action happened.
• schema_name (sysname): Schema context.
• object_name (sysname): Name of the audited object.
• statement (nvarchar(4000)): The SQL statement executed.
• additional_information (nvarchar(4000)): Unique XML event details.
• file_name (varchar(260)): Audit log file path.
• audit_file_offset (bigint): File offset for the record.
• user_defined_event_id (smallint): User-defined event ID.
• user_defined_information (nvarchar(4000)): Extra user-defined info.
• audit_schema_version (int): Always 1.
• sequence_group_id (varbinary): Unique identifier.
• transaction_id (bigint): Transaction ID.
• client_ip (nvarchar(128)): Client application’s IP.
• application_name (nvarchar(128)): Name of the client application.
• duration_milliseconds (bigint): Query execution time.
• response_rows (bigint): Rows returned.
• affected_rows (bigint): Rows affected.
• connection_id (uniqueidentifier): Server connection ID.
• data_sensitivity_information (nvarchar(4000)): Sensitivity info.
• host_name (nvarchar(128)): Client machine’s host name.
• session_context (nvarchar(4000)): Session context key-value pairs.
• client_tls_version (bigint): Client’s minimum TLS version.
• client_tls_version_name (nvarchar(128)): TLS version name.
• database_transaction_id (bigint): Current session’s transaction ID.
• ledger_start_sequence_number (bigint): Row version start number.
• external_policy_permissions_checked (nvarchar(4000)): Info on external permission checks.
• obo_middle_tier_app_id (varchar(120)): Middle tier app ID for OBO access.
• is_local_secondary_replica (bit): True if read-only local secondary replica (Azure SQL Database only).

Examples

This example retrieves audit logs from a specific Azure Blob Storage location, filtering records between 2023-11-17T08:40:40Z and 2023-11-17T09:10:40Z.

SELECT *
FROM sys. fn_get_audit_file_v2(
    'https://yourstorageaccount.blob.core.windows.net/sqldbauditlogs/server_name/database_name/SqlDbAuditing_ServerAudit/',
    DEFAULT,
    DEFAULT,
    '2023-11-17T08:40:40Z',
    '2023-11-17T09:10:40Z')