sys.fn_get_audit_file (Transact-SQL)
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).
Note
For Azure SQL Database, consider using sys.fn_get_audit_file_v2 instead. sys.fn_get_audit_file_v2
introduces time-based filtering at both the file and record levels, providing significant performance improvements, particularly for queries targeting specific time ranges.
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 |
---|---|---|
event_time |
datetime2 | Date and time when the auditable action is fired. Not nullable. |
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. |
action_id |
varchar(4) | ID of the action. Not nullable. |
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 = success0 = fail |
permission_bitmask |
varbinary(16) | In some actions, this bitmask is the permissions that were grant, denied, or revoked. |
is_column_permission |
bit | Flag indicating if this is a column level permission. Not nullable. Returns 0 when the permission_bitmask = 0 .1 = true0 = false |
session_id |
smallint | ID of the session on which the event occurred. Not nullable. |
server_principal_id |
int | ID of the login context that the action is performed in. Not nullable. |
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. |
target_server_principal_id |
int | Server principal that the GRANT /DENY /REVOKE operation is performed on. Not nullable. Returns 0 if not applicable. |
target_database_principal_id |
int | The database principal the GRANT /DENY /REVOKE operation is performed on. Not nullable. Returns 0 if not applicable. |
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. |
class_type |
varchar(2) | The type of auditable entity that the audit occurs on. 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 the Database Engine in case there were explicit or implicit context switches. |
server_principal_name |
sysname | Current login. Nullable. |
server_principal_sid |
varbinary | Current login SID. Nullable. |
database_principal_name |
sysname | Current user. Nullable. Returns NULL if not available. |
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. |
target_database_principal_name |
sysname | Target user of action. Nullable. Returns NULL if not applicable. |
server_instance_name |
sysname | Name of the server instance where the audit occurred. The standard server\instance format is used. |
database_name |
sysname | The database context in which the action occurred. Nullable. Returns NULL for audits occurring at the server level. |
schema_name |
sysname | The schema context in which the action occurred. Nullable. Returns NULL for audits occurring outside a schema. |
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. |
statement |
nvarchar(4000) | Transact-SQL statement if it exists. Nullable. Returns NULL if not applicable. |
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. |
file_name |
varchar(260) | The path and name of the audit log file that the record came from. Not nullable. |
audit_file_offset |
bigint | The buffer offset in the file that contains the audit record. Not nullable. Applies to: SQL Server only |
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 |
audit_schema_version |
int | Always 1 . |
sequence_group_id |
varbinary | Unique identifier. Applies to: SQL Server 2016 (13.x) and later versions |
transaction_id |
bigint | Unique identifier to identify multiple audit events in one transaction. Applies to: SQL Server 2016 (13.x) and later versions |
client_ip |
nvarchar(128) | Source IP of the client application. Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL Database |
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 |
duration_milliseconds |
bigint | Query execution duration in milliseconds. Applies to: Azure SQL Database and SQL Managed Instance |
response_rows |
bigint | Number of rows returned in the result set. Applies to: Azure SQL Database and SQL Managed Instance |
affected_rows |
bigint | Number of rows affected by the executed statement. Applies to: Azure SQL Database only |
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 |
host_name |
nvarchar(128) | Host Name of the client machine. |
session_context |
nvarchar(4000) | The key-value pairs that are a part of the current session context. |
client_tls_version |
bigint | Minimum TLS version supported by the client. |
client_tls_version_name |
nvarchar(128) | Minimum TLS version supported by the client. |
database_transaction_id |
bigint | Transaction ID of the current transaction in the current session. |
ledger_start_sequence_number |
bigint | The sequence number of an operation within a transaction that created a row version. Applies to: Azure SQL Database only |
external_policy_permissions_checked |
nvarchar(4000) | Information related to the external authorization permission check, when an audit event is generated, and Purview external authorization policies are evaluated. Applies to: Azure SQL Database only |
obo_middle_tier_app_id |
varchar(120) | The application ID of the middle tier application that connects to Azure SQL Database using on-behalf-of (OBO) access. Nullable. Returns NULL if the request isn't made using OBO access.Applies to: Azure SQL Database only |
is_local_secondary_replica |
bit | True if the audit record originates from a read-only local secondary replica, False otherwise.Applies to: Azure SQL Database only |
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, theMSG_INVALID_AUDIT_FILE
error message is returned.fn_get_audit_file
can't be used when the audit is created with theAPPLICATION_LOG
,SECURITY_LOG
, orEXTERNAL_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
.
More information
System catalog views:
- sys.server_audit_specifications (Transact-SQL)
- sys.server_audit_specification_details (Transact-SQL)
- sys.database_audit_specifications (Transact-SQL)
- sys.database_audit_specification_details (Transact-SQL)
Transact-SQL:
- CREATE SERVER AUDIT (Transact-SQL)
- ALTER SERVER AUDIT (Transact-SQL)
- DROP SERVER AUDIT (Transact-SQL)
- CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)
- ALTER SERVER AUDIT SPECIFICATION (Transact-SQL)
- DROP SERVER AUDIT SPECIFICATION (Transact-SQL)
- CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)
- ALTER DATABASE AUDIT SPECIFICATION (Transact-SQL)
- DROP DATABASE AUDIT SPECIFICATION (Transact-SQL)
- ALTER AUTHORIZATION (Transact-SQL)