Viewing a SQLServerAudit File

Problem: When we enable a database audit the output file is .SQLAudit file and it does not reveal any information as it is encrypted by SQL server. How to view

Solution: sys.fn_get_audit_file

There is a system function which extracts the metadata from the audit file generated by the SQL Server Audit output file. This function accepts the file path and the default parameters as arguments and returns information like the object name, database name and the user who has triggered the audit.

Syntax:

fn_get_audit_file ( file_pattern,  

    { default | initial_file_name | NULL },  

    { default | audit_record_offset | NULL } ) 

To know how to create a database audit from a SQL Server management studio, please go through my previous article. database auditing

Example: In this example, I would like to show the delete auditing that has triggered from a database audit file.

I have an audit file that has been triggered by delete statements in the database. This file path is sent as an argument to the function to view the output.

SELECT event_time,

                action_id,

                object_name,

                statement,

                database_name,

                server_principal_name

FROM sys.fn_get_audit_file('C:\Temp\Audit-20170424-162710_E5FF0CCD-3446-406E-910B-9A5A77AA0260_0_131375393908760000.sqlaudit', DEFAULT, DEFAULT);

GO

OUTPUT:

event_time	action_id	object_name	statement	database_name	server_principal_name
2017-04-24 20:29:50.8795593	AUSC				TYLER\mahesh.sambu
2017-04-24 20:31:09.1098741	DL	Orders	DELETE FROM dbo.orders where UnitId='m789'	DB	TYLER\mahesh.sambu
2017-04-25 00:29:56.8242544	DL	History	DELETE  FROM [dbo].[History] where DATEDIFF(dd,[Entry_Date],getdate())>=365	DB	NT AUTHORITY\SYSTEM
2017-04-26 01:43:54.5946628	DL	History	DELETE  FROM [dbo].[History] where DATEDIFF(dd,[Entry_Date],getdate())>=365	DB	NT AUTHORITY\SYSTEM