sys.fn_get_audit_file (Transact-SQL)sys.fn_get_audit_file (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

从服务器审核在 SQL ServerSQL Server 中创建的审核文件返回信息。Returns information from an audit file created by a server audit in SQL ServerSQL Server. 有关详细信息,请参阅 SQL Server Audit(数据库引擎)For more information, see SQL Server Audit (Database Engine).

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

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

参数Arguments

file_patternfile_pattern
指定要读取的审核文件集的目录(或路径)和文件名。Specifies the directory or path and file name for the audit file set to be read. 类型为 **nvarchar (260) **。Type is nvarchar(260).

  • SQL ServerSQL Server:

    此参数必须包括路径(驱动器盘符或网络共享)和文件名,可以包含通配符。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>\* -收集指定位置中的所有审核文件。<path>\* - Collect all audit files in the specified location.

    • ** <path> \ LOGINSAUDIT_ {GUID}***-收集具有指定名称和 GUID 对的所有审核文件。<path>\LoginsAudit_{GUID}* - Collect all audit files that have the specified name and GUID pair.

    • ** <path> \ LOGINSAUDIT_ {GUID} 00_29384. .Sqlaudit** -收集特定的审核文件。**<path>\LoginsAudit{GUID}_00_29384.sqlaudit** - Collect a specific audit file.

  • AZURE SQL 数据库Azure SQL Database:

    此参数用于指定 (包括存储终结点和容器) 的 blob URL。This argument is used to specify a blob URL (including the storage endpoint and container). 虽然它不支持星号通配符,但你可以使用部分文件 (blob) 名称前缀 (而不是完整的 blob 名称) 来收集以此前缀开头 (blob) 的多个文件。While it does not support an asterisk wildcard, you can use a partial file (blob) name prefix (instead of the full blob name) to collect multiple files (blobs) that begin with this prefix. 例如:For example:

    • <Storage_endpoint>/<Container>/<ServerName>/<DatabaseName>/ -收集特定数据库 (blob) 的所有审核文件。<Storage_endpoint>/<Container>/<ServerName>/<DatabaseName>/ - collects all audit files (blobs) for the specific database.

    • ** <Storage_endpoint> / <Container> / <ServerName> / <DatabaseName> / <AuditName> / <CreationDate> / <FileName> . .xel** - (blob) 收集特定的审核文件。<Storage_endpoint>/<Container>/<ServerName>/<DatabaseName>/<AuditName>/<CreationDate>/<FileName>.xel - collects a specific audit file (blob).

备注

在无文件名模式的情况下传递路径将生成错误。Passing a path without a file name pattern will generate an error.

initial_file_nameinitial_file_name
指定审核文件集中要开始读取审核记录的特定文件的路径和名称。Specifies the path and name of a specific file in the audit file set to start reading audit records from. 类型为 **nvarchar (260) **。Type is nvarchar(260).

备注

Initial_file_name参数必须包含有效条目,或者必须包含默认值 |NULL 值。The initial_file_name argument must contain valid entries or must contain either the default | NULL value.

audit_record_offsetaudit_record_offset
指定一个已知位置,该位置包含为 initial_file_name 指定的文件。Specifies a known location with the file specified for the initial_file_name. 使用此参数时,函数将从缓冲区中紧跟指定偏移量之后的第一个记录开始读取。When this argument is used the function will start reading at the first record of the Buffer immediately following the specified offset.

备注

Audit_record_offset参数必须包含有效条目,或者必须包含默认值 |NULL 值。The audit_record_offset argument must contain valid entries or must contain either the default | NULL value. 类型为 bigintType is bigint.

返回的表Tables Returned

下表描述此函数可返回的审核文件内容。The following table describes the audit file content that can be returned by this function.

列名称Column name 类型Type 说明Description
action_idaction_id varchar(4)varchar(4) 操作的 ID。ID of the action. 不可为 Null。Not nullable.
additional_informationadditional_information nvarchar(4000)nvarchar(4000) 仅适用于单个事件的唯一信息,以 XML 的形式返回。Unique information that only applies to a single event is returned as XML. 有少量的可审核操作包含此类信息。A small number of auditable actions contain this kind of information.

对于具有与操作相关联的 TSQL 堆栈的操作,将以 XML 格式显示一个级别的 TSQL 堆栈。One level of TSQL stack will be displayed in XML format for actions that have TSQL stack associated with them. 该 XML 格式如下:The XML format will be:

<tsql_stack><frame nest_level = '%u' database_name = '%.*s' schema_name = '%.*s' object_name = '%.*s' /></tsql_stack>

Frame nest_level 指示框架的当前嵌套级别。Frame nest_level indicates the current nesting level of the frame. 模块名称表示为由三部分组成的格式(database_name、schema_name 和 object_name)。The Module name is represented in three part format (database_name, schema_name and object_name). 模块名称将被解析为对无效的 xml 字符(如、、、)进行转义 '\<' '>' '/' '_x'The module name will be parsed to escape invalid xml characters like '\<', '>', '/', '_x'. 它们将被转义为 _xHHHH\_They will be escaped as _xHHHH\_. HHHH 代表该字符对应的四位十六进制 UCS-2 代码。The HHHH stands for the four-digit hexadecimal UCS-2 code for the character

可以为 Null。Is nullable. 如果事件没有报告其他信息,则返回 NULL。Returns NULL when there is no additional information reported by the event.
affected_rowsaffected_rows bigintbigint 适用于:仅适用于 Azure SQL 数据库Applies to: Azure SQL Database only

受执行语句影响的行数。Number of rows affected by the executed statement.
application_nameapplication_name nvarchar(128)nvarchar(128) 适用于: Azure SQL 数据库 + SQL Server 从2017开始 () Applies to: Azure SQL Database + SQL Server (starting with 2017)

执行导致审核事件的语句的客户端应用程序的名称Name of client application which executed the statement that caused the audit event
audit_file_offsetaudit_file_offset bigintbigint 适用于:仅 SQL ServerApplies to: SQL Server only

包含审核记录的文件中的缓冲区偏移量。The buffer offset in the file that contains the audit record. 不可为 null。Is not nullable.
audit_schema_versionaudit_schema_version intint 始终为 1Always 1
class_typeclass_type varchar(2)varchar(2) 发生审核的可审核实体的类型。The type of auditable entity that the audit occurs on. 不可为 null。Is not nullable.
client_ipclient_ip nvarchar(128)nvarchar(128) 适用于: Azure SQL 数据库 + SQL Server 从2017开始 () Applies to: Azure SQL Database + SQL Server (starting with 2017)

客户端应用程序的源 IPSource IP of the client application
connection_idconnection_id GUIDGUID 适用于: Azure SQL 数据库和 SQL 托管实例Applies to: Azure SQL Database and SQL Managed Instance

服务器中的连接的 IDID of the connection in the server
data_sensitivity_informationdata_sensitivity_information nvarchar(4000)nvarchar(4000) 适用于:仅适用于 Azure SQL 数据库Applies to: Azure SQL Database only

受审核查询根据数据库中分类的列返回的信息类型和敏感度标签。Information types and sensitivity labels returned by the audited query, based on the classified columns in the database. 详细了解 Azure SQL 数据库数据发现和分类Learn more about Azure SQL Database data discover and classification
database_namedatabase_name sysnamesysname 发生此操作的数据库上下文。The database context in which the action occurred. 可以为 Null。Is nullable. 对于在服务器级别发生的审核,返回 NULL。Returns NULL for audits occurring at the server level.
database_principal_iddatabase_principal_id intint 在其中执行操作的数据库用户上下文 ID。ID of the database user context that the action is performed in. 不可为 null。Is not nullable. 如果此不适用,则返回0。Returns 0 if this does not apply. 例如,如果是服务器操作,则返回 0。For example, a server operation.
database_principal_namedatabase_principal_name sysnamesysname 当前用户。Current user. 可以为 Null。Is nullable. 如果不可用,则返回 NULL。Returns NULL if not available.
duration_millisecondsduration_milliseconds bigintbigint 适用于: Azure SQL 数据库和 SQL 托管实例Applies to: Azure SQL Database and SQL Managed Instance

查询执行持续时间,以毫秒为单位Query execution duration in milliseconds
event_timeevent_time datetime2datetime2 触发可审核操作的日期和时间。Date and time when the auditable action is fired. 不可为 null。Is not nullable.
file_namefile_name varchar(260)varchar(260) 作为记录来源的审核日志文件的路径和名称。The path and name of the audit log file that the record came from. 不可为 null。Is not nullable.
is_column_permissionis_column_permission bitbit 指示是否为列级权限的标志。Flag indicating if this is a column level permission. 不可为 null。Is not nullable. 当 permission_bitmask = 0 时返回 0。Returns 0 when the permission_bitmask = 0.
1 = true1 = true
0 = false0 = false
object_idobject_id intint 发生审核的实体的 ID。The ID of the entity on which the audit occurred. 这包括:This includes the following:
服务器对象Server objects
数据库Databases
数据库对象Database objects
架构对象Schema objects
不可为 null。Is not nullable. 如果实体是服务器本身或者没有在对象级别执行审核,则返回 0。Returns 0 if the entity is the Server itself or if the audit is not performed at an object level. 例如,对于 Authentication,则返回 NULL。For example, Authentication.
object_nameobject_name sysnamesysname 发生审核的实体的名称。The name of the entity on which the audit occurred. 这包括:This includes the following:
服务器对象Server objects
数据库Databases
数据库对象Database objects
架构对象Schema objects
可以为 Null。Is nullable. 如果实体是 Server 自身或者没有在对象级别执行审核,则返回 NULL。Returns NULL if the entity is the Server itself or if the audit is not performed at an object level. 例如,对于 Authentication,则返回 NULL。For example, Authentication.
permission_bitmaskpermission_bitmask varbinary(16)varbinary(16) 在某些操作中,这是授予、拒绝或撤消的权限。In some actions, this is the permissions that were grant, denied, or revoked.
response_rowsresponse_rows bigintbigint 适用于: Azure SQL 数据库和 SQL 托管实例Applies to: Azure SQL Database and SQL Managed Instance

在结果集中返回的行数。Number of rows returned in the result set.
schema_nameschema_name sysnamesysname 在其中执行操作的架构上下文。The schema context in which the action occurred. 可以为 Null。Is nullable. 对于在架构外发生的审核,返回 NULL。Returns NULL for audits occurring outside a schema.
sequence_group_idsequence_group_id varbinaryvarbinary 适用于:仅 SQL Server 从2016开始 () Applies to: SQL Server only (starting with 2016)

唯一标识符Unique identifier
sequence_numbersequence_number intint 跟踪单个审核记录中的记录顺序,该记录太大而无法放在写入缓冲区中以进行审核。Tracks the sequence of records within a single audit record that was too large to fit in the write buffer for audits. 不可为 null。Is not nullable.
server_instance_nameserver_instance_name sysnamesysname 发生审核的服务器实例的名称。Name of the server instance where the audit occurred. 使用标准服务器\实例格式。The standard server\instance format is used.
server_principal_idserver_principal_id intint 在其中执行操作的登录上下文 ID。ID of the login context that the action is performed in. 不可为 null。Is not nullable.
server_principal_nameserver_principal_name sysnamesysname 当前登录名。Current login. 可以为 Null。Is nullable.
server_principal_sidserver_principal_sid varbinaryvarbinary 当前登录名 SID。Current login SID. 可以为 Null。Is nullable.
session_idsession_id smallintsmallint 发生该事件的会话的 ID。ID of the session on which the event occurred. 不可为 null。Is not nullable.
session_server_principal_namesession_server_principal_name sysnamesysname 会话的服务器主体。Server principal for session. 可以为 Null。Is nullable.
statementstatement nvarchar(4000)nvarchar(4000) TSQL 语句(如果存在)。TSQL statement if it exists. 可以为 Null。Is nullable. 如果不适用,则返回 NULL。Returns NULL if not applicable.
成功succeeded bitbit 指示触发事件的操作是否成功。Indicates whether the action that triggered the event succeeded. 不可为 null。Is not nullable. 对于除登录事件之外的所有事件,它仅报告权限检查(而不是操作)成功或失败。For all events other than login events, this only reports whether the permission check succeeded or failed, not the operation.
1 = success1 = success
0 = 失败0 = fail
target_database_principal_idtarget_database_principal_id intint 执行 GRANT/DENY/REVOKE 操作的数据库主体。The database principal the GRANT/DENY/REVOKE operation is performed on. 不可为 null。Is not nullable. 如果不适用,则返回 0。Returns 0 if not applicable.
target_database_principal_nametarget_database_principal_name sysnamesysname 操作的目标用户。Target user of action. 可以为 Null。Is nullable. 如果不适用,则返回 NULL。Returns NULL if not applicable.
target_server_principal_idtarget_server_principal_id intint 执行 GRANT/DENY/REVOKE 操作的服务器主体。Server principal that the GRANT/DENY/REVOKE operation is performed on. 不可为 null。Is not nullable. 如果不适用,则返回 0。Returns 0 if not applicable.
target_server_principal_nametarget_server_principal_name sysnamesysname 操作的目标登录名。Target login of action. 可以为 Null。Is nullable. 如果不适用,则返回 NULL。Returns NULL if not applicable.
target_server_principal_sidtarget_server_principal_sid varbinaryvarbinary 目标登录名的 SID。SID of target login. 可以为 Null。Is nullable. 如果不适用,则返回 NULL。Returns NULL if not applicable.
transaction_idtransaction_id bigintbigint 适用于:仅 SQL Server 从2016开始 () Applies to: SQL Server only (starting with 2016)

用于在一个事务中标识多个审核事件的唯一标识符Unique identifier to identify multiple audit events in one transaction
user_defined_event_iduser_defined_event_id smallintsmallint 适用于: SQL Server 2012 (11.x)SQL Server 2012 (11.x) 和更高版本、Azure SQL 数据库和 SQL 托管实例Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later, Azure SQL Database and SQL Managed Instance

作为参数传递给 sp_audit_write的用户定义事件 id。User defined event id passed as an argument to sp_audit_write. 对于系统事件为 NULL (默认) 值为NULL ,对于用户定义的事件则为非零。NULL for system events (default) and non-zero for user-defined event. 有关详细信息,请参阅 (transact-sql)sp_audit_write For more information, see sp_audit_write (Transact-SQL).
user_defined_informationuser_defined_information nvarchar(4000)nvarchar(4000) 适用于: SQL Server 2012 (11.x)SQL Server 2012 (11.x) 和更高版本、Azure SQL 数据库和 SQL 托管实例Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later, Azure SQL Database and SQL Managed Instance

用于记录用户要使用 sp_audit_write 存储过程在审核日志中记录的任何其他信息。Used to record any extra information the user wants to record in audit log by using the sp_audit_write stored procedure.

备注Remarks

如果传递到fn_get_audit_filefile_pattern参数引用的路径或文件不存在,或者该文件不是审核文件,则返回MSG_INVALID_AUDIT_FILE错误消息。If the file_pattern argument passed to fn_get_audit_file references a path or file that does not exist, or if the file is not an audit file, the MSG_INVALID_AUDIT_FILE error message is returned.

权限Permissions

  • SQL Server:需要 CONTROL Server 权限。SQL Server: Requires the CONTROL SERVER permission.
  • AZURE SQL 数据库:需要 CONTROL Database 权限。Azure SQL Database: Requires the CONTROL DATABASE permission.
    • 服务器管理员可以访问服务器上所有数据库的审核日志。Server admins can access audit logs of all databases on the server.
    • 非服务器管理员只能从当前数据库访问审核日志。Non server admins can only access audit logs from the current database.
    • 将跳过不满足上述条件的 blob, (将在查询输出消息) 中显示跳过的 blob 的列表,并且函数将仅返回允许访问的 blob 中的日志。Blobs that do not meet the above criteria will be skipped (a list of skipped blobs will be displayed in the query output message), and the function will return logs only from blobs for which access is allowed.

示例Examples

  • SQL ServerSQL Server

    此示例从名为 \\serverName\Audit\HIPAA_AUDIT.sqlaudit 的文件读取。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  
    
  • Azure SQL 数据库Azure SQL Database

    下面的示例从名为的文件中读取 ShiraServer/MayaDB/SqlDbAuditing_Audit/2017-07-14/10_45_22_173_1.xelThis example reads from a file that is named ShiraServer/MayaDB/SqlDbAuditing_Audit/2017-07-14/10_45_22_173_1.xel:

    SELECT * FROM sys.fn_get_audit_file ('https://mystorage.blob.core.windows.net/sqldbauditlogs/ShiraServer/MayaDB/SqlDbAuditing_Audit/2017-07-14/10_45_22_173_1.xel',default,default);
    GO  
    

    此示例从同一文件中读取,但使用其他 T-sql 子句 (TOPORDER BYWHERE 子句来筛选函数返回的审核记录) :This example reads from the same file as above, but with additional T-SQL clauses (TOP, ORDER BY, and WHERE clause for filtering the audit records returned by the function):

    SELECT TOP 10 * FROM sys.fn_get_audit_file ('https://mystorage.blob.core.windows.net/sqldbauditlogs/ShiraServer/MayaDB/SqlDbAuditing_Audit/2017-07-14/10_45_22_173_1.xel',default,default)
    WHERE server_principal_name = 'admin1'
    ORDER BY event_time
    GO
    

    此示例从以开头的服务器读取所有审核日志 ShThis example reads all audit logs from servers that begin with Sh:

    SELECT * FROM sys.fn_get_audit_file ('https://mystorage.blob.core.windows.net/sqldbauditlogs/Sh',default,default);
    GO  
    

有关如何创建审核的完整示例,请参阅 SQL Server Audit(数据库引擎)For a full example about how to create an audit, see SQL Server Audit (Database Engine).

有关设置 Azure SQL 数据库审核的信息,请参阅 SQL 数据库审核入门For information on setting up Azure SQL Database auditing, see Get Started with SQL Database auditing.

另请参阅See Also

CREATE SERVER AUDIT (Transact-SQL) CREATE SERVER AUDIT (Transact-SQL)
ALTER SERVER AUDIT (Transact-SQL) ALTER SERVER AUDIT (Transact-SQL)
DROP SERVER AUDIT (Transact-SQL) DROP SERVER AUDIT (Transact-SQL)
CREATE SERVER AUDIT SPECIFICATION (Transact-SQL) CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)
ALTER SERVER AUDIT SPECIFICATION (Transact-SQL) ALTER SERVER AUDIT SPECIFICATION (Transact-SQL)
DROP SERVER AUDIT SPECIFICATION (Transact-SQL) DROP SERVER AUDIT SPECIFICATION (Transact-SQL)
CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL) CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)
ALTER DATABASE AUDIT SPECIFICATION (Transact-SQL) ALTER DATABASE AUDIT SPECIFICATION (Transact-SQL)
DROP DATABASE AUDIT SPECIFICATION (Transact-SQL) DROP DATABASE AUDIT SPECIFICATION (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL) ALTER AUTHORIZATION (Transact-SQL)
sys.server_audits (Transact-SQL) sys.server_audits (Transact-SQL)
sys.server_file_audits (Transact-SQL) sys.server_file_audits (Transact-SQL)
sys.server_audit_specifications (Transact-SQL) sys.server_audit_specifications (Transact-SQL)
sys.server_audit_specification_details (Transact-SQL) sys.server_audit_specification_details (Transact-SQL)
sys.database_audit_specifications (Transact-SQL) sys.database_audit_specifications (Transact-SQL)
sys.database_audit_specification_details (Transact-SQL) sys.database_audit_specification_details (Transact-SQL)
sys.dm_server_audit_status (Transact-SQL) sys.dm_server_audit_status (Transact-SQL)
sys.dm_audit_actions (Transact-SQL) sys.dm_audit_actions (Transact-SQL)
sys.dm_audit_class_type_map (Transact-SQL) sys.dm_audit_class_type_map (Transact-SQL)
创建服务器审核和服务器审核规范Create a Server Audit and Server Audit Specification