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.