How to search for a domain name based on the IP address in Excel or Powershell?

Question

I have a few IP addresses on excel file, I want to do nslookup. How can I do it on excel or powershell? Any ideas or recommendations. Don't confuse yourself with vlookup. I am asking for nslookup in command prompt. Is there any way to look for every single ip address from each row and find the domain. If there is a way of doing it using VBA in excel, that will be great.

See the attached to clarify the requirement. Feel free to ask me any questions.

Answer 1

Try this in powershell

Import-Csv "Csvpath" | ForEach {
    $IPAddress = $_
    $hostname = ([System.Net.DNS]::GetHostByAddress($_)).hostname
    Write-Host "$($IPAddress) $($Hostname)"
}

Answer 2

This is for PowerShell.

If you save the data in the Excel file as CSV or in a text file where each IP address is listed on its own line, the following should work.

The below code assumes a text file with IP addresses:

$result = Get-Content -Path 'D:\IpAddresses.txt' | ForEach-Object {
    $dnsRecord = Resolve-DnsName -Name $_ -ErrorAction SilentlyContinue
    $hostName  = if ($dnsRecord) { $dnsRecord.NameHost } else { "Not found" }
    [PsCustomObject]@{
        'IPAddress' = $_
        'Domain'    = $hostName
    }
}

# output on console
$result

# write results to new CSV file to easily open in Excel
$result | Export-Csv -Path 'D:\IP_Domain.csv' -UseCulture -NoTypeInformation

Output:

IPAddress      Domain                                       
---------      ------                                       
208.97.177.124 apache2-argon.william-floyd.dreamhost.com    
208.97.177.125 apache2-linus.john-hart.dreamhost.com        
208.97.177.126 apache2-daisy.charles-carroll.dreamhost.com  
208.97.177.127 apache2-igloo.benjamin-harrison.dreamhost.com
208.97.177.128 apache2-emu.charles-carroll.dreamhost.com    
208.97.177.129 apache2-rank.thomas-heyward-jr.dreamhost.com 
208.97.177.130 apache2-udder.arthur-middleton.dreamhost.com 
208.97.177.131 apache2-bongo.william-ellery.dreamhost.com   
208.97.177.132 apache2-bongo.william-hooper.dreamhost.com

---

If you save the Excel file as CSV (which is MUCH easier to handle in PowerShell than an Excel file) something like this:

IpAddress
208.97.177.124
208.97.177.125
208.97.177.126
208.97.177.127
208.97.177.128
208.97.177.129
208.97.177.130
208.97.177.131
208.97.177.132

Then all you need to change in the code is the top line into:

# read the CSV file and select an array of the IP addresses listen in column 'IpAddress'
$result = Import-Csv -Path 'D:\IpAddresses.csv' | Select-Object -ExpandProperty IpAddress | ForEach-Object {
    # rest of the code
}

Or shorter:

$result = (Import-Csv -Path 'D:\IpAddresses.csv').IpAddress | ForEach-Object {
    # rest of the code
}

---

You can of course also read directly from Excel, but that takes a lot more doing:

# open the Excel file and read the values into an array:
$file      = 'D:\test.xlsx'
$row       = 1               # the row number you want to read. Numbering starts at 1

# create an Excel COM object and open the file
$excel         = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook      = $excel.Workbooks.Open($file)
$sheet         = $workbook.Worksheets(1).Activate

# get the total number of columns in the sheet
$colCount = ($sheet.UsedRange.Columns).Count
# collect the cell values in a loop
$addresses = for ($col = 1; $col -le $colCount; $col++) {
    $sheet.Cells.Item($row, $col).Text
}

# close excel and clean up the used COM objects
$excel.Quit()
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

Then loop through the addresses using

$result = $addresses | ForEach-Object {
    # rest of the code
}