A Creative (and Easy) Way to Assign IP Subnets to AD Sites

As a consultant, I’m often brought in as hired help when projects or daily tasks are overwhelming the onsite IT staff. In this role, I sometimes get assigned maintenance or cleanup tasks that initially seem, well, boring. What can make these jobs more interesting is the chance to be creative in accomplishing the chore. When the solution involves writing some nifty code that does the job in minutes, what seemed to be just another tedious assignment turns out to be downright satisfying. This was the case when I was recently asked to look into a Netlogon warning that was showing up on all the domain controllers (DCs) in a fairly large corporation. This warning was accompanied by spotty complaints of slow logons. Figure 1 shows the important parts of the warning message.

This company has a home office with several thousand users and hundreds of branch offices with hundreds of clients at each branch. Apparently, when the company migrated to Active Directory (AD), only some of the IP subnets in use had been assigned to AD sites. As event 5807 warns, not associating IP subnets to AD sites can lead to clients at one location authenticating to a DC in another location several IP hops away, even though there might be a DC sitting just 10 feet from the client. The Microsoft article “How Domain Controllers Are Located in Windows” (http://support.microsoft.com/?kbid=247811) emphasizes the importance of having subnets associated with AD sites as a client’s primary means of finding an optimal DC with which to communicate.

When I looked at the netlogon.log file on one of the branch offices’ DCs, I discovered the file was a whopping 17MB and consisted primarily of NO_CLIENT_SITE entries, as Figure 2 shows. Closer inspection of this log revealed that clients from the home office and clients from other branches (which had their own local DCs) had been authenticating to this remote server. Clearly, the important task of associating IP subnets to AD sites had fallen through the cracks during the migration to AD, and this problem needed to be fixed.

Let me walk you through the solution I used to fix the subnet problem. This solution involves getting the necessary subnet information, using a macro called Subnets, and using a script named Subs2site.vbs.

Getting the Subnet Information
Fortunately, the company in question had records of which subnets were in use at the various locations. Besides needing this information, I also needed an easy way to extract subnet information from the netlogon.log files. With both sets of information, I could use a script to associate IP subnets to AD sites.

Microsoft Excel is a perfect tool for extracting data from log files. It supports Visual Basic for Applications (VBA) through a macro-editing tool called Visual Basic Editor. Using the editor’s Watch feature, you can monitor the value of any variable as you step through the macros or scripts that you create with this tool. You can get explanations and examples of code syntax at any time by highlighting a keyword and pressing the F1 key. Visual Basic Editor is included with most Microsoft Office programs, so you probably already have it on hand. A separate scripting tool with the same level of functionality as Visual Basic Editor can cost more than $100, so it’s worth getting to know this editor.

To extract the subnets from the IP addresses in the netlogon.log files, you can follow a simple five-step procedure:

1. To open a netlogon.log file in Excel, you need to replace the spaces between the six fields in each line (see Figure 2) with tabs. To do so, open the netlogon.log file in Microsoft Word or another text editor that lets you insert tab characters. Choose the Replace option from the Edit menu. With the cursor sitting in the Find what text box, press the space bar once to insert a space character. To get the Tab character in the Replace with text box, click the More button, choose Special, then select Tab Character. Finally, click the Replace All button.
2. Save the resulting file as a text file.
3. Open the text file in Excel as a tab-delimited file. Excel places each tab-separated field into a separate column, which makes it easy to delete columns. Delete all the columns except the one that contains the IP addresses.
4. Sort the IP addresses by highlighting the column and choosing Sort from the Data menu. Sort the column by ascending order. After making sure that the No header row option is selected, click OK. Sorting the column is crucial to the logic of the Subnets macro, so don’t omit this step.
5. Rename this worksheet to Sheet1 by double-clicking the current name and entering Sheet1. Then, insert an additional worksheet by selecting Worksheet under the Insert menu. Excel should automatically name the new worksheet Sheet2. Like step 4, this step is crucial and can’t be omitted because certain commands in the Subnets macro refer to these worksheets by name.

Using the Subnets Macro
The Subnets macro, which Listing 1 shows, progresses through the list of IP addresses in Sheet1, extracts a list of unique subnets, and writes them to a column in Sheet2. You can download this macro by clicking the Download the Code Here button above. The sidebar “How the Subnets Macro Works” describes the macro’s code.

To use the Subnets macro, follow these steps:

1. To be able to execute macros with Visual Basic Editor, you must turn on Excel’s ability to run them. This ability is turned off by default in the latest Office applications to protect you from malicious scripts that might attempt to execute without your approval. To change the default, open Excel, then select Macro, Security on the Tools menu. Set the security level to Low while you’re editing or running the Subnets macro. When you’re done, reset the security level to High.
2. Paste the Subnets macro in Visual Basic Editor as a general module available to the entire workbook rather than as a macro attached to a specific sheet. To do this, access a blank module space by selecting Tools, Macro, Visual Basic Editor. In the editor, select the Module option on the Insert menu. Paste the Subnets macro into the window that appears.
3. Switch back to Excel. On the Tools menu, select Macro followed by, Macros. Select Subnets, then click Run. The macro will extract all the unique subnets and write them to Sheet2.
4. Delete Sheet1, and save Sheet2 as a text file named subnets.txt. Place this file in a folder named Subnets on the C: drive (C:\Subnets). When Subs2site.vbs runs, it will look for the subnets.txt file at this location.