Resolve Hostnames with T-Sql

Resolve hostnames with t-sql

Well, I suppose you could use xp_cmdshell to execute nslookup and parse the results. Seems like a really awkward thing for SQL Server to be doing, though.

exec master..xp_cmdshell 'nslookup intel.com'

.. then you'll probably want to stuff that in a temp table and walk through the results.

You could also, if you can get access to SQL Server 2005 or 2008, build a stored procedure or function in .NET and do a simple call to Dns.GetHostAddresses().

SQL Server Query : Host name

There is not any table with historical information about the host that executed a query - http://www.sqlservercentral.com/Forums/Topic1334479-146-1.aspx

Is it possible to get hostnames of computers connected to MS SQL database?

First, the PowerShell code you posted returns ALL database users, not just the ones that are currently connected.

In order to get the host and login info for current processes use this code:

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
$Sql = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “DEVSQL”
$sql.EnumProcesses() | Where_Object {$_.Database -eq "TestDB"} | Select host,login

Resolve HostName to IP

You can simply use the DNS class to do so:

IPHostEntry hostEntry;

hostEntry= Dns.GetHostEntry(host);

//you might get more than one ip for a hostname since 
//DNS supports more than one record

if (hostEntry.AddressList.Length > 0)
{
      var ip = hostEntry.AddressList[0];
      Socket s = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.IP);
      s.Connect(ip, 80);
}

Check ip and dns name

HOST_NAME() returns the client's computer name.

This code resolves its ip address using ping and xp_cmdshell:

set nocount on
declare @ip varchar(255), @cmd varchar(100) 
set @cmd = 'ping -n 1 ' + HOST_NAME() 
create table #temptb (grabfield varchar(255)) 
insert into #temptb exec master.dbo.xp_cmdshell @cmd 
select @ip = substring(grabfield,  charindex('[',grabfield)+1,
 charindex(']',grabfield)-charindex('[',grabfield)-1) from #temptb  where left(grabfield,7) = 'Pinging' 
print @ip 
drop table #temptb
set nocount off 

Source: http://www.sqlservercentral.com/Forums/Topic150196-8-1.aspx

---

Related Topics