0

when auditing what are the failing logins in one of my server, using the sp_readerrorlog procedure I get the following result, when running the following query:

 EXEC sp_readerrorlog 0, 1, 'Login failed' 

enter image description here

it shows me the IP address of the servers where from the login was attempted.

I know that ping -a resolve ip addresses to hostnames

enter image description here

any ideas, even vague ones, or indications, as how I could get to those hostnames using T-SQL?

lets, say I can get to the ip address and put it into a variable. how then could I go and do a magic and get to the hostname for that particular ip address?

  • 3
    T-SQL isn't the right tool for the job, although you could use xp_cmdshell to invoke NSLOOKUP to resolve the host names. Consider executing the query in a PowerShell script, where you can get the host name using the .NET System.Net.Dns.GetHostEntry() method (e.g. [System.Net.Dns]::GetHostEntry("127.0.0.1"). – Dan Guzman Mar 7 at 12:55
2

As other comments on your question indicated, there are probably better ways other than TSQL to get this data, but here is something I cobbled together using 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 |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.