Edit

Azure Synapse Analytics audit log format

Tip

Microsoft Fabric Data Warehouse is an enterprise scale relational warehouse on a data lake foundation, with a future-ready architecture, built-in AI, and new features. If you're new to data warehousing, start with Fabric Data Warehouse. Existing dedicated SQL pool workloads can upgrade to Fabric to access new capabilities across data science, real-time analytics, and reporting.

Azure Synapse Analytics auditing tracks database events and writes them to Azure Storage, or sends them to Azure Event Hubs or Azure Monitor 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. The directory hierarchy within the container is <ServerName>/<DatabaseName>/<AuditName>/<Date>/. The blob file name format is <CreationTime>_<FileNumberInSession>.xel, where CreationTime uses the UTC hh_mm_ss_ms format and FileNumberInSession is a running index when a session spans multiple blob files.

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

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

Event Hubs

Audit events are written to the namespace and event hub configured for auditing. Events are captured in the body of Apache Avro events and use JSON formatting with UTF-8 encoding.

Log Analytics

Audit events are written to the AzureDiagnostics table in the configured Log Analytics workspace. Database audit events use the SQLSecurityAuditEvents category, and Microsoft support operations use the DevOpsOperationsAudit category. For query guidance, see Log Analytics queries.

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. For more information, see Azure Synapse Analytics security: Threat protection 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 Azure Synapse Analytics 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. Azure Synapse Analytics auditing 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 Not populated for Azure Synapse Analytics 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

Related content

  • Last updated on