Microsoft Enterprise Networking Team

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

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:

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