Forum Topic

Thanks for your participation and contribution.


Back to Security

Auditing Login Attempts

Post a reply
1874 views
i90runner1
i90runner1
Auditing Login Attempts
 

DROP TABLE IF EXISTS #temptable;
CREATE TABLE #temptable
(
    [LogDate] DATETIME,
    [ProcessInfo] NVARCHAR(12),
    [Text] NVARCHAR(3999)
);

INSERT INTO #temptable
(
    [LogDate],
    [ProcessInfo],
    [Text]
)
EXEC sys.sp_readerrorlog @p1 = 0,        -- int -- 0 = current, 1 = Archive #1, 2 = Archive
                         @p2 = 1,        -- int -- 1 or NULL = error log, 2 = SQL Agent log
                         @p3 = 'failed', -- varchar(255) -- String one we want to search for
                         @p4 = 'login';  -- varchar(255) -- String two we want to search to further refine the result

SELECT COUNT(*) AS Count FROM #temptable

SELECT REVERSE(SUBSTRING(REVERSE(Text), 2, 13)) AS [IPAddress],
       COUNT(*) AS FailedAttempts
FROM #temptable
WHERE Text LIKE 'Login failed for user%'
GROUP BY REVERSE(SUBSTRING(REVERSE(Text), 2, 13));

SELECT * FROM #temptable


1-1 of 1

Reply to this discussion

You cannot edit posts or make replies: You should be logged in before you can post.