# Updating AD DNS Records Dynamically from Excel Sheet

We have a Microsoft SBS 2011 server at work which handles DHCP and DNS for our company. We have an excel spreadsheet stored in a shared directory on the SBS itself of all the public IP addresses that we own. That excel document is updated constantly by us by hand. It would be nice if we could dynamically update our DNS server with short names for those IP addresses so that when someone using any machine on our domain pings, or tries to connect to one of those public IPs, all they need to write out is a short name instead of the entire IP.

Currently, I had Linux go through that excel document once using a bash loop, and generate a windows hosts file that people could copy manually into C:\Windows\System32\Drivers\etc\hosts. This worked great, but this would need to be copy pasted onto every machine in the domain, and it would not be dynamic - pretty much the reason DNS was invented.

Is there any way to have either our SBS, or maybe even Linux constantly go through the excel file and update the SBS AD DNS server with some simple records that point to public IPs? An example of a mapping might be:

123456r 196.16.56.56
123458r 196.65.89.56


I believe those are A records we need. It would be extremely preferable if we did not need to append any kind of domain name to those entries and just have them be as simple as those examples. What's a good way to accomplish this?

## 1 Answer

You could do it with powershell but you would need to change the file type to csv

Take a look here. https://gallery.technet.microsoft.com/scriptcenter/Update-DNS-records-with-da10910d and you will find the following powershell script, just set it as a regular scheduled task or something users can run themselves. I used this recently to do a mass update of ips spread across many zones and it worked well.

    # Environment Setup
$DNSServer = "YourDNSServer"$DNSZone = "YourZoneName"
$InputFile = "dnsrecords.csv" # Read the input file which is formatted as name,type,address with a header row$records = Import-CSV $InputFile # Now we loop through the file to delete and re-create records # DNSCMD does not have a modify option so we must use /RecordDelete first followed by a /RecordAdd ForEach ($record in $records) { # Capture the record contents as variables$recordName = $record.name$recordType = $record.type$recordAddress = $record.address # Build our DNSCMD DELETE command syntax$cmdDelete = "dnscmd $DNSServer /RecordDelete$DNSZone $recordName$recordType /f"

# Build our DNSCMD ADD command syntax
$cmdAdd = "dnscmd$DNSServer /RecordAdd $DNSZone$recordName $recordType$recordAddress"

# Now we execute the command
Write-Host "Running the following command: $cmdDelete" Invoke-Expression$cmdDelete

Write-Host "Running the following command: $cmdAdd" Invoke-Expression$cmdAdd
}