I love SQL Audit, mainly because I can use it avoid login auditing going to the SQL Server error log, but that is besides the point. However, an audit is only useful if you can use the information within the audit easily. This is one negative on SQL Audit, I do not find it easy to query, but here is how you can do it.
I created an audit to log failed logins
USE [master]
GO
CREATE SERVER AUDIT [Login_Audit]
TO FILE
( FILEPATH = N'C:\Audit\'
,MAXSIZE = 1024 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'd60fca2d-14a6-4cc5-bb6c-78a9249acae6'
)
GO
CREATE SERVER AUDIT SPECIFICATION [Login_Audit]
FOR SERVER AUDIT [Login_Audit]
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO
What I want to know is how many failed logins happen in a 24 hour period and what logins are failing.
SELECT server_principal_name, COUNT(*) as FailedLoginCount
FROM sys.fn_get_audit_file ('C:\Audit\*',default,default)
WHERE event_time > GETDATE()-1 AND action_id = 'LGIF'
GROUP BY server_principal_name
ORDER BY FailedLoginCount Desc
Not the most elegant method for getting the information I want, but it is a starting place.
