0

I have a table in sql server containing a list of computers in the domain. It has a column called FQDN which contains the names of the computers. I added a column for the IP as a nvarchar (I will format it later). Instead of filling the ip manually I wonder if I can create a function to get them from the dns based on the names in the name column. For example in powershell if I type this [System.Net.Dns]::GetHostAddresses("yourhosthere").ipaddresstostring and store it in a variable I get exactly what I want. I was not able to embedd the above powershell in a function in sql server and then iterate over the name column to update the ip column. I tried of course EXEC xp_cmdshell with powerhell.exe but to no avail.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Dvintila
  • 212
  • 3
  • 13
  • That's probably something that should be done on the client or ETL tool. What you typed isn't Powershell, it's a .NET call. You could use the same line through SQLCLR. Resolving an unkown address can take up to 15 seconds though, which is why it's better to run such code on the client – Panagiotis Kanavos Oct 11 '18 at 11:30
  • I could use this exec master..xp_cmdshell 'nslookup servername'. It works fine but I don't know how to extract the ip from that. I cannot use connectionproperty function because it is not an active connection it's just a string in a table – Dvintila Oct 11 '18 at 11:35
  • To what end do you need to store this IP information? It seems like the sort of thing that could easily be out of date as soon as you've finished. – Damien_The_Unbeliever Oct 11 '18 at 11:42
  • I agree these ip's could change (although they are fixed dns records) The straight answer is that "management" sent a list of column names for the the inventory table :) – Dvintila Oct 11 '18 at 11:47
  • Do you have to do it from the SQL Server side, or can you do a client side script (something like Powershell)? – squillman Oct 11 '18 at 12:41
  • I can do it from a client as long as it updates the table in sql. Unfortunatelly I am not that proficient with powershell as to achieve this. – Dvintila Oct 11 '18 at 13:53
  • And unfortunately there is no functionality available directly in tsql / sql server to do what you want. So pick a language you **are comfortable** in and implement the code to achieve your goal. At this point, your question should be: how to programatically [resolve hostname to ip](https://stackoverflow.com/questions/13248971/resolve-hostname-to-ip) – SMor Oct 11 '18 at 16:01

1 Answers1

0

This will give you the IP address.

 SELECT CONNECTIONPROPERTY('local_net_address') AS local_net_address

Or,

SELECT dec.local_net_address
FROM sys.dm_exec_connections AS dec
WHERE dec.session_id = @@SPID;
AswinRajaram
  • 1,519
  • 7
  • 18
  • I cannot use connectionproperty function because it is not an active connection it's just a string in a table – Dvintila Oct 11 '18 at 11:47