How to read SQL Server Audit Files using fn_get_audit_file function

There is a system function in SQL Server called sys.fn_get_audit_file that returns information from an audit file created by a server or database audit. It requires the CONTROL SERVER permission.

Here is a simple T-SQL query to read all audit files from a specified location (for example ‘C:\Audit\’):

SELECT * FROM sys.fn_get_audit_file
('C:\Audit\*', null, null)
GO

To read all audit files with a specified name (for example from an audit called 'test_audit') from a specified location (for example ‘C:\Audit\’):

SELECT * FROM sys.fn_get_audit_file
('C:\Audit\test_audit_*.sqlaudit', null, null)
GO

To read all information from a specific audit file (for example ‘C:\Audit\test_audit_file_name.sqlaudit’):

SELECT * FROM sys.fn_get_audit_file
('C:\Audit\test_audit_file_name.sqlaudit', null, null)
GO

You can create your own query that returns only information that you need from the audit files. For example:

SELECT
[event_time], [session_id], [object_id], [session_server_principal_name], [server_principal_name],
[database_principal_name], [server_instance_name], [database_name],
[object_name], [file_name], [statement]
FROM sys.fn_get_audit_file
('C:\Audit\*', null, null)
GO

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">