Powershell script to read and validate Windows DNS servers

If you've ever had to administer a Windows DNS server you likely know that there are limited tools for cleanup.  I wrote this script to read the entire list of entries from a specific domain in DNS and then ping each address to see if it was live or not.  That way I can easily go back and investigate the ones are show up as dead.  It writes it all to an Excel spreadsheet.

Watch for word-wrapping...

#======================================================================================

#         File Name : DNS-Validator.ps1

#   Original Author : Kenneth C. Mazie (kcmjr AT kcmjr.com)

#                   :

#       Description : Used to read entries from Windows DNS and ping check each.

#                   :

#             Notes : Set the name of your DNS server and domain within the script.

#                   : Execute with a user ID with domain admin permissions.

#                   : Creates an Excel spreadsheet and populates it with IP addresses

#                   : and resolved names from DNS, then pings each.  Also

#                   : lists a reverse count of the total number of entries just because.

#                   :

#          Warnings : None

#                   :  

#             Legal : Public Domain. Modify and redistribute freely. No rights reserved.

#                   : SCRIPT PROVIDED "AS IS" WITHOUT WARRANTIES OR GUARANTEES OF

#                   : ANY KIND. USE AT YOUR OWN RISK. NO TECHNICAL SUPPORT PROVIDED.

#                   :

#           Credits : Code snippets and/or ideas came from many sources including but

#                   :   not limited to the following:

#                   :

#    Last Update by : Kenneth C. Mazie

#   Version History : v1.0 - 06-10-14 - Original

#    Change History : v1.1 -

#                   :

#=======================================================================================

clear-host

$ErrorActionPreference="silentlycontinue"

$DNS_Server = "my-dns-server"

$DomainName = "my-dns-domain"

$excel = New-Object -ComObject Excel.Application

$excel.Visible = $true

$workbook = $excel.Workbooks.Add()

$sheet = $workbook.ActiveSheet

$counter = 0

$dns_entries = Get-WmiObject -namespace "root\MicrosoftDNS" -class MicrosoftDNS_Atype -ComputerName $DNS_server -Filter $DomainName

$count = $dns_entries.count

$column = 1

$range = $Sheet.Range("a1","d1")

$range.Style = 'Title'

$range.font.bold = $True

$range.Interior.ColorIndex = 24

$sheet.cells.Item(1,$column++) = "IP Address"

$sheet.cells.Item(1,$column++) = "Hostname"

$sheet.cells.Item(1,$column++) = "Ping Result"

$sheet.cells.Item(1,$column++) = "Count"

$row=2

foreach ($dnsrecord in $dns_entries) {

  $Column = 1   

   $sheet.cells.Item($row,$column).activate()  

      $sheet.cells.Item($row,$column++) = $dnsrecord.IPAddress

      $sheet.cells.Item($row,$column++) = $dnsrecord.OwnerName.split(".")[0] 

     

      if (Test-connection -Count 1 -ComputerName $dnsrecord.IPAddress){

        $sheet.cells.item($row,$column).font.ColorIndex = 10

      $sheet.cells.Item($row,$column++) = "Successful"

      }else {

        $sheet.cells.item($row,$column).font.ColorIndex = 3

        $sheet.cells.item($row,$column).font.bold = $true

        $sheet.cells.Item($row,$column) = "FAILED"

        $column++

        $sheet.cells.item($row,$column).font.bold = $false

      }

      $sheet.cells.Item($row,$column++) = $count

     

     

      $WorkSheet = $Workbook.worksheets.Item(1)

    $UsedRange = $WorkSheet.UsedRange

    $UsedRange.EntireColumn.AutoFit() | Out-Null

    $dataRange = $Sheet.Range(("A{0}" -f $row),("D{0}" -f $row))

    $UsedCols = $UsedRange.Columns.Count 

    1..$UsedCols | ForEach {

      $dataRange.Borders.Item($_).LineStyle = 1

      $dataRange.Borders.Item($_).Weight = 12

    }

      $count --

      $row ++

}

$workbook.SaveAs("C:\DNS-Validator.xlsx")

$excel.Quit()

Write-Host COMPLETED