How to get machine name from ip address in SQL Server 2008?

Question

I have to get the machine name from IP address in SQL Server, is there anything that I could do to accomplish my task

Answer 1

DB:

IPAdd | MachineName

Query:

select MachineName from DBTable where IPAdd='yourIPAddress'

Try with:

SELECT SERVERPROPERTY('MachineName')

Or

SELECT  
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address 

Or

SELECT @@SERVERNAME;

Refer:

http://msdn.microsoft.com/en-us/library/ms187944.aspx

Hope its helpful.

Answer 2

I might be wrong but as far as I know, SQL Server can not do what you want. Resolving machine names from IP address is something your network's DNS server does.

Best idea I come up with to get that info from SQL Server is using xp_cmdshell command to execute command prompt commands from SQL.

xp_cmdshell 'NBTSTAT -A 10.10.10.10'

However, please note that xp_cmdshell needs to be enabled on your server first in order to work, and often it is not beacause of possible security issues. Read more about xp_cmdshell at http://msdn.microsoft.com/en-us/library/ms175046.aspx

Also, result from this will be just like from command prompt and it will require some parsing to get exact machine name from it. Check this topic for more info: Get Results from XP_CMDSHELL

I don't know your solution architecture and how you get the IP address, but if it is from some client side applications, it might be easier to find client's machine name executing SELECT HOST_NAME() from client's side query or with .NET and sending machine names directly.

Answer 3

You can't do this natively in the SQL language, your best bet is to either:

1/ shell out via xp-cmdshell and run nslookup, which will require some string manipulation to get the command right and then some cleaning up of the output to return the machine name

2/ Write a C# CLR function that takes the IP address as an input and makes use of the Dns.GetHostEntry method to resolve and return the name.

See here for the documentation:

Dns.GetHostEntry Method

I wrote a really quick simple CLR function for getting the machine name from the IP Address is below, please note there is no error handling or input checking to make sure the IP is valid before trying to resolve it, and it won't give you the IP address if you pass it a hostname, but it can be easily modified to include all these things, it's just to give you an idea of how it could work:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString dnsResolve(String ipAddress)
    {
        IPHostEntry host = Dns.GetHostEntry(ipAddress);
        return new SqlString(host.HostName.ToString());
    }
}

Answer 4

What you want is called a Reverse DNS lookup. There are command line tools that can do it (nslookup.exe) or you can use the DnsQuery API. You have no business doing either from T-SQL so don't try to do it. Resolve the IP to name in the client.

Answer 5

You can try this to get the name of your machine.

SELECT SERVERPROPERTY(N'MachineName');

May be above can serve your purpose.

Answer 6

--Im using command line for checking host name after IP
declare @IP as varchar(15)
declare @cmd as varchar(1000) 
set @IP='192.168.0.1' 
SET @cmd = 'ping -a -n 1 ' + @IP 
Create Table #Output (Output varchar(150) default(''))
INSERT INTO #Output 
EXEC xp_cmdshell @cmd 
Begin try 
Select top 1 Replace(LEFT([Output],CHARINDEX('[', [output])-2),'Pinging ','') as HostName from #Output where Output is not null 
End Try 
Begin catch 
Select 'Host name for:' + @IP +' could not be find'
End catch 
drop table #Output 

Answer 7

This actually works: Modified answer from Sky Diver

--Im using command line for checking host name after IP
    declare @IP as varchar(15)
    declare @cmd as varchar(1000) 
    set @IP='137.201.17.204' 
    SET @cmd = 'ping -a -n 1 ' + @IP 
    Create Table #Output (Output varchar(150) default(''))
    INSERT INTO #Output 
    EXEC xp_cmdshell @cmd 
    Begin try 
    Select top 1 Replace(LEFT([Output],CHARINDEX('[', [Output])-2),'Pinging ','') as HostName from #Output where Output like 'Pinging%'
    End Try 
    Begin catch 
    Select 'Host name for:' + @IP +' could not be find'
    End catch 
    --drop table #Output 

Answer 8

Try this you will get a system Name.......

select HOST_NAME()

regards : Rajsri Vyshnnavi