Get IP addresses that lead to SQL Server login failure.

IPAddressesOfLoginFailure.sql

DECLARE @LogIndex INT
DECLARE @LastCheckDate DATETIME

SET @LogIndex = 0
SET @LastCheckDate = '2023-12-21'

DECLARE @Logs TABLE
(
    LogDate DATETIME,
    ProcessInfo NVARCHAR(4000),
    [Text] NVARCHAR(MAX)
)

INSERT INTO @Logs
EXECUTE sp_readerrorlog @p1 = @LogIndex, @p2 = 1, @p3 = N'The login packet used to open the connection is structurally invalid'

INSERT INTO @Logs
EXECUTE sp_readerrorlog @p1 = @LogIndex, @p2 = 1, @p3 = N'Could not find a login matching the name provided'

INSERT INTO @Logs
EXECUTE sp_readerrorlog @p1 = @LogIndex, @p2 = 1, @p3 = N'Length specified in network packet payload did not match number of bytes read'

SELECT IPAddress, COUNT(*) AS [Count]
FROM (
    SELECT TRIM(' ]
' + CHAR(10) + CHAR(13) FROM RIGHT([Text], LEN([Text]) - CHARINDEX('[CLIENT:', [Text]) - LEN('CLIENT: '))) AS IPAddress
    FROM @Logs
    WHERE LogDate >= @LastCheckDate
) T
GROUP BY IPAddress
ORDER BY [Count] DESC

DECLARE @IPAddress VARCHAR(128)
SET @IPAddress = 'x.x.x.x'

SELECT TOP 10 *
FROM (
    SELECT TRIM(' ]
' + CHAR(10) + CHAR(13) FROM RIGHT([Text], LEN([Text]) - CHARINDEX('[CLIENT:', [Text]) - LEN('CLIENT: '))) AS IPAddress, *
    FROM @Logs
    WHERE LogDate >= @LastCheckDate
) T
where IPAddress = @IPAddress
ORDER BY LogDate DESC

SELECT TOP 10 *
FROM (
    SELECT TRIM(' ]
' + CHAR(10) + CHAR(13) FROM RIGHT([Text], LEN([Text]) - CHARINDEX('[CLIENT:', [Text]) - LEN('CLIENT: '))) AS IPAddress, *
    FROM @Logs
    WHERE LogDate >= @LastCheckDate
) T
where IPAddress = @IPAddress
ORDER BY LogDate