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. In my previous Daily Drill Down, I introduced my Network Address Inventory Utility, a free download that offers a quick way to retrieve this information. The utility automates the use of the Net View, NBTSTAT, and ping commands to gather the information and then compile the data in an Excel spreadsheet. In this Daily Drill Down, I’ll analyze each section of code in the script to give you a better understanding of how it all works so you can later tweak the code to create tools to fit your needs. Click here to view the complete script.
Since this script has so many variables, 30 in fact, I’ve decided to begin the script with the Option Explicit statement in line 1. Using this statement at the beginning of a script forces the explicit declaration of all variables with the Dim statement. So if a variable appears in the script, it must also appear in a Dim line. If the variable doesn’t show up, VBScript pops up with an error message at run time. Basically the Option Explicit statement prevents accidental reuse of a variable name, and it helps you quickly catch any typos.
Then, lines 2 thru 5 use the Dim statement to define the variables used in the script. As you look at the variable names, you’ll see I’ve used names that represent the data each one will hold.
Line 6 sets up the constant called ForReading and sets its value to 1. Setting up this constant makes it easy to configure the commands that open text files for reading information. Line 7 initializes a global variable, called Row, which will be used to indicate successive row numbers in the Excel spreadsheet. The initial value is set to 2, which is the first row in the spreadsheet that the script will use for data. I will explain more about this later in the article.
In lines 8 thru 11, I use Set statements to assign object references to variables. Line 8 uses the Set statement to activate the Excel object model and assigns those properties and methods that the Excel object exposes to a variable called XL. Line 9 uses the Set statement to activate all of the features of the Windows Script Host object model and assign those features to the WshShell variable. Since this script will need to access the file system to open text files, line 10 uses the Set statement to activate all of the file system features provided by the FileSystemObject and assign them to the FileSystem variable. Line 11 activates the regular expression object with the New RegExp command. The object reference is then assigned to the RegularExpression variable.
In line 12, I use the Windows Script Host Popup method to display a splash screen to alert the user that the script has begun its operation and ask the user to wait. This dialog box is displayed on the screen for one second and then closes itself so the script can continue processing the remaining commands.
Creating the spreadsheet template
Since an Excel spreadsheet will be the repository for the data collected by the Network Address Inventory Utility, the next part of the script creates a spreadsheet template. To launch this part of the operation, line 13 uses a Call statement to activate the BuildSpreadSheet subroutine. Because this subroutine will simply create an empty spreadsheet, there are no parameters to pass along—thus the empty parentheses at the end of the line.
At this point, let’s jump down to line 41, which begins the 11-line BuildSpreadSheet subroutine. In line 8, I activated the Excel object model and assigned its properties and methods to the XL variable. With the Set XL command, I’ve actually launched Excel in the background. To bring it to the foreground, I set the Visible property to True in line 42. Then, in line 43, I use the Add method of the Workbooks object to create a new spreadsheet.
For the spreadsheet to accommodate the data, I need to format the columns, so in lines 44 through 46 with the Columns(x).ColumnWidth property, I set the column width of the first three columns to 20 characters each. Then, to establish the column headers, I use the Cells(x, y).Value property to specify the headers for the three columns in lines 47 thru 49. Once I have the headers in place, I format them by selecting them, making them bold, and then increasing the font size to 12 points. I use the Range(“xx,:xx”).Select property in line 50, the Selection.Font.Bold = True property in line 51, and the Selection.Font.Size = 12 property in line 52.
As soon as the Excel spreadsheet template is complete, the script jumps back to line 14 where I begin the process of obtaining a list of host names assigned to each computer.
Obtaining a list of the computers
The key to the Network Address Inventory Utility script is obtaining host names. Once I have the host name of each computer on the network, I can use them to retrieve each system’s IP address and MAC address.
To obtain a list of all the computers on the network, I use the Run method of the WshShell object to shell out to DOS via the Cmd.exe /c command. Here at line 14, Cmd.exe starts a DOS session, and the /c parameter configures the command interpreter to carry out the commands that follow it and then exits. In this case, the command that follows it is a Net View command. I then use the DOS-based redirection symbol (>) to pipe the results of the Net View command into the NetViewList.txt file, which is created in the C:\Windows\Temp folder.
Line 15 then employs the OpenTextFile method to open the file for reading and uses the Set command to assign the contents of the file to the TheNVFile object variable.
At this point, the TheNVFile object variable contains a list of all the computers on the network in the format created by the Net View command. For example, one line in the file might look like this:
\\Frodo Main System
I need to grab each line in the file and extract just the name of the computer. Once I have the name of a computer, I can then use it to get the system’s IP address and MAC address.
Finding a host name
In line 16, I initiate a Do While loop that runs until the AtEndOfStream file pointer is set to True, which indicates that the script has read all the data in the NetViewList.txt file. Line 17 reads one line of the text file and assigns that string to the TheLine variable. Then, line 18 assigns the Whacks variable to a string containing the characters “\\,” which signifies that the text immediately following is the host name assigned to the computer. I then call the FindPattern function in line 19 and send it the TheLine and Whacks variables.
The job of the FindPattern function, lines 70 thru 77, is to search a text string for a specific pattern and then return a Boolean value that indicates whether or not the pattern was found. In this case, let’s assume that the text string is the following line:
\\Frodo Main System
To perform its job, the FindPattern function receives two variables, which it renames to TheText and ThePattern. The TheText variable will contain a string of text, and the ThePattern variable will contain a specific group of characters that might include some special regular expression characters that work like wildcards. This group of characters is called a pattern.
In line 71, I assign the contents of the ThePattern variable to the regular expression Pattern property. Then, in lines 72 thru 76 I use an If…Then…Else statement to run the regular expression Test method, which determines whether or not the specified pattern is contained in the string of text. If it is, I return the Boolean value True by assigning that result to the function name in line 73. If not, I set the return result to False in line 75.
Extracting the host name
The FindPattern function will return a Boolean variable of True if the characters are found in the string and False if not. That result will then be assigned to the WhacksFound variable.
Line 20 then uses an If…Then statement to test the value of the WhacksFound variable. If the WhacksFound variable is set to False, execution of the script drops to line 35 and loop begins again.
If the WhacksFound variable is set to True, the host name of a computer was found in the string of text and execution of the script proceeds to line 21, which assigns the WhacksPattern variable to a string containing the characters “\\” and two of the special regular expression characters “\S” and “*.”
You can think of these special characters as wildcards. The regular expression character “\S” specifies that any characters—both letters and numbers—that immediately follow the “\\” be considered a match. The regular expression character “*” specifies that there can be any number of characters, i.e. one or more.
At line 22, I set the Flag variable to 1. This variable will be sent to the GetPattern function and will indicate that this is the first time this function is being called and that the result to be returned should be a host name. In line 23, I call the GetPattern function and send it the TheLine, WhacksPattern, and Flag variables.
The job of the GetPattern function, lines 78 thru 88, is to extract the host name from the text string and return it to the calling routine. Again, let’s assume that the text string is this line:
\\Frodo Main System
In this case, the script must extract only the host name Frodo and discard everything else.
In line 79, I assign the contents of the ThePattern variable to the regular expression Pattern property. In line 80, I run the regular expression Execute method, which locates and extracts the pattern from the string and assigns it to the Matches collection. Since a collection is a form of an array, I must use a For…Next loop to read through the collection and access the pattern. So I use the Value property of the Match object in line 82 and assign the pattern to the TheMatch variable.
Once I’ve accessed the pattern and assigned it to TheMatch variable, I must clean it up a bit. The way I clean it up depends on the value that the Flag variable contains. In this case, the Flag variable is set to 1, which indicates that the TheMatch variable contains a host name. So the process of cleaning it up means simply removing the UNC “\\” characters that precede the name. To do so, I use the Mid function in line 83 to advance to the third character in the string, or pattern, and return it and every character that follows and then reassign that to the TheMatch variable.
At this point, the TheMatch variable contains the host name, i.e. Frodo from my example. Then, in line 87 I assign the TheMatch variable to the function name GetPattern and the function ends.
Getting the Mac address
When the script returns to line 23, I assign the result of the GetPattern function to the HostName variable. In line 24 I call the GetNBTable function and send it the HostName variable, which in my example contains the host name Frodo.
The job of the GetNBTable function—lines 89 thru 96—is to shell out to a DOS prompt, run the NBTStat command, and route the NetBIOS table for that particular computer to a file called NBTList.txt. These three steps are actually done in lines 90 and 91. The NetBIOS table generated by the NBTStat command will contain, among other things, the MAC address assigned to that computer’s network adapter.
Lines 92 and 93 then open the NBTList.txt and assign its contents to the function name GetNBTable. In lines 94 and 95, I perform some housekeeping tasks by closing the NBTList.txt file and deleting it. The function then ends.
When the script returns to line 24, I assign the result of the GetNBTable function to the NBTable variable. In line 25, I assign the MACPattern variable to a string containing the characters “MAC Address =” and the special regular expression character “\S,” which signifies the text that immediately follows is the MAC address assigned to that computer’s network adapter. In line 26, I set the Flag variable to a value of 2. Line 27 then calls GetPattern function and sends it the NBTable, MACPattern, and Flag variables.
In this case, the job of the GetPattern function will be to search through the NetBIOS table and extract and return the MAC address. When the GetPattern function returns that value, it will be assigned to the MACAddress variable.
Getting the IP address
After the script obtains the MAC address assigned to the network adapter, it proceeds to track down the IP address assigned to the system. So line 28 calls the GetIPAddress function and sends it the HostName variable.
The job of the GetIPAddress function, which consists of lines 97 thru 104, is to shell out to a DOS prompt and run the ping command using the host name. For example, if the HostName variable contained the host name Frodo, the ping command would be this:
Ping –n 1 Frodo
The results from the ping command are routed to a file called IPList.txt. This is done in lines 98 and 99. The results will contain, among other things, the IP address assigned to the system.
Lines 100 and 101 open the IPList.txt and assign its contents to the function name GetIPAddress. In lines 102 and 103, I perform some housekeeping tasks by closing the IPList.txt file and deleting it. Then, the function ends.
When the script returns to line 28, I assign the result of the GetIPAddress function to the PingReport variable. In line 29, I assign the PingPattern variable to a string containing the characters “Reply from” and the special regular expression character “\S*,” which signifies that the text immediately following is the IP address name assigned to the computer’s network adapter. In line 30, I set the Flag variable to a value of 3. Line 31 calls the GetPattern function and sends it the PingReport, PingPattern, and Flag variables.
In this case, the job of the GetPattern function will be to search through the results of the ping command and extract and return just the IP address. When the GetPattern function returns that value, it will be assigned to the IPAddress variable.
When the GetPattern function returns the IP address, it will have a colon character (“:”) appended to it. Line 32 uses the VBScript Replace function to easily remove that character and reassign the results to the IPAddress variable.
Populating the spreadsheet
At this point, the script has obtained the host name of one computer on the network and the IP address and MAC address assigned to that computer. This information is stored in the variables: HostName, IPAddress, and MACAddress. The next step is to add this data to the Excel spreadsheet. So line 33 calls the AddToSpreadSheet subroutine and sends it the HostName, IPAddress, and MACAddress variables.
The AddToSpreadSheet subroutine, which consists of lines 54 thru 60, uses the Cells(x, x).Value property in lines 55 thru 57 to add the contents of the variables to the appropriate cells in the spreadsheet. The first time that AddToSpreadSheet subroutine runs, the value of the Row variable is 2, so the data is added to the first three columns in row 2.
Line 58 increments the Row variable to 3, and line 59 uses the Cells(x, x).select property to advance the pointer focus in Excel to the next row. The next time the AddToSpreadSheet subroutine runs, the data will be entered on row 3 and so on.
Once the script has processed all the computers in the list, it drops out of the loop at line 35. Lines 36 and 37 perform some housekeeping tasks by closing the NVList.txt file and deleting it. In line 38, the script displays a dialog box that informs the user that the script is finished. This message will remain on the screen for five seconds. Once the dialog box disappears, the script advances to line 39, which calls the SaveSpreadSheet subroutine.
The SaveSpreadSheet subroutine, which consists of lines 61 thru 69, begins by running the VBScript Date function on line 62. The reason for this call is that the script will name the Excel spreadsheet file with a default name and append the current date to help you keep track of your inventory.
The Date function returns the current date and assigns it to the TheDate Variable. However, the Date function returns the date in the format mm/dd/yy, and the backslashes (/) aren’t compatible with file-naming conventions. So I use the VBScript Replace function in line 63 to remove that character from the date and replace it with the dash (-) character and then reassign the results to the TheDate variable.
Line 64 concatenates the default filename “NetAI” with the current date and then assigns that value to the Suggestion variable. In line 65, I call the Excel GetSaveAsFilename method and pass it the Suggestion variable, which causes Excel to open a File Save As dialog box and insert the contents of the Suggestion variable into the dialog box’s File Name text box.
In line 66, I test to see if the user clicked the Cancel button instead of the Save button. If the user clicks the Save button, line 67 saves the file. The script then returns to line 40, which calls the Windows Script Host Quit method and the script terminates.
After going through this script line-by-line, you can really appreciate how powerful Windows Script Host can be in automating specific tasks. In the case of the Network Address Inventory Utility, this can save you a tremendous amount of time and effort. Moving forward, you could use this script as a template to construct other time-saving troubleshooting tasks, such as capturing other system data. However, be sure to first read my previous Daily Drill Down to understand the prerequisites for the script to work.