如何從 sp_readerrorlog 中獲取補充資訊

在審核我的一台伺服器中的失敗登錄時，使用sp_readerrorlog過程，在執行以下查詢時，我得到以下結果：

EXEC sp_readerrorlog 0, 1, 'Login failed' 

它向我顯示了嘗試登錄的伺服器的 IP 地址。

我知道將ping -aIP 地址解析為主機名

任何想法，甚至是模糊的想法或指示，我如何使用 T-SQL 獲取這些主機名？

假設我可以獲取 IP 地址並將其放入變數中。那我怎麼能去魔術並獲得該特定IP地址的主機名？

正如對您問題的其他評論所指出的那樣，除了 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 |

引用自：https://dba.stackexchange.com/questions/231559