1

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?

  • I used Powershell and a stored procedure to automate the collection of audit information - dba.stackexchange.com/a/180314/60085 – Scott Hodgin Aug 1 '17 at 9:20
  • @ScottHodgin this is interesting, but our external process needs to read data with sys.fn_get_audit_file. – user_0 Aug 1 '17 at 9:29
  • That's exactly what my process does - it uses wildcard . to process all available audit files. By using wildcards, you don't have to know the exact file name. You have to be careful when reading audit files by making sure you're not trying to process them while they are currently being written to AND that you only read them once. That's why my process uses a staging fileshare. – Scott Hodgin Aug 1 '17 at 9:32
  • @ScottHodgin your process is usefull, but as I can see you cant read the current audit, right? You wrote: " 'skip' over files that are currently being written". Actually I need to read also the current file. I'd like really to understand why in sys.server_file_audits I cant read the real filename. – user_0 Aug 1 '17 at 9:40
1

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.
  • I missed the part about the incremental number. Thank you. – user_0 Aug 1 '17 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.