Resolving a list of IP Addresses to Hostnames using Powershell

Following on from my post on Auditing and Summarizing Logon activity on a SQL Server, I figured I need to automate the resolving of IP Addresses to Host names to make the Audit information more useful.

So off I go to Google or MSDN and find [System.Net.Dns]::GetHostbyAddress($IP). Nice! This function can be called from Powershell and does exactly what I need it to do, resolve an IP Address (string) to a hostname.

I created a Powershell Function that wraps this function and adds some rudimentary error handling. I need error handling (who or what doesn't) because the hosts that populates list of IP addresses may not always be available and the host name resolution will fail. Powershell Resolve-IP function code:

Function Resolve-IP ($IP) {

  trap {
    write-host "An error occured: "
    write-host "ID: " $_.ErrorID
    write-host "Message: "$_.Exception.Message
    throw "Couldn't Resolve $IP"
}

$results = [System.Net.Dns]::GetHostbyAddress($IP)
$results
}

Cool, so now we can grab our list of IP Addresses from our AuditLoginHistory table. I do this by using a SQL SMO function called get-sqldata that runs a T-SQL query and returns the results. So my T-SQL Query is:

select distinct host from auditloginhistory

I then use a foreach loop to iterate through the IP Addresses and resolve each IP address to a hostname. The final results, in my case, is exported to a .csv. I'll then Bulk Insert this csv list of IP Addresses and Host names into a SQL table using another SQL SMO function. Then I'll join the Host_IP table to my AuditLoginHistory and display both IP Address and Hostname.

Below is the Powershell script that runs the above:

. ./Function_Resolve-IP.ps1
$hosts = get-sqldata 'sql1' master "select distinct host from auditloginhistory"
foreach ($IP in $hosts) {Resolve-IP $IP.host.tostring()}
$ipex = {foreach ($IP in $hosts) {Resolve-IP $IP.host.tostring()}}
$ipex.invoke() | Select @{N="HostName";E={$_.HostName.tostring()}}, @{N="IP_Address";E={$_.get_AddressList()}} | export-csv "host-ip.csv" -NoTypeInformation