Reverse DNS lookup using T-SQL

  • Hello, I have a stored procedure that is passed an IP address that I would like to do a reverse DNS lookup on to get the machine name. Has anyone ever done anything like this, and if so, how?

    Thanks

  • Here is an example using Ping:

    create procedure usp_ReturnNameFromIP @IP varchar(15),@Name varchar(100) output

    as

     

    set nocount on

    create table #ping (line varchar(1000))

    declare @answer varchar(1000)

    declare @command varchar(1000)

    declare @pointer int

    set @command = 'ping -a ' + @ip

    insert into  #ping exec  master.dbo.xp_cmdshell @command

    select @answer = line from #ping where line like 'Pinging%'

    set @pointer = charindex('[',@answer)

    set @answer = left(@answer,@pointer -1)

    set @answer = rtrim(ltrim(replace(@answer,'Pinging ','')))

    set @Name = @answer

    drop table #ping

    return

    Call it like this:

    declare @Name varchar(100)
    exec usp_ReturnNameFromIP '192.168.0.5',@Name OUTPUT
    print @Name

     

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • nslookup run from a command prompt will allow you to do the reverse lookup.

  • Thank you both for your responses. I ended up using nslookup, as it runs much faster than ping does (doesn't have to wait for packet responses). Kathi I really appreciate the code, it was helpful in getting this to work.

    Thanks again.

  • CLR is more elegant.

    Regards,

    Jason

    http://dbace.us

    ----------------------------------------------------------------------------------------------

    using System.Net;

    using System.Security;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

    public class SQLSPGetHostName

    {

    // static string whatipaddress

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void GetHostNameFromIPAddress(string whatipaddress)

    {

    string wantipaddress = whatipaddress;

    IPHostEntry IpEntry = Dns.GetHostEntry(wantipaddress);

    // IPHostEntry IpEntry = Dns.Resolve(wantipaddress);

    SqlContext.Pipe.Send(IpEntry.HostName.ToString());

    }

    [Microsoft.SqlServer.Server.SqlFunction()]

    public static string fn_GetHostname(string whatipaddress)

    {

    string wantipaddress = whatipaddress;

    try

    {

    IPHostEntry IpEntry = Dns.GetHostEntry(wantipaddress);

    // IPHostEntry IpEntry = Dns.Resolve(wantipaddress);

    return IpEntry.HostName.ToString();

    }

    catch

    {

    return whatipaddress;

    }

    }

    }

    Jason
    http://dbace.us
    😛

  • jswong05 (5/9/2012)


    CLR is more elegant.

    Regards,

    Jason

    http://dbace.us

    This thread is in the sql 2000 section. Can't use CLR. Not to mention this thread is 7 years old. 😉

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Looking for any easy way to do same inside sql, maybe any new way appeared through the years?

  • vr wrote:

    Looking for any easy way to do same inside sql, maybe any new way appeared through the years?

    Can you use xp_CmdShell?  I ask because that's still the easiest way whether you end up using "ping" or "nslookup".  And, no, xp_CmdShell is NOT the big, bad security violation that many make it out to be if used correctly. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply