Reverse DNS lookup using T-SQL

Question

Post reply

Reverse DNS lookup using T-SQL

dcnorman

SSC Eights!

Points: 971
More actions
February 21, 2005 at 9:53 am

#65449

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

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

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 2

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

dcnorman SSC Eights! Points: 971 More actions · Answer 3

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.

jswong05 Hall of Fame Points: 3553 More actions · Answer 4

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
😛

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

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/

vr Grasshopper Points: 12 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 7

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.

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