Mass DNS Cleanup – Microsoft DNS

Is your company into hosting websites?
Do you manage your own DNS using Microsoft DNS?
Do you have thousands of domains that probably need cleaning?

I wanted to take my time to devise this page to cleaning up Microsoft DNS.

So I am in the business of Hosting and manage our own public facing DNS server for thousands of customers. Naturally an Application guy or a developer would setup the system so DNS is fully automated but not all of us has the luxury of such a beautiful system like that. That’s ok because we infrastructure can handle our own.

Now Long story short, we host a lot of websites, thousands. Some clients host their own dns from Godaddy or some third part DNS hosting services which is fine but may have at one time asked us to host their DNS. Some clients just ask us to host their dns and may have went elsewhere to get their site hosted. In any event we have a DNS server that has over 9 thousand zone records. Of the 9000+ Zones I am most certain that there are tons of domains that we can delete as the domain either expired, the client moved away from hosting from us, etc. There are a number of reasons as to why there are lots of zones that are no longer in use that is still in our DNS server.

That’s where this tutorial comes in play.

Now I want to make myself very clear that this probably isn’t the only way to clean out mass domains but is a way I figured out how to do it by use of text editors, simple batch scripts and a little excel know how. I’m sure other Developers or Application guys developed a way to automate such tasks but since I don’t know how to write code I have to use a little creativity to cleanup my DNS server.

In our case there are some clients who no longer has a site but we host their dns so we can’t just query the database for active domains/Clients and do a lookup against current domains in DNS and kill all the domains that’s not in our active domain/client list from the DB Query.

I also want to mention that this is a time consuming process but will work. There’s a lot of steps so I’ll try to make it clear.

Goal is to delete domains in our DNS server that either doesn’t exist anymore (Domain is no longer registered) and to delete domains where customers no longer point their NS records to our DNS server, meaning they host their DNS elsewhere.

1. Export all DNS Records from DNS server
2. Parse the Export to just domain names
3. Create script to determine whether a domain NS points to us or not.
– This same script will tell us if a domain exists or not by not having a value.
4. Parse the results to where you only have a list of the domains that we can delete.
5. Create a Delete script that deletes the zone in the primary and secondary dns server and also cleans up the .DNS file in %systemroot%\system32\dns
6. Create a restore/revert script in the event something bad happened.

Programs I use for this excercise
Textpad
Microsoft Excel 2010

Step 1

Exporting the zones to textfile:

1. Go to your primary DNS Server. (I just like working on primary)
2. Open Microsoft DNS, click “Forward Lookup Zones” and Export to your Desktop all the zones. I named my file “DNSExport.txt”

– I currently have 9545 Domains in my DNS Server. Wow that’s a lot!

Step 2

Creating an NSLookup Script, set type to NS
1. Use textpad to edit the DNS export of everything but the domain name.
abc.com
def.net
ghi.co
etc.mobi

2. Now you have a list of only the domains. Save this file still as DNSExport.txt. Open a new tab in textpad and copy/paste all the domains to the second tab. Here we will create our Script.
3. On Textpad hit F8 Key to bring up search and replace. Ensure that regular expression is checked.
-On “Find What:” enter “^” that is shift+6. This tell me to replace the very first character of every line with the replacement.
-On “Replace with:” enter “nslookup -querytype=ns ” Make sure that you have a space after the letters ns.
-Click “Replace ALL”
4. Again, On Textpad hit F8 Key to bring up search and replace. Ensure that regular expression is checked.
-On “Find What:” enter “$” that is shift+6. This tell me to replace the last character of every line with the replacement.
-On “Replace with:” enter ” >> DNSQuery1.txt” Make sure that you have a space before the characters >>.
-Click “Replace ALL”
5. Save this file as “QueryNS.bat”

Your script should look like this:
nslookup -querytype=ns Domain1.INFO >> DNSQuery.txt
nslookup -querytype=ns Domain2.net >> DNSQuery.txt
nslookup -querytype=ns Domain3.com >> DNSQuery.txt
nslookup -querytype=ns Domain4.COM >> DNSQuery.txt
and so on….

– Double Click the bat. The results will display on DNSQuery.txt at the location you ran the bat file.

Step 3

Cleanup and Find the domains not needed!

1. After you run NSlookup Script Open Doc using Textpad
2. Mark all instances of the following: To mark hit F5, search for the below and click on “Mark all”
-“internet address =”
-“Address: ”
-“Server: ”
-“DNS request timed out.”
-“timeout was 2 seconds.”
-“IPv6 address =”

3. Click on Edit, Cut Other, Bookmarked Lines
4. Now all you should have in the text editor is a domain followed by the Name Server. It should look like:
Domain1.INFO nameserver = ns2.somenameserver.com
Domain1.INFO nameserver = ns1.somenameserver.com
Domain2.net nameserver = ns2.somenameserver.com
Domain2.net nameserver = ns1.somenameserver.com
5. Copy all and paste to Excel
6. Create Excel Headers “Domain” and “Name Server” – I LIke to Bold and color my headers. you may choose to for better viewing.
7. Highlight the two columns at the header, sort by Domain.
8. Delete all the lines that says (root) ??? unknown type 41 ???
9. Cut all the lines that has a “Domain and Name server” onto the second page. You may delete the first page. This is to make the numbers smaller in excel and to remove excess values.
10. Highlight column A “Domain” and click on “Remove Duplicates on the data tab in excel. It will remove all secondary/Third NS Records as you don’t really need it for this purpose.

Now your left with all the domains with NS Records nicely parsed out.

11. Now create a Filter on the top row.
12. Now find and replace “nameserver = ” with no value to remove those words in the Name server tab.
13. Filter only Blank fields. Since we removed duplicates at the domain level, we need to fill in the blanks of some that didn’t have a second NS Value and repopulate.
14. Your going to need to parse to rerun the NS Script on only these domains with blank fields.
15. Filter out your own Name server, which leaves you to all domains that don’t point to you.

– From this exercise I have just determined that out of the 9545 Domains I have 843 Domains that has their NS Record pointed elsewhere meaning I can delete it without causing any damage as some other dns is handling that domains request.
– From this exercise I also see that I only have 4972 records that has a name server. That tells me that of the 9545 Domains, There are 4573 Domains that don’t even have a name server.

That info tells me that we can technically delete 4573 (non NS Record Domains, probably the domain doesn’t exist anymore) + 843 Domains (NS Pointed elsewhere) = 5416.

— We can technically delete 5416 Domains of the 9545. Holy wow, is this true. That’s a lot of domains that can break!

Now as a test to the theory of this process. out of the two lists of domains you can delete. for the NS not to us, check http://iptools.com to confirm a hand full of domains to validate. For the No Name Servers, do the same or even try to resolve the domain on a web browser. Matter fact go to godaddy to try and register the domain to see if its available…

Don’t worry I got you covered. Now let’s determine those 5416 Domain. We already know off hand the 843 from the previous excercise. Lets grab those domains and put them onto a separate page in excel and label, “NS Not to Us”

16. Now that we copied all 843 Domains to a second page. Lets get back to the first page and and filter all so we see all 4972 domains.
17. Now remember that list we compiled of the export domains from DNS. Lets grab all those domains 9545 domains and page to a separate excel spreadsheet labeled, “export from DNS”
18. Label the first excel page, the one with the parsed domains and Name server tabs “Parsed”.
19. On the third page where you pasted the DNS export lets add a second column named “VLOOKUP”
20. do a vlookup against page 1 to determine what domains do not exist in the parsed list.
=VLOOKUP(A:A,Parced!A:A,1,FALSE)
21. Down highlight the VLOOKUP feild where you see the domain. and pull that down all the way to the final value of the spreadsheet, in my case 9545 lines.
22. Now Put a filter on the headers. Filter by N/A

– Now I see 4579 N/A’s. This likely means that there are 4579 Domains that are probably expired or at the very least does not have a NS Record so is an invalid domain, that should be safe to delete.
-for the sake of keeping things clean, I copied all 4579 domains from the Filter to another page in excel labeled “No Name Servers”

Now we have two tabs that are meaningful.

1. NS Not to US
2. No Name Servers

The domains in these Tabs should be safe to delete but we need to run the NS Lookup script against each list again to double check. The last thing you want is to delete a legit domain.

*If you run the ns script against the “No Name Servers” List you should not see any name servers in the dump.
*If you do the ns script against “NS Not to us” you should confirm that NS is not pointing to you.
*Once you double check everything let’s create the Cleanup final scripts.

This will include:
*Backing up the “To Delete” Domains .DNS File to another directory.
*Deleting the list from Name server 1 and 2.
*Deleting the zones from c:\windows\dns
*We can also create a revert or restore script to put things back the way they were in the event of some failure or mis-research.

Step 4

Creating the Check, Delete and Revert Script

Now that we have two lists of domains we can delete, we can create the scripts to double check work as well as to create our revert all changes script.
1. NS Not to US
2. No Name Servers

I. First thing first let’s start by creating our final check script.
Create a text file, change name to 1-finalcheck-nsquery.bat.
In this batch file we will create the same as the original ns script we did at the top of this post, only for the select domains. We need to repeat this step for “NS Not to Us” and “No Name Servers”.

So in the 1-finalcheck-nsquery.bat let’s put the output of “NS Not to us”
nslookup -querytype=ns Domain1.com >> d:DNSQuery.txt
nslookup -querytype=ns Domain2.com >> d:DNSQuery.txt

We just want to make sure that the NS Record isn’t pointing to us again. Just to make sure. You never know.

II. Next let’s create our backup script. I called mine 2-backupdns.bat.
In this batch file I just copy the list in “NS Not to Us” domains to backup to a directory i created at c:\backup. My script looks like this:
copy %systemroot%\System32\DNS\domain1.com.dns c:\backup
copy %systemroot%\System32\DNS\domain2.com.dns c:\backup

III. Next we create a Delete script for “NS Not to Us”
We will be using Dnscmd.exe to delete domains in the primary and secondary dns servers. Use regular expression how I did in the beginning of this post to edit the commands to the front or back of the domain name like so:
dnscmd.exe primarydnsserverIP /zonedelete domain1.com /f
dnscmd.exe primarydnsserverIP domain2.com /f
dnscmd.exe secondarydnsserverIP /zonedelete domain1.com /f
dnscmd.exe secondarydnsserverIP /zonedelete domain2.com /f

IV. Next we create a script to delete the zone files for the deleted domains. We can use regular expression again to create the script that will look like:
del %systemroot%\System32\DNS\domain1.com.dns
del %systemroot%\System32\DNS\domain2.com.dns

V. Finally we create a revert script. In this script you want to copy the zone files, then load them into primary and secondary dns servers. That’s the high level. It will look something like this:

@ECHO OFF
ECHO.
ECHO.
ECHO RESTORING .DNS ZONE FILES!
ECHO.
copy c:\backup\*.dns %systemroot%\system32\dns /y
ECHO.
ECHO RESTORE OF ZONE FILES COMPLETED!
ECHO.
ECHO.
ECHO Next adding dns from zone file to DNS

dnscmd.exe /zoneadd domain1.com /primary /load /file domain1.com.dns
dnscmd.exe /zoneadd domain2.com /primary /load /file domain2.dns

ECHO Completed Adding Zones from Zone Files
ECHO.
ECHO Next Granting Secondaries Zone Transfer

dnscmd.exe /zoneresetsecondaries domain1.com /securelist SecondaryDNSServerIP
dnscmd.exe /zoneresetsecondaries domain2.com /securelist SecondaryDNSServerIP

###example of SecondaryDNSServerIP is:####
###dnscmd.exe /zoneresetsecondaries domain1.com /securelist 192.168.1.20 (the ip address of the Secondary dns server)####

ECHO Completed Granting Secondaries Zone Transfer
ECHO.
ECHO Next Adding Zones to Secondary

dnscmd.exe SecondaryDNSServerIP /zoneadd domain1.com /secondary PrimaryDNSServerIP
dnscmd.exe SecondaryDNSServerIP /zoneadd domain2.com /secondary PrimaryDNSServerIP

###example of PrimaryDNSServerIP is:####
###dnscmd.exe SecondaryDNSServerIP /zoneadd domain1.com /secondary 192.168.1.10 (the ip address of the Primary dns server)####

ECHO Completed Adding Zones to Secondary
ECHO.
ECHO Restore Completed

That’s pretty much all to it. I know this is complicated and seems overkill but options were limited for me to have an easier solution for this hefty task. Were talking thousands of domains to cleanup…

Hope this helps!