Versions: SQL Server 2008 R2 – 2014
I'm using a server audit to find changes to logins on my SQL Server instances, and writing it to the ERRORLOG directory path. Unfortunately, not all the instances use the same path for the ERRORLOG directory. I can get the path using SELECT SERVERPROPERTY('ErrorLogFileName')
but I need to "insert" the return value of that statement to two other places. The first is when I'm creating the Server Audit:
CREATE SERVER AUDIT [ServerAuditName]
TO FILE
( FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL\Log'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
So, I need to "insert" the results of the SERVERPROPERTY query into a variable or…something, so I can use it in the FILEPATH value above. Secondarily, I also need to find the ERRORLOG directory path when reading the *.sqlaudit files the Server Audit creates. I can easily use the following to get files, but here too, I need to "insert" the filepath vaule with the result of the SERVERPROPERTY query:
SELECT *
FROM sys.fn_get_audit_file('Filepath\*.sqlaudit',DEFAULT, DEFAULT)
I have tried some "google-fu" and have looked at a couple of string functions, like replace and the "trim-family"…which wasn't very relevant. If anyone can help or point me to a good article or two on how to achieve this, I would greatly appreciate it.
Best Answer
The first thing you need to do is chop the filename off of that full path, else you will just get an error. The following does that using a CTE to apply the expression twice so that I didn't have to copy/paste the
CONVERT
:In the query above, I used
COLLATE Latin1_General_100_CI_AS
to force a case-insensitive comparison as I noticed different casing between "regular" SQL Server (i.e. running via a service) and SQL Server Express LocalDB in terms of the output of theSERVERPROPERTY('ErrorLogFileName')
function:"regular":
C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017EXPRESS\MSSQL\Log\ERRORLOG
LocalDB:
C:\Users\Solomon\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\LostInTranslation\error.log
You can use that with Dynamic SQL to generate the
CREATE SERVER AUDIT
statement as follows:For reading the audit file, you don't need the Dynamic SQL as you can pass the expression to the DMF as follows: