Failed and Successful Login Auditing Using xp_readerrorlog
Use this script to audit logins on your server. Undocumented extended stored procedure xp_readerrorlog will pull all rows within your designated search criteria and dump the data into a temp table. From there a second temp table is created to find your failed logins and group them per User, IP and Month. The third temp table is used to find your last successful login per User and IP.
** Note that you must be logging both successful and failed logins in server properties to pull users last login.
USE [master]
GO
DECLARE @FirstDay DATETIME,
@LastDay DATETIME
SET @FirstDay = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 2, 0); --Edit start date here, default first day of previous month
SET @LastDay = GETDATE(); --Edit end date of log pull here
IF OBJECT_ID('tempdb..#TempLog') IS NOT NULL
BEGIN
DROP TABLE #TempLog
END;
IF OBJECT_ID('tempdb..#TempLog2') IS NOT NULL
BEGIN
DROP TABLE #TempLog2
END;
IF OBJECT_ID('tempdb..#TempLog3') IS NOT NULL
BEGIN
DROP TABLE #TempLog3
END;
CREATE TABLE #TempLog (--Create temp table for xp_readerrorlog data, raw data dump
LogDate DATETIME,
ProcessInfo NVARCHAR(50),
TEXT NVARCHAR(2000)
);
CREATE TABLE #TempLog2 (--Create temp table2 for xp_readerrorlog data, grouped failed logins
[ServerName] NVARCHAR(255),
[User] NVARCHAR(255),
[IP] NVARCHAR(255),
[FailedAttempts] INT,
[Month] NVARCHAR(255)
);
CREATE TABLE #TempLog3 (--Create temp table3 for xp_readerrorlog data, last successful login
[ServerName] NVARCHAR(255),
[User] NVARCHAR(255),
[IP] NVARCHAR(255),
[LogDate] NVARCHAR(255)
);
INSERT INTO #TempLog (LogDate, ProcessInfo, TEXT)
EXEC master.dbo.xp_readerrorlog -1, 1, NULL, NULL, @FirstDay, @LastDay;
INSERT INTO #TempLog2--Group failed logins
SELECT
@@SERVERNAME AS [ServerName],
SUBSTRING(TEXT,(CHARINDEX('''',TEXT)+1),(CHARINDEX('''',TEXT,24)-(CHARINDEX('''',TEXT)+1))) AS [User],
SUBSTRING(TEXT,(CHARINDEX(':',TEXT)+2),(CHARINDEX(']',TEXT,25)-(CHARINDEX(':',TEXT)+2))) AS [IP],
'1' AS [FailedAttempts],--Failed login count
DATENAME(m,LogDate) AS [Month]
FROM #TempLog
WHERE ProcessInfo = 'Logon'
AND TEXT LIKE '%Login failed%'
SELECT
[ServerName],
[User],
[IP],
COUNT([FailedAttempts]) AS [FailedAttempts],
[Month]
FROM #TempLog2
GROUP BY [ServerName], [User], [IP], [Month]
ORDER BY [User];
INSERT INTO #TempLog3--Last successful login per user
SELECT
@@SERVERNAME AS [ServerName],
SUBSTRING(TEXT,(CHARINDEX('''',TEXT)+1),(CHARINDEX('''',TEXT,28)-(CHARINDEX('''',TEXT)+1))) AS [User],
SUBSTRING(TEXT,(CHARINDEX('[',TEXT)+9),(CHARINDEX(']',TEXT,28)-(CHARINDEX('[',TEXT)+9))) AS [IP],
LogDate AS [LastLogin]
FROM #TempLog
WHERE ProcessInfo = 'Logon'
AND TEXT LIKE '%Login succeeded%'
AND TEXT NOT LIKE '%NT AUTHORITY%'
AND TEXT NOT LIKE '%Database Mirroring%'
GROUP BY TEXT, LogDate;
SELECT
[ServerName],
[User],
[IP],
MAX(LogDate) AS [LastLogin]
FROM #TempLog3
GROUP BY [ServerName], [User], [IP];
IF OBJECT_ID('tempdb..#TempLog') IS NOT NULL
BEGIN
DROP TABLE #TempLog
END;
IF OBJECT_ID('tempdb..#TempLog2') IS NOT NULL
BEGIN
DROP TABLE #TempLog2
END;
IF OBJECT_ID('tempdb..#TempLog3') IS NOT NULL
BEGIN
DROP TABLE #TempLog3
END; 
