如何从sp_readerrorlog中获取补充信息

Question

在审计我的服务器中的失败登录时，使用服务提供商_阅读日志过程在运行以下查询时得到以下结果：

 EXEC sp_readerrorlog 0, 1, 'Login failed' 

它向我展示了试图登录的服务器的IP地址。

我知道ping -a将ip地址解析为主机名

有什么想法，甚至是模糊的想法，或者暗示，我怎样才能用T找到那些主机名呢？

假设我可以到达ip地址并将其放入一个变量中。那么，我如何才能去做一个魔术，并得到主机名的特定ip地址？

Answer 1

正如对您的问题的其他评论所指出的，除了TSQL之外，可能还有更好的方法来获取这些数据，但是下面是我使用TSQL拼凑在一起的一些东西。

--Declare variables
--OldDelim and NewDelim are used to split on multi-char (Thanks to Aaron Bertrand for the idea)
DECLARE @olddelim NVARCHAR(10) = 'client:'
    ,@newdelim NCHAR(1) = NCHAR(9999);-- pencil (✏)
DECLARE @RowsToProcess INT
DECLARE @txt TABLE (txt VARCHAR(100))
DECLARE @cmd VARCHAR(100)
DECLARE @LogInfo TABLE (LogDate DATETIME,ProcessInfo VARCHAR(100),txt VARCHAR(max))

--Retrieve errorlog information
INSERT INTO @LogInfo
EXEC sp_readerrorlog 0,1,'Login failed'

--temp table to hold the results of parsing the errorlog info
DROP TABLE IF EXISTS #DataToProcess

--add row number to facilitate while loop processing and updates of temp table
SELECT *
    ,trim(replace(value, ']', '')) AS HostNameIP
    ,convert(VARCHAR(30), NULL) AS HostName
    ,ROW_NUMBER() OVER (
        ORDER BY newid()
        ) AS rn
INTO #DataToProcess     --insert into temp table
FROM @LogInfo li
CROSS APPLY (           --cross apply split information to get HostIP
    SELECT value
    FROM string_split(replace(li.txt, @olddelim, @newdelim), @newdelim)
    WHERE value LIKE '%]%'
    ) ca

SET @RowsToProcess = (
        SELECT count(*)
        FROM #DataToProcess
        )
DECLARE @cntr INT = 1

WHILE @cntr <= @RowsToProcess
BEGIN
    SET @cmd = 'nslookup ' + (
            SELECT HostNameIP
            FROM #DataToProcess
            WHERE rn = @cntr
            )

    DELETE FROM @txt
    INSERT INTO @txt
    EXEC xp_cmdshell @cmd

    --Update temp table with HostName from nslookup
    UPDATE #DataToProcess
    SET HostName = (
            SELECT trim(replace(txt, 'Name:', ''))
            FROM @txt
            WHERE txt LIKE '%name%'
            )
    WHERE rn = @cntr

    SET @cntr += 1
END

SELECT LogDate, ProcessInfo, txt, HostNameIP, HostName
FROM #DataToProcess

| LogDate                 | ProcessInfo | txt                                                                                                             | HostNameIP    | HostName                |
|-------------------------|-------------|-----------------------------------------------------------------------------------------------------------------|---------------|-------------------------|
| 2019-03-07 14:40:07.320 | Logon       | Login failed for user 'xyz'. Reason: Could not find a login matching the name provided. [CLIENT: 10.165.32.119] | 10.165.32.119 | sc-hgrckb2.anywhere.com |
| 2019-03-07 15:06:51.540 | Logon       | Login failed for user 'zzz'. Reason: Could not find a login matching the name provided. [CLIENT: 10.165.32.119] | 10.165.32.119 | sc-hgrckb2.anywhere.com |