13

Morning All,

I've been handed a domain with c.150k DNS records in it for a total of 100k machines. The DNS records are all in a single forward lookup zone on the same domain and just to be helpful, there is no reverse lookup zone.

Since Scavenging and Ageing is not an option (just my luck!), I need to find an easy way to kill off a load of stale records.

Simplest starting point for me was to look to delete all records which have a timestamp older than a year (roughly 2700 records) - but some of them are still responding to ping.

Odds are this is because we use DHCP and the IPs have since been given to new hosts.

Normally a reverse lookup zoen would come in real handy about now so that I could look for multiple registrations for an IP, but as i said before - there isn't one.

Anyone know off the top of your heads a simple way to look for duplicates? I have considered a full export to CSV from DNS and start sorting & filtering, but for over 2k records, I was hoping for something a little easier.

Also, due to the crappy nature of some of the kit we run, just deleting the records and allowing a re-registration to happen is not an option!

Thoughts ?

EDIT: not all machines are AD joined, so an AD/DNS comparisson is out of the question.

EDIT 2: Upvotes all round, some really good suggestions here! I've already started work on exports and comapres in Excel, have a ping fo each IP/Host in progress logging to a test file to see what is and isnt real and am waiting on change approval for a packet capture to start realtime monitoring. Those scripts are also useful, and while I am not going to use them in their current format, they have given me a good basis for wiritng my own stuff. Thanks everyone!

8

If you can export as a CSV then import in to Excel then you can use the conditional formatting to generate a list of duplicates?

http://www.tech-recipes.com/rx/35290/excel-2013-find-duplicate-data-using-conditional-formatting/

  • 4
    Please do not use links for solutions. Providing the solution in the answer maintains the answer better because links can go bad. – Paul Dec 31 '14 at 14:40
  • 3
    To be clear about what @Paul says, of course links are wanted, but not only links. So keep the link in the answer, but add what is written on its target. – glglgl Jan 1 '15 at 8:05
  • Fair enough! Shall do in future. – Aceth Jan 5 '15 at 16:07
6

I would use nmap to verify all your alive hosts which should clear out the rest of the dead records for you. I would strongly recommend scavenging from this point forward though. Sounds like fun though :)

  • This would work if the hosts are always-on. If the machines are also laptops then some (or a lot, depends on the activity) may get lost. – WoJ Jan 1 '15 at 21:21
4

Here are two scripts (which are almost identical) - one to find duplicate IP and one to find duplicate names.

#region Find duplicate name in dns records
$DupNames = Get-WmiObject -ComputerName 'dc2008' `
   -Namespace 'root\MicrosoftDNS' `
   -Class MicrosoftDNS_AType `
   -Filter "ContainerName='yadsarah.org.il'" | Select-Object RecordData, TimeStamp, OwnerName | `
Group-Object OwnerName | Where-Object {$_.Count -gt 1}

$DupNamesArr = @()
foreach ($RecordGroup in $DupNames) {
    foreach ($Record in $RecordGroup.Group) {
        $RecordObj = New-Object System.Object
        $RecordObj | Add-Member -MemberType NoteProperty -Name 'Name' -Value $Record.OwnerName
        $RecordObj | Add-Member -MemberType NoteProperty -Name 'IPAddress' -Value $Record.RecordData
        $RecordObj | Add-Member -MemberType NoteProperty -Name 'TimeStamp' -Value $Record.TimeStamp
        $DupNamesArr += $RecordObj
    }
}
#endregion

#region Find duplicate IP in dns records
$DupIPs = Get-WmiObject -ComputerName 'dc2008' `
   -Namespace 'root\MicrosoftDNS' `
   -Class MicrosoftDNS_AType `
   -Filter "ContainerName='yadsarah.org.il'" | Select-Object RecordData, TimeStamp, OwnerName | `
Group-Object RecordData | ?{$_.Count -gt 1}

$DupIPArr = @()
foreach ($RecordGroup in $DupIPs) {
    foreach ($Record in $RecordGroup.Group) {

        $RecordObj = New-Object System.Object
        $RecordObj | Add-Member -MemberType NoteProperty -Name 'Name' -Value $Record.OwnerName
        $RecordObj | Add-Member -MemberType NoteProperty -Name 'IPAddress' -Value $Record.RecordData
        $RecordObj | Add-Member -MemberType NoteProperty -Name 'TimeStamp' -Value $Record.TimeStamp
        $DupIPArr += $RecordObj
    }
}
#endregion

Notes:

  1. Some systems (like load balancing) require some duplicate records in order to work properly.

  2. You can add the computer name and IP using WMI, thus verifying the record.

  3. Records with timestamp 0 are static, meaning they were added manually (and are more likely to be important).

2

Maybe you can log all real-life DNS requests for a sufficiently long period of time (this may in fact be several months if you want to catch that once-per-quarter-query-all-printer-page-statistics script). Then delete all records never requested for.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.