Updating a MySQL Table That's Missing FQDN DNS Information

I had to add a new column to a MySQL table for FQDN DNS data. We already have the IP addresses in the table. This table has nearly 10000 rows, so we need a relatively quick way to do a one off bulk update. This is the method I used, which didn't take long.

1) Acquire id and IP address from the table:

select id,trim(ip) from YOUR_TABLE where ip != '' order by id;

2) Export that to a CSV format.

If you're using MySQL workbench or something similar, it is very straightforward to do this.

3) We then turn to PowerShell and load the CSV:

[System.Array]$IPs = Import-CSV 'IPs.csv'

4) Run this one liner to acquire FQDNs for your IPs:

$IPs | Foreach{ $DNS = Resolve-DnsName $_.ip; IF($DNS){ ($_.id + "," + $_.ip + "," + $DNS.namehost) >> DNSs.csv} }

5) Load this CSV into Excel, and add the following formula into the 4th column:

="UPDATE YOUR_TABLE SET dns = '" &C1& "' WHERE id = "&A1&";"

6) Run the UPDATE commands and job done!