Friday, March 23, 2012

Have SQL report IP of query-er

I am trying to come up with a way to save the IP of the last computer that ran an update in our servers. I don't want to have to re-write the .net code, so I am wondering if there is a way, using functions available in SQL 2000 that I can get the IP of the machine running the update.

I know that SQL server 'knows' this information because it can log it in it's log files, but I don't know how to get at it from a stored procedure.

Thanks.

DECLARE @.Hostname VARCHAR(255)
SET @.Hostname=host_name()
CREATETABLE #Results
(
Results VARCHAR(4000)
)

DECLARE @.Commandstring VARCHAR(300)
SET @.Commandstring='ping '+ @.Hostname
INSERTINTO #Results
EXEC master..xp_cmdshell @.Commandstring

SelectDISTINCTSUBSTRING(Results,12,CHARINDEX(':',Results)-12)AS HostIpAdressfrom #Results
Where ResultsLIKE'Reply from%'

DROPTABLE #Results

No comments:

Post a Comment