Sql-server – Select from sys.server_file_audits returns wrong filename

auditsql server

I activated an audit on a SQL Server 2016 istance.

Version i SP1 CU3, Enterprise Edition.

With this query:

SELECT * FROM sys.server_file_audits

I get as log_file_path: O:\auditfiles\

In log_file_name: logins_3D6DA4C0-B388-4175-88A5-D2820733CFB7.sqlaudit

But when I query:

SELECT * FROM sys.fn_get_audit_file ('O:\auditfiles\logins_3D6DA4C0-B388-4175-88A5-D2820733CFB7.sqlaudit',default,default);

It returns nothing.

Checking on file system the real name of the file is:

logins_3D6DA4C0-B388-4175-88A5-D2820733CFB7_0_131451051332700000.sqlaudit

Where the filename part _0_131451051332700000 is not retrived from query on sys schema.

When I select with correct filename i get the results.

I need to automate this in order to collect data from external queries.

How can I retrive the correct file name?

Best Answer

Reviewing the documentation for sys.server_file_audits, the log_file_name is (highlighting mine)

Base name for the log file supplied in the CREATE AUDIT DDL. An incremental number is added to the base_log_name file as a suffix to create the log file name.

Depending on how quickly your sqlaudit files are being created, it can be extremely difficult to determine what the 'current' audit file is.

If you want a quick one-off select of audit information, you can use wildcards of '*.sqlaudit' to read ALL files in the directory.

SELECT * FROM sys.fn_get_audit_file ('O:\auditfiles\logins*.sqlaudit',default,default);

Reading sqlaudit files that are currently being written to presents challenges in the fact that rows can be written to that active file immediately AFTER you have just read it, so you might miss future audit rows if you don't continually select them. Also, be advised that audit files will continue to be written to your directory path and the wildcard select will run longer and longer unless you implement some kind of pruning process of the sqlaudit files.

If your intention is to track and save this audit information, I'd suggest implementing a solution similar to an answer I gave for 'How to load data to a table from SQL Server Audit File?'

It uses a Sql Agent job that executes a stored procedure to process the sqlaudit files. In summary, the stored procedure:

  • Uses Powershell to move any audit files (that are not currently being written to) to a staging fileshare. The powershell command uses the -ErrorAction SilentlyContinue parameter which allows the command to 'skip' over files that are currently being written to when copying to the staging fileshare. Eventually, the 'locked' files will become available to be copied on a future interval.
  • Now that I have available SQLAudit files to read from the staging fileshare, we're able to use sys.fn_get_audit_file to process all SQLAudit files from the staging fileshare by using wildcards.
  • After consuming the files from the staging fileshare, I use Powershell to delete the files from the staging fileshare.
Related Question