Network ip

Question

Network ip

ramyours2003

SSChampion

Points: 12266
More actions
November 26, 2012 at 5:27 am

#271156

is any query to find the network IP , OS version etc by using management studio

Viewing 6 posts - 1 through 5 (of 5 total)

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

Brandie Tarvin SSC Guru Points: 172991 More actions · Answer 1

SQL Server doesn't track those things. Therefore, no, there is no T-SQL command capable of getting that information.

However, you could use PowerShell or xp_cmdshell to get out to a command prompt and then query for that information using the usual methods.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Lowell SSC Guru Points: 323504 More actions · Answer 2

in 2008 and above, there is a new function called connection property, and you can get a lot of that information from that.

that can give you both the client ip of the connection as well as the server ip the current connectionis using...it cannot give you other IP's the server listens to...for that you need the permissions to query one of the DMV's: sys.dm_exec_connections .

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

my typical sample query:

--Requires SQL 2008 +

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',

ConnectionProperty('physical_net_transport') AS 'physical_net_transport'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Brandie Tarvin SSC Guru Points: 172991 More actions · Answer 3

Oh, hey! I just learned something new!

Thanks, Lowell.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Lowell SSC Guru Points: 323504 More actions · Answer 4

i was just looking through the SERVERPROPERTY functions, and cannot seem to find anything that shows the operating system.

it is buried in @@VERSION, though, so i'd expect a serverproperty to pull that part out.

the last part of string from SELECT @@VERSION that you can re-translate into something that makes sense to you:

Win7= ...on Windows NT 5.2 (Build 3790: Service Pack 2)

and

Server 2008 = ...on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) i think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Perry Whittle SSC Guru Points: 233821 More actions · Answer 5

you can also use xp_regread to query the apprpriate registry values for your SQL server instance.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉