Difference between sys.fn_get_audit_file & sys.fn_xe_file_target_read_file with example

Difference between sys.fn_get_audit_file & sys.fn_xe_file_target_read_file

sys.fn_get_audit_file :-

Returns information from an audit file created by a server audit in SQL Server.

SQL Server

This example reads from a file that is named \\serverName\Audit\HIPPA_AUDIT.sqlaudit.
SELECT * FROM sys.fn_get_audit_file ('\\serverName\Audit\HIPPA_AUDIT.sqlaudit',default,default);
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Azure SQL Database

This 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This 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

sys.fn_xe_file_target_read_file:-

Reads files that are created by the Extended Events asynchronous file target. One event, in XML format, is returned per row.

Retrieving data from file targets
The following example gets all the rows from all the files. In this example the file targets and metafiles are located in the trace folder on the C:\ drive.

SELECT * FROM sys.fn_xe_file_target_read_file('C:\traces\*.xel', 'C:\traces\metafile.xem', null, null);


Reading large result sets by executing sys.fn_xe_file_target_read_file in Management Studio may result in an error. Use the Results to File mode (Ctrl+Shift+F) to export large result sets to a file and read the file with another tool instead.

Example:-

Extended Events query to get  deadLocks
====================================

1. Create a separate event for deadlock

-------------------------------------------------------------------
 CREATE EVENT SESSION DeadLocks
    ON SERVER
     ADD EVENT
       sqlserver.lock_deadlock
   (
         ACTION
          (
            sqlserver.database_id,
            sqlserver.client_app_name,
            sqlserver.sql_text
           )
   )

ADD TARGET package0.asynchronous_file_target
( SET filename = 'D:\ffxfer\DeadLocks_Capture.xel',
metadatafile = 'D:\ffxfer\DeadLocks_Capture.mta',
max_file_size = 10,
max_rollover_files = 10);
GO

2. Start the Event

-------------------------------

ALTER EVENT SESSION DeadLocks
ON SERVER
STATE = START
GO

3. How to collect  data to temp table

---------------------------------------

DECLARE @xel_filename varchar(256) = 'D:\ffxfer\DeadLocks_Capture.xel '

DECLARE @mta_filename varchar(256) = 'D:\ffxfer\DeadLocks_Capture.mta'

SELECT CONVERT(xml, event_data) as Event_Data

INTO #File_Data

FROM sys.fn_xe_file_target_read_file(@xel_filename, @mta_filename, NULL, NULL)

4. how to retrieve data.

select * from #File_Data


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Creating a server audit containing a WHERE clause

---------

CREATE DATABASE TestDB;  
GO  

USE TestDB;  
GO
  
CREATE SCHEMA DataSchema;  
GO
  
CREATE TABLE DataSchema.GeneralData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL);  
GO
  
CREATE TABLE DataSchema.SensitiveData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL);  
GO
  

-- Create the server audit in the master database  

USE master;  
GO
  
CREATE SERVER AUDIT AuditDataAccess  
    TO FILE ( FILEPATH ='C:\SQLAudit\' )  
    WHERE object_name = 'SensitiveData' ;  
GO

-- start the server audit

  

ALTER SERVER AUDIT AuditDataAccess WITH (STATE = ON);  
GO
  

-- Create the database audit specification in the TestDB database

  
USE TestDB;  
GO


  
CREATE DATABASE AUDIT SPECIFICATION [FilterForSensitiveData]
  
FOR SERVER AUDIT [AuditDataAccess]
   
ADD (SELECT ON SCHEMA::[DataSchema] BY [public])
  
WITH (STATE = ON);  
GO
  
-- Trigger the audit event by selecting from tables
  
SELECT ID, DataField FROM DataSchema.GeneralData;
  
SELECT ID, DataField FROM DataSchema.SensitiveData;  
GO  
-- Check the audit for the filtered content  

SELECT * FROM fn_get_audit_file('C:\SQLAudit\AuditDataAccess_*.sqlaudit',default,default);  

GO

Thanks for reading..