| Downloads |
|---|
| 93172.zip |
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:
- 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.
- Save the resulting file as a text file.
- 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.
- 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.
- 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:
- 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.
- 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.
- 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.
- 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.
Using Subs2site.vbs
I used Subs2site.vbs, which Listing 2 shows to import the subnets into AD. Although I could’ve written this code using Visual Basic Editor, I instead chose to adapt an existing script to save time. You can download Subs2site.vbs by clicking the Download the Code Here button above.
Subs2site.vbs is somewhat limited in that it can import subnets to only one site at a time. Thus, if the extracted subnets need to be imported to different sites, you need to group the extracted subnets by site and save the groups in separate files.
To use Subs2site.vbs, follow these steps:
- Make sure that the C:\Subnets\subnets.txt file contains the subnets you want to import to a particular site.
- Open Subs2site.vbs in a text editor such as Notepad.
- In the code at callout A in Listing 2, change the strSiteObjectRDN variable’s value to the target AD site.
- If you have 16-bit subnets, change /24 to /16 in the code at callout B in Listing 2.
- Run Subs2site.vbs from the command-shell window by simply typing subs2site.vbs and pressing Enter. You must have sufficient permissions to access AD for this script to work.
Subs2site.vbs will then import the listed subnets to the specified AD site. Note that if a particular subnet already exists in that site, the On Error Resume Next statement allows the script to continue to the next subnet without causing the script to end with an error. If you have multiple sites to which you want to assign subnets, you simply repeat steps 1 through 5 for each site.
A Creative Solution
Thanks to Visual Basic Editor, I was able to be resourceful and even a little artistic in devising a solution to the subnet problem. Even better, the Subnets macro and Subs2site.vbs script let me reduce a substantial task into a handful of mouse clicks and keystrokes.
