SQL Database audit log format

  • Article

Applies to: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

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.

Naming conventions

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 is a running index in case session logs spans across multiple Blob files.

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

Read-only Replicas audit logs are stored in the same container. The directory hierarchy within the container is of the form <ServerName>/<DatabaseName>/<AuditName>/<Date>/RO/. The Blob file name shares the same format. The Audit Logs of Read-only Replicas are stored in the same container.

Event Hub

Audit events are written to the namespace and event hub that was defined during auditing configuration, and are captured in the body of Apache Avro events and stored using JSON formatting with UTF-8 encoding.

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. For additional useful information about Log Analytics search language and commands, see Log Analytics search reference.

Audit log fields

Name (blob) Name (Event Hubs/Log Analytics) Description Blob type Event Hubs/Log Analytics type
action_id action_id_s ID of the action varchar(4) string
action_name action_name_s Name of the action N/A string
additional_information additional_information_s Any additional information about the event, stored as XML nvarchar(4000) string
affected_rows affected_rows_d Number of rows affected by the query bigint int
application_name application_name_s Name of client application nvarchar(128) string
audit_schema_version audit_schema_version_d Always 1 int int
class_type class_type_s Type of auditable entity that the audit occurs on varchar(2) string
class_type_desc class_type_description_s Description of auditable entity that the audit occurs on N/A string
client_ip client_ip_s Source IP of the client application nvarchar(128) string
connection_id N/A ID of the connection in the server GUID N/A
data_sensitivity_information data_sensitivity_information_s Information types and sensitivity labels returned by the audited query, based on the classified columns in the database. Learn more about Azure SQL Database data discover and classification nvarchar(4000) string
database_name database_name_s The database context in which the action occurred sysname string
database_principal_id database_principal_id_d ID of the database user context that the action is performed in int int
database_principal_name database_principal_name_s Name of the database user context in which the action is performed sysname string
duration_milliseconds duration_milliseconds_d Query execution duration in milliseconds bigint int
event_time event_time_t Date and time when the auditable action is fired datetime2 datetime
host_name N/A Client host name string N/A
is_column_permission is_column_permission_s Flag indicating if this is a column level permission. 1 = true, 0 = false bit string
N/A is_server_level_audit_s Flag indicating if this audit is at the server level N/A string
object_ id object_id_d The 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 level int int
object_name object_name_s The 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 level sysname string
obo_middle_tier_app_id obo_middle_tier_app_id_s The application id of the middle tier application which connected to SQL Database using OBO access. varchar(120) string
permission_bitmask permission_bitmask_s When applicable, shows the permissions that were granted, denied, or revoked varbinary(16) string
response_rows response_rows_d Number of rows returned in the result set bigint int
schema_name schema_name_s The schema context in which the action occurred. NULL for audits occurring outside a schema sysname string
N/A securable_class_type_s Securable object that maps to the class_type being audited N/A string
sequence_group_id sequence_group_id_g Unique identifier varbinary GUID
sequence_number sequence_number_d Tracks 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 truncated int int
server_instance_name server_instance_name_s Name of the server instance where the audit occurred sysname string
server_principal_id server_principal_id_d ID of the login context in which the action is performed int int
server_principal_name server_principal_name_s Current login sysname string
server_principal_sid server_principal_sid_s Current login SID varbinary string
session_id session_id_d ID of the session on which the event occurred smallint int
session_server_principal_name session_server_principal_name_s Server principal for session sysname string
statement statement_s T-SQL statement that was executed (if any) nvarchar(4000) string
succeeded succeeded_s Indicates 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 = fail bit string
target_database_principal_id target_database_principal_id_d The database principal the GRANT/DENY/REVOKE operation is performed on. 0 if not applicable int int
target_database_principal_name target_database_principal_name_s Target user of action. NULL if not applicable string string
target_server_principal_id target_server_principal_id_d Server principal that the GRANT/DENY/REVOKE operation is performed on. Returns 0 if not applicable int int
target_server_principal_name target_server_principal_name_s Target login of action. NULL if not applicable sysname string
target_server_principal_sid target_server_principal_sid_s SID of target login. NULL if not applicable varbinary string
transaction_id transaction_id_d SQL Server only (starting with 2016) - 0 for Azure SQL Database bigint int
user_defined_event_id user_defined_event_id_d User defined event ID passed as an argument to sp_audit_write. NULL for system events (default) and non-zero for user-defined event. For more information, see sp_audit_write (Transact-SQL) smallint int
user_defined_information user_defined_information_s User defined information passed as an argument to sp_audit_write. NULL for system events (default) and non-zero for user-defined event. For more information, see sp_audit_write (Transact-SQL) nvarchar(4000) string

Next steps

Learn more about Azure SQL Database auditing.