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
----------------------------------------------------------------------------------------------
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
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply