Automation

 View Only
  • 1.  Find FQDNs for hostnames from a list of servers imported from csv

    Posted 12 days ago

    Hello,

    I have a spreadsheet with a large number of hosts and IP addresses for which I need to find their FQDN's. Some of them may not even be valid IP addresses, some have no DNS A records associated with them.

    I'm sure this has been done before, so if there's a better approach by all means let me know, but I was planning to ping each IP address, then I'll have to go back and update the hostname in the original spreadsheet.

    I got started with something like this:

    $VMs = Import-Csv -Path "C:\output\FQDN_Sorted.csv"
    $attempt = @()
    
    foreach ($VM in $VMs) {
        $result = ping -a $vm."IP Address"
        }
        if ($result -contains "Reply") {
            Write-Host "it triggered"
            $attempt += [PSCustomObject] @{
                        Name = $result
            }
        Write-Host "$result"
    }

    However, even if $result contains reply example:

    Pinging rq-in-f94.1e100.net [142.250.115.94] with 32 bytes of data:
    Reply from 142.250.115.94: bytes=32 time=59ms TTL=105
    Reply from 142.250.115.94: bytes=32 time=60ms TTL=105
    Reply from 142.250.115.94: bytes=32 time=86ms TTL=105
    Reply from 142.250.115.94: bytes=32 time=59ms TTL=105

    It doesn't seem to trigger the if ($result -contains "Reply")

    My questions are:

    1. How can I select only the FQDN name from the successful ping replies?
    2. Is there a better way to do this I'm not seeing?






  • 2.  RE: Find FQDNs for hostnames from a list of servers imported from csv
    Best Answer

    Posted 11 days ago

    Here is a simple update based on what you have

    The -n 1 for the ping command will speed up the ping results and overall report

    For the FQDN, you just have to split the results and grab what you want for the FQDN.

    $PingReport = @()
    foreach ($VM in $VMs) {
        $result = ping -a $vm.name -n 1
        $FQDN = $Result.split()[2]
       
        if ($result -Match "Reply") {
            Write-Host "Pass"
            $PingReport += [pscustomobject] @{
                Ping = "Success";
                FQDN = $FQDN;
            }
        }
        Else {              
            Write-Host "Fail"
            $PingReport += [pscustomobject] @{
                Ping = "Fail";
                FQDN = $VM.WhateverFromYourInput;
            }
        }
    }



  • 3.  RE: Find FQDNs for hostnames from a list of servers imported from csv

    Posted 11 days ago

    Oh, and use -match.  -match will also work with regular expresssions but in your case -Match "Reply" works fine.  Also, make sure to update this to whatever matches your input. $VM.WhateverFromYourInput;




  • 4.  RE: Find FQDNs for hostnames from a list of servers imported from csv

    Posted 11 days ago

    Apologies, one last thing.  I was using Get-vm | select -first 10 for my $VMs variable.  It looks like you'll need to trim .name in $VM.name on the third line.




  • 5.  RE: Find FQDNs for hostnames from a list of servers imported from csv

    Posted 11 days ago

    Hi.

    "-contains" is used for checking array elements, not for checking a substring within a string (as in your case). If you change your "-contains" to "-match" it should work. "-match" works as a regular expression.

    Use this instead:

    If ($result -match "Reply") {$result}

    Regards.




  • 6.  RE: Find FQDNs for hostnames from a list of servers imported from csv

    Posted 11 days ago

    This is great, thanks for your help. With your help I modified your script slightly:

    $VMs = Import-Csv -Path "C:\output\FQDN_resolution\FQDN_input_test.csv"
    $PingReport = @()
    foreach ($VM in $VMs) {
        $result = ping -a $vm."IP Address" -n 1
        $FQDN = $Result.split()[2]  
        if ($result -Match "Reply") {
            Write-Host "Pass"
            $PingReport += [pscustomobject] @{
                Ping = "Success";
                IP = $VM."IP Address";
                FQDN = $FQDN;
            }
        }
        Else {              
            Write-Host "Fail"
            $PingReport += [pscustomobject] @{
                Ping = "Fail";
                IP = $VM."IP Address";
                FQDN = $VM."Server CI Related";
            }
        }
    } 

    The end result gives me output like this:

    Ping IP FQDN
    Fail 192.168.0.123 justhostname
    Success 192.168.197.215 192.168.197.215
    Success 192.168.196.159 192.168.196.159
    Success 192.168.196.68 server1.domain.com
    Success 192.168.197.16 server2.domain.com
    Success 192.168.197.58 server3.domain.com

    This will be extremely helpful, however as you can see from above, many of the "successful" results don't actually have A records associated, so I end up getting the IP address rather than the FQDN. Can a check be added for a numeric result in order to exclude these? Beyond my ability, but I'm changing the if statement to check for both "reply" and -ne numeric? 

    if ($result -Match "Reply" -and $result -ne "??numeric??"") {
            Write-Host "Pass"
            $PingReport += [pscustomobject] @{
                Ping = "Success";
                IP = $VM."IP Address";
                FQDN = $FQDN;
            }







  • 7.  RE: Find FQDNs for hostnames from a list of servers imported from csv

    Posted 11 days ago

    It took some digging, but this version looks perfect, it's only marking 'success' when it finds the full FQDN and is ignoring successful pings which are only returning IP addresses!

    $VMs = Import-Csv -Path "C:\output\FQDN_resolution\FQDN_input_test.csv"
    $PingReport = @()
    foreach ($VM in $VMs) {
        $result = ping -a $vm."IP Address" -n 1
        $FQDN = $Result.split()[2]  
        if ($result -Match "Reply" -and $Result.split()[2]  -notmatch "^[\d\.]+$" ) {
            Write-Host "Pass"
            $PingReport += [pscustomobject] @{
                Ping = "Success";
                IP = $VM."IP Address";
                Original = $VM."Server CI Related";
                FQDN = $FQDN;
            }
        }
        Else {              
            Write-Host "Fail"
            $PingReport += [pscustomobject] @{
                Ping = "Fail";
                IP = $VM."IP Address";
                Original = $VM."Server CI Related";
                FQDN = $VM."Server CI Related";
            }
        }
    } 
    $PingReport | Export-Csv -Path "C:\output\FQDN_resolution\FQDN_output.csv" -NoTypeInformation



  • 8.  RE: Find FQDNs for hostnames from a list of servers imported from csv

    Posted 11 days ago
    Edited by LucD 11 days ago

    Hi James,

    A couple of observations. As others have mentioned, the -Match conditional should return what you're looking for.

    Also, you may want to avoid using the += as this increases processing time when the list of VMs gets large. Instead, look at the sample below.

    Finally, I like to test using an internal table, which I then convert to a .CSV file.

    Hope this helps.

    # Sample .csv internal content using PSCustomObject

    # This is useful during tests

    $data = @(

        [PSCustomObject]@{FQDN="dns.google";      IP="8.8.8.8"}        # Should work

        [PSCustomObject]@{FQDN="one.one.one.one"; IP="1.1.1.1"}        # Will return general failure

        [PSCustomObject]@{FQDN="dns.quad9.net";   IP="9.9.9.9"}        # Should work

        [PSCustomObject]@{FQDN="www.doofus.com";  IP="74.208.175.225"} # Should time out

    )

     

    # .csv Path

    $CsvPath = ".\FQDN_Sorted.csv"

    $CsvRept = ".\Report.csv"

     

    # Export table to external .csv

    $data | ConvertTo-Csv -NoTypeInformation | Set-Content $CsvPath

     

    # Process .csv

    $report = Import-Csv -Path $CsvPath |

        ForEach-Object {

            $result = ping -a -n 1 $_.IP

            If ($result -Match "Reply") {

                $result = "Success"

            } else {

                $result = "Fail"

            }

            [PSCustomObject] @{

                Result = $result

                VM     = $_.FQDN

                IP     = $_.IP

            }

          }

    $report | Export-Csv -Path $CsvRept -NoTypeInformation   

    Finally, instead of using ping, you may want to look at the Test-Connection PS statement. This returns True or False.

    # Using PS test connection
    $report = Import-Csv -Path $CsvPath |
        ForEach-Object {
            If (Test-Connection -ComputerName $_.IP -Count 1 -Quiet) {
                $result = "Success"
            } else {$result = "Fail"}
            [PSCustomObject] @{
                Result = $result
                VM     = $_.FQDN
                IP     = $_.IP
            }
          }

    This makes the code a lot more compact and in my opinion, more elegant.

    Cheers.