Create Server Audit with Specification

First we will create the SERVER AUDIT.

Next we create the SERVER AUDIT SPECIFICATION and ATTACH IT to the SERVER AUDIT.

AWS RDS example follows.

USE [master]
GO
 
/****** Object:  Audit [LoginAuditLog]    Script Date: 2/23/2017 11:58:47 AM ******/
CREATE SERVER AUDIT [LoginAuditLog]
TO FILE 
(    FILEPATH = N'E:\MSSQL\Log\'
	,MAXSIZE = 10 MB
	,MAX_FILES = 10
	,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [LoginAuditLog] WITH (STATE = ON)
GO

USE [master]
GO
 
CREATE SERVER AUDIT SPECIFICATION [LoginAuditLogSpecs]
FOR SERVER AUDIT [LoginAuditLog]
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO

SERVER AUDIT SPECIFICATION ON AWS RDS works a little differently. You must specify the PATH on the D drive according to AWS documentation. A few other settings also cannot be changed. (Example here subject to change.)

USE [master]
GO
 
/****** Object:  Audit [LoginAuditLog]    Script Date: 1/8/2021 10:21:52 AM ******/
CREATE SERVER AUDIT [LoginAuditLog]
TO FILE 
(    FILEPATH = N'D:\rdsdbdata\SQLAudit\'
	,MAXSIZE = 10 MB
	,MAX_ROLLOVER_FILES = 2147483647   -- This value cannot be configured in RDS as of 2021
	,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
 
)
WHERE ([database_name]<>'rdsadmin')
 
ALTER SERVER AUDIT [LoginAuditLog] WITH (STATE = ON)
GO

USE [master]
GO
 
CREATE SERVER AUDIT SPECIFICATION [LoginAuditLogSpecs]
FOR SERVER AUDIT [LoginAuditLog]
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO