Export DNS records to Excel to read time stamps and static records

Export DNS records to Excel to read time stamps and static records

  • Comments 14
  • Likes

Ask a DNS administrator and he’ll tell you there is no such thing as being “too careful” with DNS data! One of the dreaded things is to check the box for Auto Scavenging. A slight mis-configuration can lead to useful DNS entries getting deleted.

Some of the common questions that may come to an Administrator’s mind when thinking about scavenging is – How many static records do I have? Do I really have aged records lingering? Well, the answers to these questions are easy to find. Just open each record in the DNS console and look at the time stamp. This is easy if you have 20 records. That’s far from practical in the real world, though.

What one really needs is data in an organized form, say in Excel. Unfortunately the format of “dnscmd enumrecords” is not exactly ready to be imported as data. Let’s look at a sample output of “dnscmd /enumrecords contoso.com @ /Type A /additional”:

Returned records:
@ [Aging:3570365] 600 A    192.168.0.3
         [Aging:3570365] 600 A    192.168.0.1
         [Aging:3570365] 600 A    192.168.0.4
         [Aging:3570365] 600 A    192.168.0.2
2K-A [Aging:3558828] 1200 A    192.168.0.14
clusdfs [Aging:3570365] 1200 A    192.168.0.31
cluster [Aging:3570365] 1200 A    192.168.0.30
contoso-dca [Aging:3570521] 3600 A    192.168.0.1
CONTOSO-DCB [Aging:3570521] 3600 A    192.168.0.2
CONTOSO-DCC [Aging:3570413] 1200 A    192.168.0.3
CONTOSO-DCD [Aging:3570394] 1200 A    192.168.0.4
R2-A [Aging:3570365] 1200 A    192.168.0.11
R2-B [Aging:3570365] 1200 A    192.168.0.12
R2-C [Aging:3570496] 1200 A    192.168.0.13
R2-E [Aging:3570365] 1200 A    192.168.0.199
R2-F [Aging:3570365] 1200 A    192.168.0.19
R2-G [Aging:3570365] 1200 A    192.168.0.20
rat-r2 [Aging:3562303] 1200 A    192.168.0.254
test 3600 A    10.1.1.10
VISTA-A [Aging:3558828] 1200 A    192.168.0.17
VISTA-B [Aging:3570365] 1200 A    192.168.0.51
XP-A [Aging:3562227] 1200 A    192.168.0.15
XP-B [Aging:3562227] 1200 A    192.168.0.16
Command completed successfully.

We do get the name of the record, time stamp, TTL, type & IP address. This data cannot be directly imported into Excel, however; it needs to be formatted with delimiters so that Excel can import it. We have chosen to use a “,” (comma) in this case.

Some points to keep in mind are:

  1. Observe the first few lines of the data in the example above. Each “Same as parent folder” is on a separate line with the Record name missing in subsequent lines.
  2. For static records, the text “[Aging:xxxxxxxx]” is missing.
  3. We have tried to accommodate more types of records like SRV, NS, SOA, MX, and CNAME, though typically one would be interested in the A records.

We will achieve the desired result in two steps using two VBScripts. The scripts perform the following functions:

  1. Put in the delimiter “,” to separate the data on each line. In our example, the script is named “changetocsv.vbs”.
  2. Perform a calculation to convert the “Aging” number to a readable date format and then open the file in Excel, provided Excel is installed on the machine being used. We will name this script “openexcel.vbs”.

Note that both scripts manipulate contents of the file. Each script should be run only once on a file. Here is a summary of how the overall process will work:

  • Create a directory/folder to hold the exported DNS data and script files.
  • Copy the contents of both scripts given below and place them in the folder created.
  • Export the data from DNS using the dnscmd.exe utility included with Windows Server.
  • At a Command Prompt in the folder created, run each script against the exported data to format it for and import it into Excel.

Detailed steps:

1.  Create a folder, such as C:\dnsdata, in which to store each of the scripts below.  Eg: changetocsv.vbs and openexcel.vbs.

2.  At a Command Prompt, run the following command:

dnscmd /enumrecords contoso.com @ /Type A /additional > c:\dnsdata\dns.csv

Note: For more information on dnscmd.exe, run ‘dnscmd /?’ at a Command Prompt.

3.  Save the below script as “changetocsv.vbs” in the directory created. This script will read the raw output taken from dnscmd command, format it by inserting comma delimiters, and then save it as the same filename specified at the command prompt when it is run.

Const ForReading = 1
Const ForWriting = 2

strFileName = Wscript.Arguments(0)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFileName, ForReading)

strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, " [Aging:", ",")
strNewText1 = Replace(strNewText, "] ", ",")

Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)
objFile.WriteLine strNewText1
objFile.Close

'please modify Rtype array as per the record requirements

Rtype = Array("A", "SRV", "NS", "SOA","MX","CNAME")

For i = 0 To UBound(Rtype)
rrtype = " "+Rtype(i) +"    "

Set objFile = objFSO.OpenTextFile(strFileName, ForReading)

strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, rrtype, ","+Rtype(i)+",")

Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)
objFile.WriteLine strNewText
objFile.Close    

Next

Set objFile = objFSO.OpenTextFile(strFileName, ForReading)

strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, " ", ",,")

Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)
objFile.WriteLine strNewText
objFile.Close

4.  The script takes one argument. At the command prompt while in the directory created earlier, run the following command:

C:\dnsdata> changetocsv.vbs dns.csv

This command modifies the content of dns.csv and overwrites the same file.

5.  (optional) View the modified dns.csv. If you open the new version of dns.csv, you will see that it has been changed, similar to our example below:

Returned,,records:
@,3570365,600,A,192.168.0.3
        ,3570365,600,A,192.168.0.1
        ,3570365,600,A,192.168.0.4
        ,3570365,600,A,192.168.0.2
2K-A,3558828,1200,A,192.168.0.14
clusdfs,3570365,1200,A,192.168.0.31
cluster,3570365,1200,A,192.168.0.30
contoso-dca,3570521,3600,A,192.168.0.1
CONTOSO-DCB,3570521,3600,A,192.168.0.2
CONTOSO-DCC,3570413,1200,A,192.168.0.3
CONTOSO-DCD,3570394,1200,A,192.168.0.4
R2-A,3570365,1200,A,192.168.0.11
R2-B,3570365,1200,A,192.168.0.12
R2-C,3570496,1200,A,192.168.0.13
R2-E,3570365,1200,A,192.168.0.199
R2-F,3570365,1200,A,192.168.0.19
R2-G,3570365,1200,A,192.168.0.20
rat-r2,3562303,1200,A,192.168.0.254
test,,3600,A,10.1.1.10
VISTA-A,3558828,1200,A,192.168.0.17
VISTA-B,3570365,1200,A,192.168.0.51
XP-A,3562227,1200,A,192.168.0.15
XP-B,3562227,1200,A,192.168.0.16
Command,,completed,,successfully.

Thanks to the new formatting, the file could now be easily opened in Excel as a csv file. However, the “aging” number (second column) needs to be converted to a readable date. The Aging number in the DNS data gives hours since 1/1/1600 00:00, while Excel is configured with 1/1/1900 00:00 as starting point. So we need to remove a constant from the aging number to normalize it and then specify the format. In the following script, we remove constant 2620914.50 and divide the result by 24 since Excel understands “days” rather than “hours”.

6.  Save the script file below to “openexcel.vbs”. This script will modify the comma delimited file, dns.csv in our example, to convert the number mentioned for Aging to a date format and opens the file in Excel automatically.

Const ForReading = 1
Const ForWriting = 2
strfile= wscript.Arguments(0)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strfile, ForReading)

Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
If not strLine = "" Then
    arrItems = Split(strLine, ",")

intDatevalue = 0

If not(arrItems(1))="" Then

    intDateValue = (arrItems(1) - 2620914.50)/24
 End if
   
    intItems = Ubound(arrItems)
    ReDim Preserve arrItems(intItems + 1)
    If intDateValue > 0 Then
        arrItems(intItems + 1) = intDateValue
    Else
        arrItems(intItems + 1) = ""
    End If
    strNewLine = Join (arrItems, ",")
    strNewText = strNewText & strNewLine & vbCrLf
End If 
Loop

objFile.Close

Set objFile = objFSO.OpenTextFile(strfile, ForWriting)
objFile.Write strNewText
objFile.Close

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(strfile)
Set objRange = objExcel.Cells(1, 6)
Set objRange = objRange.EntireColumn

objRange.NumberFormat = "m/d/yyyy hh:mm:ss AM/PM"

7.  The script takes one argument. At the command prompt, run the following command:

C:\dnsdata> openexcel.vbs c:\dnsdata\dns.csv

The script modifies the content of dns.csv and overwrites the same file with modified content. The above script opens the resultant file in Excel, provided Excel is available J.

IMPORTANT: Please give full path name of the file otherwise the Excel will give an error while attempting to open the file dns.csv.

The columns are Name, Aging, TTL, Type, IP address & Time Stamp. Blanks in Time Stamp indicate a static record. Below is the result after running both scripts on our example data:

dnscmdexcel

8.  Once the file is open, save the resultant as dns.xls and use that for all future reference.

Thanks “Scripting Guy” for your archives (http://www.microsoft.com/technet/scriptcenter/resources/qanda/all.mspx ) without which the VB scripts would not have been possible.

Contributed by Rajeev Narshana & Kapil Thacker

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • This week’s interesting collection of links! Kerberos Authentication problems – Service Principal Name

  • Excellent info to a common question!

  • Thanks,

    But in Vista you already have this option by default.

  • The first script did not work pretty well for me I had to replace this line:

    rrtype = " "+Rtype(i) +"    "

    by this one:

    rrtype = " "+Rtype(i) +"    "

    Thanks anyway

  • Beautiful! Thanks :)

    Ohh and thanks to Rajeev and Kapil \m/

  • Perfect!!! Thanks, Rajeev and Kapil!!

    I think the problem in the first script is copy&paste this line:

    rrtype = " "+Rtype(i) +"    "

    To correct it, you only have to replace the spaces into "     " with a keystroke of the TAB.

  • Few notes:

    1 - You don't need to keep reading the file in every time you do a replace.  Just work on the same string variable.

    2 - You don't really need the extra variables strNewText and strNewText1.

    3 - You are adding extra CRLF's to the end of the file. Just use Write.

  • I don't really understand the calculation for the timestamp and it looks like you are not taking the time zone into consideration. I'm getting half hour values??? Easier to see what is going on with dateadd in vbscript.

    strComputer = "."

    Set objWMIService = GetObject("winmgmts:" _

       & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

    Set colTimeZone = objWMIService.ExecQuery("Select * from Win32_TimeZone")

    For Each objTimeZone in colTimeZone

      wscript.echo dateadd("n",objTimeZone.Bias,dateadd("h",<DNS timestamp>,"1-Jan-1601"))

    Next

  • paulcerv - where would i add the dateadd in the original vbscript?

  • Performed this on my 2008 DC.  TWICE.

    Pass 1.

    The TYPE column was empty.  The letter 'A' was the first character in the IP Address column.  Since I couldn't figure out how to modify the script, I went to the original export file.  I found out that the character between the 'A' character and the IP Address was a TAB, and not a SPACE.  So I used WordPad to change the TAB to a SPACE.

    Pass 2.

    Results...worse.  Any row with no computer name was off by three (3) extra columns

    Also, TIMESTAMP column didn't contain any TIMESTAMPS.  Any values displayed looked like values in column G, from my first pass.

    Is there a more current script that fixes the TYPE column problem?

    Thanks.

  • Hello Michael,

    thank you very much for providing the scripts.

    I have a task to identify unnecessary static records and convert them into dynamic ones. I do not turn all statioc entries into dynamic ones. My idea was to take your two scripts, then open up the result in Excel, then delete the entries I do NOT want to convert, save the file again to *.csv. Then I want to use this last csv-file to use it with a script, that turns all the listet entries in the csv-file into dynamic entries.

    My problems are:

    - I did not found such a script flying around in the internet

    - I am not able to write such a script within an acceptable time

    - I do not anyone personally who could write such a script

    So I would like to ask if you know if such a conversion-script is available or if you are able to provide such a script?

    Thank you very much in advance for a reply, even if it is a negative answer.

    Kind regards, David

  • Hello Michael,

    Thank you so much for sharing the scripts. The first script seem to start working better after replacing rrtype = " "+Rtype(i) +"    " with rrtype = " "+Rtype(i) +"    " (TAB instead spaces).

    But when running the second script I always get an error message:

    Line: 15

    Char: 1

    Error: Subscript out of range: '[number: 1]'

    Code: 800A0009

    Source: Microsoft VBScript runtime error

    I double checked and I seem to have the same script as on this page.

    Could you help me, please?

    Thanks in advance!

  • Great Article

  • We can use "/continue" switch for getting all the records.

    dnscmd /enumrecords contoso.com @ /Type A /additional /continue > c:\dnsdata\dns.csv