Get Curent Users PC Name / IP Address

Question

Post reply

Get Curent Users PC Name / IP Address

Charlottecb

Hall of Fame

Points: 3932
More actions
January 13, 2006 at 3:13 am

#167123

Hi All,
SQLServer 2000
How do I get the current users PC name and IP Address.
I need this so I can include it in a trigger (for Auditing purposes)
TIA
CCB

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

jordanac Hall of Fame Points: 3653 More actions · Answer 1

try select HOST_NAME()

Don't know how to get IP

Andy.

Charlottecb Hall of Fame Points: 3932 More actions · Answer 2

Hi Andy,

I found that in BOL and it works fine.

Thanks for replying.

Any takers for IP Address?

CCB

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Running the line below gives you various ip config details:

exec master..xp_cmdshell 'ipconfig'

...so you just need to strip out what you want:

declare @ip_row varchar(255)

declare @ip_address varchar(20)

set nocount on

create table #t (detail varchar(255))

insert #t exec master..xp_cmdshell 'ipconfig'

select @ip_row = detail from #t where upper(detail) like '%IP ADDRESS%'

select @ip_address = ltrim(rtrim(substring(@ip_row, charindex (':', @ip_row, 1) + 1, 255)))

drop table #t

set nocount off

select @ip_address

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 4

To run this I needed a minor modification:

create table #t (detail varchar(255) null)

Charlottecb Hall of Fame Points: 3932 More actions · Answer 5

Perfect.

Thanks very much

One final thing, do users need any special permissions to be able to run

exec master..xp_cmdshell 'ipconfig'

Thanks

CCB

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

That will give you the servers IP, not the current user...

For a complete networkaddress, you need two things:

IP + subnetmask or IP + network

192.168.0.1 and 255.255.255.0

or

192.168.0.1/24

An IP address alone isn't enough to identify the unique network, you must have the 'network descriptor' also.

This is especially true if you're dealing with subnetted networks.

On the remote side it's perfectly valid to log, say two connections from 192.168.0.1 that in reality was two different hosts from different subnets. This may or not be an issue for you.

In any case, the only source that reliably knows both these pieces of information, is the client itself.

Thus, the safest way is to have the client provide this info for you.

/Kenneth

Charlottecb Hall of Fame Points: 3932 More actions · Answer 7

Thanks Kenneth, I just realised that.

Getting the client to provide it won't hep when it comes to my Audit Trail Trigger. Any other ideas?

Thanks

CCB

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

Well, then I guess you're stuck.

(though getting the client to provide it *will* solve your problem)

You can't audit information not provided with the connection

Unless the connecting part provides the IP to SQL Server I can't see how it would be possible to log that information.

AFAIK, the only networkid that is reliable (ie it doesn't seem to be filtered out, which can happen with the NETBIOS name (HOST_NAME()) at the serverlevel, is the master..sysprocesses.net_address column, though this contains the MAC address, which is the hardware address at the lower levels of the OSI model.

/Kenneth

RyanRandall SSChampion Points: 13623 More actions · Answer 9

noeld has come up with a solution in this similar thread...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=264524

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 10

Though Noel's solution may work sometimes, I'd really not recommend to try that inside a trigger.

There's also some security considerations to take into account.

/Kenneth

Charlottecb Hall of Fame Points: 3932 More actions · Answer 11

Charlottecb

Hall of Fame

Points: 3932

March 10, 2006 at 1:49 am

#625866

Thanks Ryan

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 12

Charlotte, I just want to be totally clear - Noels soultion is not suitable for a trigger. If you put it in there, each and every transaction you're auditing will at the minimum be the time for the ping to complete.

On my workstation, a successfull xp_cmdshell 'ping myComp' returns in 3 seconds.

A 'Bad IP' message takes 2 seconds to resolve, and should the hostname be an actual host registered in either the WINS or DNS, but the host won't answer pings, so it will time out four times - it takes 6 seconds.

For a trigger, these are huge timespans, and well worth thinking over how that will affect overall performace.

/Kenneth