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).
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 = true0 = 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 = success0 = 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, 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
.
Related content
- 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)
- sys.server_audit_specification_details (Transact-SQL)
- sys.database_audit_specifications (Transact-SQL)
- sys.database_audit_specification_details (Transact-SQL)
- sys.dm_server_audit_status (Transact-SQL)
- sys.dm_audit_actions (Transact-SQL)
- sys.dm_audit_class_type_map (Transact-SQL)
- Create a Server Audit and Server Audit Specification
- sys.server_audits (Transact-SQL)
- sys.server_file_audits (Transact-SQL)
- sys.server_audit_specifications (Transact-SQL)
Feedback
https://aka.ms/ContentUserFeedback.
Binnenkort beschikbaar: In de loop van 2024 zullen we GitHub-problemen geleidelijk uitfaseren als het feedbackmechanisme voor inhoud en deze vervangen door een nieuw feedbacksysteem. Zie voor meer informatie:Feedback verzenden en weergeven voor