Parsing DNS server log to track active clients

  • Article

Some time ago, a customer of mine was planning a move of their DNS Server infrastructure from Windows Servers to a DNS/IPAM appliance, and although they had modified the DHCP scope options to use the new DNS servers, they wanted to determine how many clients were still resolving DNS names using their Windows DNS servers.

The following steps show how we enabled debug logging of the DNS server service and parsed the logs for the data. Although we could have written a PowerShell script to parse the logs, we ended up using LogParser this time around.

Enabling Debug Logging

You may need to enable debug logging on all DNS servers to make sure you capture all client queries.

  1. Open the DNS management console and connect to the Windows DNS server.
  1. Right-click the DNS server, click properties and go to the Debug Logging tab.
  1. Type in a filename and path for the log file and change the maximum size (bytes), if needed, and click OK. In the example below, I set the size to 536870912 which is 512MB.

  1. Finally, restart the DNS server service

 

Anatomy of the log file

The first 29 lines contain a description of each of the columns in the log

Below that, you will find one line for each packet exchanged between the DNS client and server, with the values space-delimited:

 

Parsing the log file

  1. After the span of a business cycle, copy the log file(s) to another machine.
  2. Download Log Parser 2.2 from https://www.microsoft.com/en-us/download/confirmation.aspx?id=24659 and install it.
  3. Launch a command prompt as administrator and change the directory to C:\Program Files (x86)\Log Parser 2.2 which is the default install location.
  4. Run the following command, replacing the highlighted section with the path to your stored log file:

LogParser -i:TSV -nskiplines:30 -headerRow:off -iSeparator:space -nSep:1 -fixedSep:off -rtp:-1 "SELECT field9 AS IP, REVERSEDNS(IP) AS Name, count(IP) as QueryCount FROM "C:\Temp\DNS\dns.log" WHERE field11 = 'Q' GROUP BY IP ORDER BY QueryCount DESC"

See below for a description of the various switches:

  • -i:TSV - Input format (tab-separated values)
  • -nskiplines:30 - Number of initial lines to skip (30)
  • -headerRow:off - Treat first row of each file as a header (OFF)
  • -iSeparator:space - Separator character between fields (SPACE)
  • -nSep:1 - Number of separator characters between fields (1)
  • -fixedSep:off - The 'nsep' parameter is assumed to indicate the minimum number of separator characters between fields
  • -rtp:-1 – Rows to print (-1 = all rows)
  • "SELECT
    • field9 AS IP – Set header of column 9 to “IP”
    • REVERSEDNS(IP) AS Name - Resolve IP address and set a new column with header “Name” to the corresponding host name.
    • count(IP) as QueryCount - Stores the number of number of instances of “IP” in a column with header “QueryCount”.
  • FROM "C:\Temp\DNS\dns.log" WHERE
    • field11 = 'Q' – Only pick instances where packets are of type “Standard Query”
  • GROUP BY IP
  • ORDER BY QueryCount DESC"

 

And here is what the output looks like:

IP                                        Name                                  QueryCount

-------------- ------------------------------------- ----------

10.10.20.71                     10.10.20.71                                 41

10.10.20.21                     RootMS01                                  27

127.0.0.1                          RootMS02                                 27

10.10.20.12                     ChildDC01                                 23

202.12.27.33                   M.ROOT-SERVERS.NET      21

192.36.148.17                   i.root-servers.net                    4

199.7.83.42                      l.root-servers.net                    4

192.112.36.4                    G.ROOT-SERVERS.NET       4

192.203.230.10               e.root-servers.net                    3

192.58.128.30                  j.root-servers.net                    3

199.7.91.13                       d.root-servers.net                    3

192.228.79.201                b-2016.b.root-servers.net     2

128.63.2.53                       h.root-servers.net                   2

198.41.0.4                         a.root-servers.net                    2

192.33.4.12                       c.root-servers.net                    2

193.0.14.129                     k.root-servers.net                    1

192.5.5.241                       f.root-servers.net                      1

 

Statistics:

-----------

Elements processed: 255

Elements output:    17

Execution time:     4.25 seconds

 

Using this data, my customer was able to identify all machines that had the DNS servers’ ip addresses assigned statically, and were able to point them to the new appliances. Doing so, prevented any downtime due to the change.