Azure SQL Database auditing tracks database events and writes them to an audit log in your Azure storage account, or sends them to Event Hub or Log Analytics for downstream processing and analysis.

Blob audit

Audit logs stored in Azure Blob storage are stored in a container named sqldbauditlogs in the Azure storage account. 

The directory hierarchy within the container is of the form

<ServerName>/<DatabaseName>/<AuditName>/<Date>/

The Blob file name format is

<CreationTime>_<FileNumberInSession>.xel

where CreationTime is in UTC hh_mm_ss_ms format, and FileNumberInSession

For example, for database Database1 on Server1 the following is a possible valid path:

Server1/Database1/SqlDbAuditing_ServerAudit_NoRetention/2019-02-03/12_23_30_794_0.xel

Azure SQL Database

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

his example reads from the same file as above, but with additional 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

Tables returned

Column nameTypeDescription
action_idvarchar(4)ID of the action. Not nullable.
additional_informationnvarchar(4000)Unique information that only applies to a single event is returned as XML. A few auditable actions contain this kind of information.

One level of T-SQL stack will be displayed in XML format for actions that have T-SQL stack associated with them. The XML format will be:

<tsql_stack><frame nest_level = '%u' database_name = '%.*s' schema_name = '%.*s' object_name = '%.*s' /></tsql_stack>

Frame nest_level indicates the current nesting level of the frame. The Module name is represented in three part format (database_name, schema_name and object_name). The module name will be parsed to escape invalid xml characters like '\<''>''/''_x'. They will be escaped as _xHHHH\_. The HHHH stands for the four-digit hexadecimal UCS-2 code for the character

Is nullable. Returns NULL when there is no additional information reported by the event.
affected_rowsbigintApplies to: Azure SQL Database only

Number of rows affected by the executed statement.
application_namenvarchar(128)Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL Database

Name of client application that executed the statement that caused the audit event
audit_file_offsetbigintApplies to: SQL Server only

The buffer offset in the file that contains the audit record. Not nullable.
audit_schema_versionintAlways 1
class_typevarchar(2)The type of auditable entity that the audit occurs on. Not nullable.
client_ipnvarchar(128)Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL Database

Source IP of the client application
connection_idGUIDApplies to: Azure SQL Database and SQL Managed Instance

ID of the connection in the server
data_sensitivity_informationnvarchar(4000)Applies to: Azure SQL Database only

Information types and sensitivity labels returned by the audited query, based on the classified columns in the database.
database_namesysnameThe database context in which the action occurred. Is nullable. Returns NULL for audits occurring at the server level.
database_principal_idintID of the database user context that the action is performed in. Not nullable. Returns 0 if this doesn’t apply. For example, a server operation.
database_principal_namesysnameCurrent user. Is nullable. Returns NULL if not available.
duration_millisecondsbigintApplies to: Azure SQL Database and SQL Managed Instance

Query execution duration in milliseconds
event_timedatetime2Date and time when the auditable action is fired. Not nullable.
file_namevarchar(260)The path and name of the audit log file that the record came from. Not nullable.
is_column_permissionbitFlag indicating if this is a column level permission. Not nullable. Returns 0 when the permission_bitmask = 0.
1 = true
0 = false
object_idintThe ID of the entity on which the audit occurred. This includes the following:
Server objects
Databases
Database objects
Schema objects
Not nullable. Returns 0 if the entity is the Server itself or if the audit isn’t performed at an object level. For example, Authentication.
object_namesysnameThe name of the entity on which the audit occurred. This includes the following:
Server objects
Databases
Database objects
Schema objects
Is nullable. Returns NULL if the entity is the Server itself or if the audit isn’t performed at an object level. For example, Authentication.
obo_middle_tier_app_idvarchar(120)Applies to: Azure SQL Database and SQL Managed Instance

The application ID of the middle tier application which connects to SQL Database using OBO access.
Is nullable. Returns NULL if the request is not made using OBO access.
permission_bitmaskvarbinary(16)In some actions, this is the permissions that were grant, denied, or revoked.
response_rowsbigintApplies to: Azure SQL Database and SQL Managed Instance

Number of rows returned in the result set.
schema_namesysnameThe schema context in which the action occurred. Is nullable. Returns NULL for audits occurring outside a schema.
sequence_group_idvarbinaryApplies to: SQL Server 2016 (13.x) and later versions

Unique identifier
sequence_numberintTracks the sequence of records within a single audit record that was too large to fit in the write buffer for audits. Not nullable.
server_instance_namesysnameName of the server instance where the audit occurred. The standard server\instance format is used.
server_principal_idintID of the login context that the action is performed in. Not nullable.
server_principal_namesysnameCurrent login. Is nullable.
server_principal_sidvarbinaryCurrent login SID. Is nullable.
session_idsmallintID of the session on which the event occurred. Not nullable.
session_server_principal_namesysnameServer principal for session. Is nullable. Returns the identity of the original login that was connected to the instance of SQL Server in case there were explicit or implicit context switches.
statementnvarchar(4000)Transact-SQL statement if it exists. Is nullable. Returns NULL if not applicable.
succeededbitIndicates whether the action that triggered the event succeeded. Not nullable. For all events other than login events, this only reports whether the permission check succeeded or failed, not the operation.
1 = success
0 = fail
target_database_principal_idintThe database principal the GRANT/DENY/REVOKE operation is performed on. Not nullable. Returns 0 if not applicable.
target_database_principal_namesysnameTarget user of action. Is nullable. Returns NULL if not applicable.
target_server_principal_idintServer principal that the GRANT/DENY/REVOKE operation is performed on. Not nullable. Returns 0 if not applicable.
target_server_principal_namesysnameTarget login of action. Is nullable. Returns NULL if not applicable.
target_server_principal_sidvarbinarySID of target login. Is nullable. Returns NULL if not applicable.
transaction_idbigintApplies to: SQL Server 2016 (13.x) and later versions

Unique identifier to identify multiple audit events in one transaction
user_defined_event_idsmallintApplies to: SQL Server 2012 (11.x) and later, Azure SQL Database and SQL Managed Instance

User defined event ID passed as an argument to sp_audit_writeNULL for system events (default) and non-zero for user-defined event.
user_defined_informationnvarchar(4000)Applies to: SQL Server 2012 (11.x) and later versions, Azure SQL Database, and SQL Managed Instance

Used to record any extra information the user wants to record in audit log by using the sp_audit_write stored procedure.

Log Analytics

Audit events are written to Log Analytics workspace defined during auditing configuration, to the AzureDiagnostics table with the category SQLSecurityAuditEvents,and table with the category DevOpsOperationsAudit for Microsoft Support Operations.

Audit log fields

Name (blob)Name (Event Hubs/Log Analytics)DescriptionBlob typeEvent Hubs/Log Analytics type
action_idaction_id_sID of the actionvarchar(4)string
action_nameaction_name_sName of the actionN/Astring
additional_informationadditional_information_sAny additional information about the event, stored as XMLnvarchar(4000)string
affected_rowsaffected_rows_dNumber of rows affected by the querybigintint
application_nameapplication_name_sName of client applicationnvarchar(128)string
audit_schema_versionaudit_schema_version_dAlways 1intint
class_typeclass_type_sType of auditable entity that the audit occurs onvarchar(2)string
class_type_descclass_type_description_sDescription of auditable entity that the audit occurs onN/Astring
client_ipclient_ip_sSource IP of the client applicationnvarchar(128)string
connection_idN/AID of the connection in the serverGUIDN/A
data_sensitivity_informationdata_sensitivity_information_sInformation types and sensitivity labels returned by the audited query, based on the classified columns in the database. nvarchar(4000)string
database_namedatabase_name_sThe database context in which the action occurredsysnamestring
database_principal_iddatabase_principal_id_dID of the database user context that the action is performed inintint
database_principal_namedatabase_principal_name_sName of the database user context in which the action is performedsysnamestring
duration_millisecondsduration_milliseconds_dQuery execution duration in millisecondsbigintint
event_timeevent_time_tDate and time when the auditable action is fireddatetime2datetime
host_nameN/AClient host namestringN/A
is_column_permissionis_column_permission_sFlag indicating if this is a column level permission. 1 = true, 0 = falsebitstring
N/Ais_server_level_audit_sFlag indicating if this audit is at the server levelN/Astring
object_ idobject_id_dThe ID of the entity on which the audit occurred. This includes the : server objects, databases, database objects, and schema objects. 0 if the entity is the server itself or if the audit is not performed at an object levelintint
object_nameobject_name_sThe name of the entity on which the audit occurred. This includes the : server objects, databases, database objects, and schema objects. 0 if the entity is the server itself or if the audit is not performed at an object levelsysnamestring
obo_middle_tier_app_idobo_middle_tier_app_id_sThe application id of the middle tier application which connected to SQL Database using OBO access.varchar(120)string
permission_bitmaskpermission_bitmask_sWhen applicable, shows the permissions that were granted, denied, or revokedvarbinary(16)string
response_rowsresponse_rows_dNumber of rows returned in the result setbigintint
schema_nameschema_name_sThe schema context in which the action occurred. NULL for audits occurring outside a schemasysnamestring
N/Asecurable_class_type_sSecurable object that maps to the class_type being auditedN/Astring
sequence_group_idsequence_group_id_gUnique identifiervarbinaryGUID
sequence_numbersequence_number_dTracks the sequence of records within a single audit record that was too large to fit in the write buffer for audits. Note that Azure SQL Database and Azure Synapse Audit stores 4000 characters of data for character fields in an audit record. When there are more than 4000 characters, any data beyond the first 4000 characters will be truncatedintint
server_instance_nameserver_instance_name_sName of the server instance where the audit occurredsysnamestring
server_principal_idserver_principal_id_dID of the login context in which the action is performedintint
server_principal_nameserver_principal_name_sCurrent loginsysnamestring
server_principal_sidserver_principal_sid_sCurrent login SIDvarbinarystring
session_idsession_id_dID of the session on which the event occurredsmallintint
session_server_principal_namesession_server_principal_name_sServer principal for sessionsysnamestring
statementstatement_sT-SQL statement that was executed (if any)nvarchar(4000)string
succeededsucceeded_sIndicates whether the action that triggered the event succeeded. For events other than login and batch, this only reports whether the permission check succeeded or failed, not the operation. 1 = success, 0 = failbitstring
target_database_principal_idtarget_database_principal_id_dThe database principal the GRANT/DENY/REVOKE operation is performed on. 0 if not applicableintint
target_database_principal_nametarget_database_principal_name_sTarget user of action. NULL if not applicablestringstring
target_server_principal_idtarget_server_principal_id_dServer principal that the GRANT/DENY/REVOKE operation is performed on. Returns 0 if not applicableintint
target_server_principal_nametarget_server_principal_name_sTarget login of action. NULL if not applicablesysnamestring
target_server_principal_sidtarget_server_principal_sid_sSID of target login. NULL if not applicablevarbinarystring
transaction_idtransaction_id_dSQL Server only (starting with 2016) – 0 for Azure SQL Databasebigintint
user_defined_event_iduser_defined_event_id_dUser defined event ID passed as an argument to sp_audit_write. NULL for system events (default) and non-zero for user-defined event. smallintint
user_defined_informationuser_defined_information_sUser defined information passed as an argument to sp_audit_write. NULL for system events (default) and non-zero for user-defined event. nvarchar(4000)string