Last updated on March 13th, 2018



Azure SQL Database - Blob Audit Logs formatLast updated on March 13th, 2018BackgroundThis paper specifies the format of the Blob Audit logs, which are generated by Azure SQL Database. Audit logs are collected into Azure Storage Blob files that are generated periodically in an Azure Storage account, which is owned by the customer. Blobs are stored in a container named “sqldbauditlogs” within the Azure Storage accountDirectories hierarchy within the container (‘/’ delimiter signifies directory hierarchy):<ServerName>/<DatabaseName>/<AuditName>/CreationDate/ Blob filename format:CreationTime_FileNumberInSession.xelCreationTime – time of day in UTC (hh_mm_ss_ms format)FileNumberInSession – a running index in case session log spans across multiple Blob filesExample of a full Blob file path: Server1/MayaDB/SqlDbAuditing_Audit/2017-08-26/ 10_45_22_173_1.xelEach Blob file is limited to 50MB, a new Blob file will be created once the previous one reached this limit.Additionally, a new Blob file is created daily, assuming there is audited traffic on that specific day.Customers may explore and download Blob log files using various methods and tools - additional information can be found in the Azure SQL DB Auditing Getting Started guide. Customers may also develop their own logs reporting solution or integrate with a solution of their choice.Log fields#NameDescriptionType1event_timeDate/time when the auditable action is fired.datetime22sequence_numberTracks the sequence of records within a single audit record that was too large to fit in the write buffer for audits.int3action_idID of the actionint4succeededIndicates if the action that triggered the event succeededbit 1 =Success0 = Fail5permission_bitmaskWhen applicable, shows the permissions that were granted, denied, or revokedbigint6is_column_permissionFlag indicating a column level permissionbit?1 = True0 = False7session_idID of the session on which the event occurred.int8server_principal_idID of the login context that the action is performed in.int9database_principal_idID of the database user context that the action is performed in.int10target_server_principal_idServer principal that the auditable action applies to.int11target_database_principal_idDatabase principal that the auditable action applies to.int12object_ idThe primary ID of the entity on which the audit occurred. This includes: server objects; databases; database objects; schema objectsint13class_typeType of auditable entity that the audit occurs on.int14session_server_principal_nameServer principal for the session.sysname15server_principal_nameCurrent login.sysname16server_principal_sidCurrent login SID.varbinary17database_principal_nameCurrent user.sysname18target_server_principal_nameTarget login of the action.sysname19target_server_principal_sidSID of the target login.varbinary20target_database_principal_nameTarget user of the action.sysname21server_instance_nameName of the server instance where the audit occurred. Uses the standard machine\instance format.nvarchar(120)22database_nameThe database context in which the action occurred.sysname23schema_nameThe schema context in which the action occurred.sysname24object_nameThe name of the entity on which the audit occurred. This includes: server objects; databases; database objects; schema objects; TSQL statement (if any)sysname25statementTSQL statement (if any)nvarchar(4000)26additional_informationAny additional information about the event, stored as XML.nvarchar(4000)27file_nameThe path and name of the audit log file that the record came from. Is not nullable.varchar(260)28audit_file_offsetThe buffer offset in the file that contains the audit record. Is not nullable.bigin29user_defined_event_idApplies to: SQL Server 2012 through SQL Server 2017.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).smallint30user_defined_informationApplies to: SQL Server 2012 through SQL Server 2017.Used to record any extra information the user wants to record innvarchar(4000)31audit_schema_versionint32sequence_group_idApplies to: SQL Server only (starting with 2016)varbinary33transaction_idApplies to: SQL Server only (starting with 2016)34client_ipApplies to: Azure SQL DB + SQL Server (starting with 2017)The source IP of the client applicationnvarchar(128)35application_nameApplies to: Azure SQL DB + SQL Server (starting with 2017)Name of the client application.nvarchar(128)36duration_millisecondsApplies to: Azure SQL DB onlyQuery execution duration.bigint37response_rowsApplies to: Azure SQL DB onlyNumber of rows returned in the result set.bigint38affected_rowsApplies to: Azure SQL DB onlyNumber of rows affected by the query.bigint39connection_idApplies to: Azure SQL DB onlyGUID40data_sensitivity_informationApplies to: Azure SQL DB onlyThe sensitivity labels of columns returned in the query result set.nvarchar(4000) ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download