Technical Article

How to read an audit file

,

The script reads data from audit files for a particular audit in a tabular form.

That is an answer for a question from our fellow member - https://www.sqlservercentral.com/Forums/1916789/Audting-User-Logins-How-might-you-do-it . But I have decided to share it because it may be useful for other members.

In order to use it you have to replace {NameOfYourAudit} with the name of the audit on your SQL Server instance. That audit must be a file target audit. You also can filter the events which you are interested in by uncommenting the where clause and changing {MyStatement}. You can apply your own filters as well, for example you can use: client_ip, application_name, server_principal_name, and many other fields for filtering. Apart from custom filtering you can apply the custom ordering by changing the order by clause.

You can find more information about sys.fn_get_audit_file on the Microsoft web site - https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql

--Get the path for your audit files
declare @log_dir nvarchar(260);
set @log_dir = 
(select log_file_path+'*.sqlaudit'
from sys.server_file_audits
where name = '{NameOfYourAudit}');

--Read all the audit data you are interested in
SELECT * 
FROM sys.fn_get_audit_file(@log_dir, default, default)
--You can use a filter if requred
--where statement like '%{MyStatement}%'
--Show the most recent records first
order by event_time desc;

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating