When troubleshooting network problems, having a list of basic information about each system on the network, such as the host name, IP Address, and MAC address can be a big help. However, manually gathering that information can take hours, as you would have to use many commands to gather the data and then you would probably want to organize it into some form of document.

To make things easier for you, I’ve developed a VBScript utility for you to download called the Network Address Inventory Utility, which automates the entire procedure right down to creating an Excel spreadsheet and automatically entering each system’s host name, IP address, and MAC address into it. The best part is that the VBScript will perform this operation in a matter of minutes.

In this Daily Drill Down, I’ll describe how it works and explain each of its major components in detail. In a follow-up article, I’ll analyze the script line by line for those of you who want to tweak the VBScript and Windows Script Host for your own troubleshooting purposes.

Before you can use the Network Address Inventory Utility, you must first enable File And Printer Sharing For Microsoft Networks on all systems on the network. The systems don’t actually have to be sharing a resource on the network, they just need to have the service enabled, because the Net View command—the basis of the Network Address Inventory—can only detect the names of computers on the network that have it enabled.

Second, you must install the TCP/IP protocol on all the computers on your network because the Network Address Inventory Utility gathers IP addresses, and the TCP/IP-based tools, NBTStat and ping, are also used in the Network Address Inventory Utility.

Third, you must have the Windows Script Host 5.6 package installed on the system on which you will run the Network Address Inventory Utility. If you’re running Windows XP, you’re all set. However, if you’re running Windows 2000, Windows Me, or Windows 9x, you’ll need to download and install Windows Script Host 5.6.

You can download the Windows Script Host 5.6 package from the Microsoft Windows Script section of the MSDN site. Just scroll down the tree in the right pane until you see the Windows Script 5.6 section, and select the Windows Script 5.6 item. The Windows Script 5.6 package includes Windows Script Host 5.6, VBScript 5.6, JScript 5.6, the Windows Script Components, and the Windows Script Runtime. Note that there are two versions of the Windows Script Host 5.6 package: one for Windows 2000 and one for Windows 95/98/Me/NT.

Finally, you must have Excel 97 or above installed on the system on which you will run the utility, because it’s designed to automate the Excel object and use an Excel spreadsheet as the data repository.

I designed the Network Address Inventory Utility script to automate the tasks of obtaining the host names of all computers on the network, finding their IP addresses, and reporting the MAC addresses assigned to each system’s network adapter using three DOS-based commands: Net View, NBTSTAT, and ping. The script will then create an Excel spreadsheet and add the specific information extracted from the DOS commands to it. To perform this procedure, the script will take advantage of three capabilities built into the VBScript and Windows Script Host: the ability shell out to a command prompt and run DOS-based commands, the ability to search through text files for specific information with regular expressions, and the ability to programmatically automate Microsoft Office applications.

Note OS differences

Keep in mind that the commands that shell out to the DOS prompt in this particular script are designed to run under Windows XP. However, with a few changes to these commands, the script can be modified to run on any Windows OS. First, Cmd.exe is the command interpreter for Windows NT, Windows 2000, and Windows XP. If you plan to run this script from Windows Me or Windows 9x, simply replace Cmd.exe with Command.com. Also, C:\Windows is the OS home directory for Windows XP, Windows Me, and Windows 9x. To run this script from Windows 2000 or Windows NT, simply replace C:\Windows with C:\Winnt.

Running the DOS commands
To run the DOS commands from within the script, I use the Run method of the WshShell object to shell out to DOS via the Cmd.exe /c command, like this:
WshShell.Run “Cmd.exe /c”

Here, Cmd.exe starts a DOS session, and the /c parameter configures the command interpreter to carry out the commands that follow it and then exit. I then use the DOS-based redirection symbol (>) to pipe the results of the DOS commands into text files as in the following:
WshShell.Run “Cmd.exe /c DOSCommand > TextFile.txt”

The Net View command
To do its job, the Network Address Inventory Utility script first runs the Net View command, which displays a list of all the computers on the network, as shown in Figure A.

Figure A

The script will then need to extract just the host names of the computers from this list.

The NBTSTAT command
Once the script has collected the host names of the computers, it will use them to get the MAC address assigned to the network adapters by running the NBTSTAT command like this:
NBTStat –a HostName

Here, the parameter -a followed by the name of a computer displays that system’s NetBIOS name table, which includes the MAC addresses assigned to the system’s network adapter, as shown Figure B.

Figure B

The script will then need to extract just the MAC address from this list.

The ping command
Next, the script will use host names of the computers to get the IP address assigned to each system by running the ping command along with the host name, as in the following:
Ping –n 1 HostName

Here, the -n 1 parameters configure the ping command to ping HostName once rather than the default of four times. The result of this command is shown in Figure C.

Figure C

The script will then extract only the IP address from this list.

Why use DOS-based commands?

At this point, you may be wondering why I chose to use DOS-based commands to obtain information from the computers on the network rather than the features provided by Windows Management Instrumentation—or WMI for short. Well, the answer is twofold: First, WMI is very detailed, and the procedure of extracting the network addresses tends to be overly complex. Second, these DOS-based commands are quicker at providing the information the script needs.

Regular expressions
To locate and extract the pertinent information from the text files generated by the DOS commands, the Network Address Inventory Utility script employs VBScript’s regular expressions feature. This feature works basically like the DOS-based wildcard characters that you use when searching for files on your hard disk.

Specifically, regular expressions are implemented in VBScript via a special object called RegExp. To use regular expressions to conduct a search, you use one of its properties, the Pattern property, and two of its methods, the Test and Execute methods.

In the case of the Network Address Inventory Utility, you’ll assign the text string that you want to search for in the Pattern property. Then, use the Test method to search through a text string for that particular pattern. The Test method then returns a Boolean value that indicates whether or not the pattern was found. Then, use the Execute method to go though the text string, locate the pattern, and extract it.

After the Execute method does its job, it stores that pattern as a Match object in a Matches collection, which is basically an array of Match objects. The Match object, which has it’s own set of properties, will store the extracted piece of text in the Value property. You can then get the one piece of information you need by accessing the Value property.

Automating Microsoft Excel
Having a procedure for collecting network address information from the systems on a network isn’t very useful if you don’t have a way of studying and manipulating the data. Of course, the script could easily display the data in a dialog box or send it to a text file, but neither of those options provides a very nice solution, since it would be difficult to analyze the information.

Fortunately, Microsoft Excel provides a very detailed application object model that can be easily accessed by Windows Script Host. You can perform most of the same operations in Excel via a script that you can when using the application manually.

The Network Address Inventory Utility will automatically launch Excel, create a spreadsheet template specifically formatted for displaying the data, and then add the data to the spreadsheet.

To launch Excel from within a script, you initiate the Excel.Application object and then call the Visible method (see code below). You also use a Set command to assign the Excel object’s properties and methods to a variable, which you can use later on to perform additional operations.
Set objVariable = WScript.CreateObject(“Excel.Application”)
objVariable.Visible = True

You can then create a brand new spreadsheet by using the Add method of the Workbooks object. As you do so, you also assign the WorkBooks object properties and methods to a variable (see code below), which you can use later to perform additional operations.
Set ObjVariable = ObjVariable. WorkBooks.Add

Once you’ve created a new spreadsheet, you can format it using a series of properties and methods that the Excel object makes available. You can set the width of columns with this command:
ObjVariable.Columns(x).ColumnWidth = #

You can also select a range, choose a font size, and make the text bold with the following commands:
ObjVariable.Selection.Font.Size = #
ObjVariable.Selection.Font.Bold = True

You can then add data to the cells with this command:
ObjVariable,Cells(x, y).Value = “Text”

Finally, you can save your spreadsheet using the following commands:
Variable = ObjVariable.GetSaveAsFilename()

If typing in lines of code isn’t your cup of tea, don’t worry; the NetInventory.vbs script is available for download. To download the script, just click here.

The result
When you run the script, you’ll have a spreadsheet, like the one shown in Figure D, that contains the name of each system on the network along with the IP address and MAC address assigned to each. You can then sort the data any way you want. Keep in mind that the next time you run the Network Address Inventory Utility, the script will create a brand new spreadsheet with new data.

Figure D
When the utility completes its task, you’ll have an easy-to-use spreadsheet that contains the Host Name, IP address, and MAC address for all the systems on your network.

This handy utility quickly provides information for troubleshooting your network in an easy-to-read spreadsheet. In my follow-up Daily Drill Down, I’ll explain each section of code in the Network Address Inventory Utility script line-by-line to show you how it all works.