get iis server address when inserting data in sql sever 2005

Question

Hello,

When a user on my site registers and his data is inserted into a table on sql server 2005 I need to know from which iis server the user arrived - not the users ip address. (there are 2 different server addresses under a server in iis from which the data could have been received). I then need to get the address and insert it into another table. How can I receive this address?

Any help would be greatly appreciated. Thanks!

Answer 1

Use the HOST_NAME() function

EDIT: OP clarified that he wants IIS server IP address.

Can you resolve the name to an IP using a lookup table? Presumably the IIS servers don't change that dynamically?

EDIT: some code

If you've got xp_cmdshell enabled you could try something along the lines of

declare @hostip varchar(50)
declare @hostname varchar(50)
declare @cmd varchar(8000) 

set @hostname = HOST_NAME()

set @cmd = 'ping -n 1 ' + @hostname

create table #temp (pingoutput varchar(max)) 

insert into #temp exec master.dbo.xp_cmdshell @cmd 
select @hostip = substring(pingoutput,  charindex('[',pingoutput)+1, charindex(']',pingoutput)-charindex('[',pingoutput)-1) 
from #temp
where left(pingoutput,7) = 'Pinging' 

select @hostip 

drop table #temp

replace the @hostname with whatever you have to find the IP of a given name, or use as-is to get the host ip

---

EDIT: some further thoughts

The 'ping' method to get the IP address of HOST_NAME() may not work if your web servers are in a DMZ

If it is IIS web logs that you are trying to import, are you aware of the [s-ip] field you can add to the W3C extended log format. This would add the web server ip to the log entries.