Parsing DNS server log to track active clients
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.
- Open the DNS management console and connect to the Windows DNS server.
- Right-click the DNS server, click properties and go to the Debug Logging tab.
- 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.
- 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
- After the span of a business cycle, copy the log file(s) to another machine.
- Download Log Parser 2.2 from https://www.microsoft.com/en-us/download/confirmation.aspx?id=24659 and install it.
- 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.
- 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)
- 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
18.104.22.168 M.ROOT-SERVERS.NET 21
22.214.171.124 i.root-servers.net 4
126.96.36.199 l.root-servers.net 4
188.8.131.52 G.ROOT-SERVERS.NET 4
184.108.40.206 e.root-servers.net 3
220.127.116.11 j.root-servers.net 3
18.104.22.168 d.root-servers.net 3
22.214.171.124 b-2016.b.root-servers.net 2
126.96.36.199 h.root-servers.net 2
188.8.131.52 a.root-servers.net 2
184.108.40.206 c.root-servers.net 2
220.127.116.11 k.root-servers.net 1
18.104.22.168 f.root-servers.net 1
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.