Mr. Script

Addressing the Issue, Part 2

This month, Chris shows you how to use an Excel spreadsheet to read and apply IP addresses and get your network connected.

Last month I looked at a modified version of a script that was sent to me by a reader. The purpose of the script was to change the IP address of the local machine. At the end of the column, I listed some ways to further improve the script by specifying additional IP settings, entering addresses from a pre-defined list, and enabling it to perform this operation on remote machines. This month’s script tackles the last two items:

<?xml version="1.0" ?>
<package>
<comment>
This script allows you to set a static IP address on the local computer
</comment>

   <job>
       <runtime>
       <description>

       This script allows you to set static IP addresses
       for a list of computers
       </description>

       <example>
       C:\cscript SetRemoteIP.wsf
       </example>

       <named
       name="File"
       helpstring="The name of the Excel file containing the
       list of computers and IP addresses"
       type="string"
       required="True"
       />

       </runtime>

       <object id="objXL" progid="EXCEL.Application"/>
       <object id=
"objLocator"
        progid="WbemScripting.SWbemLocator"/>

       <script language="VBScript">
       <![CDATA[
       Option Explicit
       Dim objService, objEnumerator, objInstance, intStatus,
         strIPAddr, strSNMask, strXLFile, strComputer
       strSNMask=Array("255.255.255.0")

       If WScript.Arguments.Named.Exists("File")
       strXLFile=WScript.Arguments.Named.Item("File")
       Else
       WScript.Arguments.ShowUsage()
       WScript.Quit
       End If

       objXL.Visible=True
       objXL.workbooks.open strXLFile
       objXL.sheets("IP Addresses").Activate
       objXL.ActiveSheet.range("A1").Activate

       Do While objXL.ActiveCell.Value<>""
        strComputer=objXL.ActiveCell.Value
        strIPaddr=objXL.ActiveCell.OffSet(0,1).Value
       Set        
        objService=objLocator.ConnectServer(strComputer,
        "root\cimv2",,0,context)
       Set objEnumerator=objService.ExecQuery(
        "Select * From Win32_NetworkAdapterConfiguration _
        Where DatabasePath IS NOT NULL")

       strIPaddr=Array(strIPaddr)

       objService.Security_.impersonationlevel = 3

       For Each objInstance in objEnumerator
         intStatus = objInstance.EnableStatic(
         strIPaddr, strSNMask)
       Next
       objXL.ActiveCell.OffSet(1,0).Activate
      Loop

      WScript.quit

         ]]>
      </script>

   </job>
</package>

[To download this script, right-click this link and Save As....—Ed.]

Hmm… Different, But Interesting
As you can see, this script really just combines techniques we’ve already learned in this column over the years (you have been a faithful reader, haven’t you?). Well, don’t feel bad if you missed one or two issues—I’ll remind you of what you need to know. Essentially, I use the Excel object model to programmatically read the computer name from a spreadsheet and apply the appropriate IP address for each. In order for this to work, the spreadsheet must be in the proper format. Setting it up is easy. If you’re “hard-wiring” static IP addresses to a number of computers, chances are that you’re working with a sequential range. Column A of the spreadsheet contains the computer names. Column B contains the IP addresses. The worksheet is named “IP Addresses,” and it doesn’t contain a header row. When it comes to entering the IP addresses into the spreadsheet, you can save a lot of typing by putting the first address in the first row and dragging the corner of the cell down. Excel automatically increments the last octet of the IP address by one. Of course, you’ll want to verify these numbers before you run the script.

Putting It To Work
Once I have all the computer names and IP addresses entered into the spreadsheet, I use a Do…Loop to connect to the computers individually and set the IP address and subnet mask using WMI. For the purposes of this script, I’m only using a single class C subnet. However, if you have more than one subnet, it’s simple to put the appropriate subnet mask in the spreadsheet.

Depending upon how many computers are in your worksheet, this script might be running for quite a while. Remember, it has to individually connect to each computer, query that computer’s IP information and change it to the information specified in the spreadsheet.

Optional Optimizations
If you’re only changing a few computers, you could add a line that displays the success or failure of each operation. If you’re leaving this script to run overnight, you can add a line to have it log the success or failure for each computer—just in case someone accidentally turned off their computer when they went home.

Wrapping Things Up
Next month, I’ll will finish my look into using WMI to edit IP information by showing you how to set additional IP-related information, including DNS and WINS servers and a default gateway.

About the Author

Chris Brooke, MCSE, is a contributing editor for Redmond magazine and director of enterprise technology for ComponentSource. He specializes in development, integration services and network/Internet administration. Send questions or your favorite scripts to [email protected].

Featured

  • Microsoft Warns IT Pros on Windows Netlogon Fix Coming Next Month

    Microsoft on Thursday issued a reminder to organizations to ensure that their systems are properly patched for a "Critical"-rated Windows Netlogon vulnerability before next month's "update Tuesday" patch distribution arrives.

  • Microsoft Nudging Skype for Business Users to Teams

    Microsoft on Thursday announced some perks and prods for Skype for Business unified communications users, with the aim of moving them to the Microsoft Teams collaboration service instead.

  • How To Improve Windows 10's Sound and Video Quality

    Windows 10 comes with built-in tools that can help users get the most out of their sound and video hardware.

  • Microsoft Offers More 'Solorigate' Advice Using Microsoft 365 Defender Tools

    Microsoft issued yet another article with advice on how to use its Microsoft 365 Defender suite of tools to protect against "Solorigate" advanced persistent threat types of attacks in a Thursday announcement.

comments powered by Disqus