Edit

sys.fn_get_audit_file (Transact-SQL)

  • Article

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

Returns information from an audit file created by a server audit in SQL Server. For more information, see SQL Server Audit (Database Engine).

Transact-SQL syntax conventions

Syntax

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

Arguments

file_pattern

Specifies the directory or path and file name for the audit file set to be read. Type is nvarchar(260).

Passing a path without a file name pattern generates an error.

This argument must include both a path (drive letter or network share) and a file name that can include a wildcard. A single asterisk (*) can be used to collect multiple files from an audit file set. For example:

  • \<path>\* - Collect all audit files in the specified location.

  • <path>\LoginsAudit_{GUID}* - Collect all audit files that have the specified name and GUID pair.

  • <path>\LoginsAudit_{GUID}_00_29384.sqlaudit - Collect a specific audit file.

initial_file_name

Specifies the path and name of a specific file in the audit file set to start reading audit records from. Type is nvarchar(260).

The initial_file_name argument must contain valid entries or must contain either the default or NULL value.

audit_record_offset

Specifies a known location with the file specified for the initial_file_name. When this argument is used, the function starts reading at the first record of the buffer immediately following the specified offset.

The audit_record_offset argument must contain valid entries or must contain either the default or NULL value. Type is bigint.

Tables returned

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

Column name Type Description
action_id varchar(4) ID of the action. Not nullable.
additional_information nvarchar(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 is displayed in XML format for actions that have T-SQL stack associated with them. The XML format is: <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 is parsed to escape invalid XML characters like <, >, /, _x. They're escaped as _xHHHH\_. The HHHH stands for the four-digit hexadecimal UCS-2 code for the character. Nullable. Returns NULL when there's no additional information reported by the event.
affected_rows bigint Number of rows affected by the executed statement.

Applies to: Azure SQL Database only
application_name nvarchar(128) Name of client application that executed the statement that caused the audit event.

Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL Database
audit_file_offset bigint The buffer offset in the file that contains the audit record. Not nullable.

Applies to: SQL Server only
audit_schema_version int Always 1.
class_type varchar(2) The type of auditable entity that the audit occurs on. Not nullable.
client_ip nvarchar(128) Source IP of the client application.

Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL Database
connection_id uniqueidentifier ID of the connection in the server.

Applies to: Azure SQL Database and SQL Managed Instance
data_sensitivity_information nvarchar(4000) 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.

Applies to: Azure SQL Database only
database_name sysname The database context in which the action occurred. Nullable. Returns NULL for audits occurring at the server level.
database_principal_id int ID 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_name sysname Current user. Nullable. Returns NULL if not available.
duration_milliseconds bigint Query execution duration in milliseconds.

Applies to: Azure SQL Database and SQL Managed Instance
event_time datetime2 Date and time when the auditable action is fired. Not nullable.
file_name varchar(260) The path and name of the audit log file that the record came from. Not nullable.
is_column_permission bit Flag indicating if this is a column level permission. Not nullable. Returns 0 when the permission_bitmask = 0.

1 = true
0 = false
object_id int The ID of the entity on which the audit occurred, which includes the following objects:

- 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_name sysname The name of the entity on which the audit occurred, which includes the following objects:

- Server objects
- Databases
- Database objects
- Schema objects

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_id varchar(120) The application ID of the middle tier application that connects to Azure SQL Database using OBO access. Nullable. Returns NULL if the request isn't made using OBO access.

Applies to: Azure SQL Database
permission_bitmask varbinary(16) In some actions, this bitmask is the permissions that were grant, denied, or revoked.
response_rows bigint Number of rows returned in the result set.

Applies to: Azure SQL Database and SQL Managed Instance
schema_name sysname The schema context in which the action occurred. Nullable. Returns NULL for audits occurring outside a schema.
sequence_group_id varbinary Unique identifier.

Applies to: SQL Server 2016 (13.x) and later versions
sequence_number int Tracks 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_name sysname Name of the server instance where the audit occurred. The standard server\instance format is used.
server_principal_id int ID of the login context that the action is performed in. Not nullable.
server_principal_name sysname Current login. Nullable.
server_principal_sid varbinary Current login SID. Nullable.
session_id smallint ID of the session on which the event occurred. Not nullable.
session_server_principal_name sysname Server principal for session. 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.
statement nvarchar(4000) Transact-SQL statement if it exists. Nullable. Returns NULL if not applicable.
succeeded bit Indicates 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_id int The database principal the GRANT/DENY/REVOKE operation is performed on. Not nullable. Returns 0 if not applicable.
target_database_principal_name sysname Target user of action. Nullable. Returns NULL if not applicable.
target_server_principal_id int Server principal that the GRANT/DENY/REVOKE operation is performed on. Not nullable. Returns 0 if not applicable.
target_server_principal_name sysname Target login of action. Nullable. Returns NULL if not applicable.
target_server_principal_sid varbinary SID of target login. Nullable. Returns NULL if not applicable.
transaction_id bigint Unique identifier to identify multiple audit events in one transaction.

Applies to: SQL Server 2016 (13.x) and later versions
user_defined_event_id smallint 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).

Applies to: SQL Server 2012 (11.x) and later, Azure SQL Database, and SQL Managed Instance
user_defined_information nvarchar(4000) Used to record any extra information the user wants to record in audit log by using the sp_audit_write stored procedure.

Applies to: SQL Server 2012 (11.x) and later versions, Azure SQL Database, and SQL Managed Instance

Remarks

  • If the file_pattern argument passed to fn_get_audit_file references a path or file that doesn't exist, or if the file isn't an audit file, the MSG_INVALID_AUDIT_FILE error message is returned.

  • fn_get_audit_file can't be used when the audit is created with the APPLICATION_LOG, SECURITY_LOG, or EXTERNAL_MONITOR options.

Permissions

Requires the CONTROL SERVER permission.

Examples

This example reads from a file that is named \\serverName\Audit\HIPAA_AUDIT.sqlaudit.

SELECT *
FROM sys.fn_get_audit_file(
    '\\serverName\Audit\HIPAA_AUDIT.sqlaudit',
    DEFAULT,
    DEFAULT
);
GO

For a full example about how to create an audit, see SQL Server Audit (Database Engine).

Limitations

Selecting rows from sys.fn_get_audit_file within a Create Table As Select (CTAS) or INSERT INTO is a limitation when running on Azure Synapse Analytics. Although the query completes successfully and no error messages appear, there are no rows present in the table created using CTAS or INSERT INTO.

Related content