IT Intern Blog

IT Challenges Solved

Archive for July, 2009

VBScript with WMI for Windows Workstation Audit

Posted by IT Intern on July 24, 2009

One of the first projects my boss assigned me was to do an audit of all the workstations in both buildings of our office. His main reason was that he’s sure there are many workstations not being backed up. He initially said he wants the name and physical location of each machine, as well as the active drive letters that should be backed up. He also mentioned that some of the workstations did not have the correct administrator password, and he’d like to know what each one is set as. He didn’t give me any specifics on how he wanted to collect the information, but I sort of got the impression he expected I would simply walk around to each machine and write it all down.

The IT Intern doesn’t work that way. Since it’s my project, it’s my decision how to get it done. And I chose to use VBScript with WMI. I’m not exactly familiar with either of those (VBScript moreso than WMI, but I’m here to learn, right?), so I did a fair bit of researching VBS expressions and WMI classes, and I managed to come up with a pretty neat script. It’s not as awesome as it could be – it writes to a tab-delimited text file – but the goal of the project didn’t justify spending a lot of time on it. If it were up to me, I’d make the script do the actual update and record everything in a database (I’m not sure why there isn’t a database for this already), but that’s not what the boss wants. On second thought, if he knew I could make it happen he might decide it’s worth it (I’d love to have a database project). But let’s just get through this script for now.

Since the project is fairly simple, I made a script that runs locally on each machine. It does all the work itself, so we can have everyone run it from their own computers and in a few minutes we’ll have all the information we need. If we were to scale the project up, I’d make it run remotely.

I mentioned the tab-delimited output file. I chose tab-delimited so the contents of the file can be copied directly into a spreadsheet. Again, it’s a small project and only needs to happen once, but if we were to go further I would make the data write directly to a database.

Now we get to the good stuff: the code. I’m a firm believer in commenting code, so my file starts off with a couple paragraphs of comments.

‘ WorkstationAudit.vbs
‘ by The IT Intern

‘ This script is to be run locally on each machine whenever an audit is performed.
‘ Extracted information will be appended to a .txt file on \\MYSERVER\Audits
‘ The contents of the .txt file can be copied as a whole into an Excel Spreadsheet,
‘ which will then be properly formatted for either use as-is or for exporting to a database.

‘ PRIOR TO EACH AUDIT:
‘ Locate the file “SysInfoTemplate.txt” on \\MYSERVER\Audits
‘ This file contains the column headers for the spreadsheet
‘ Make a copy of the file in the same folder and rename the copy to “SysInfo.txt”
‘ When the audit is complete, copy the contents of “SysInfo.txt” to an Excel Spreadsheet.
‘ When the spreadsheet has been reviewed and saved, delete “SysInfo.txt”

‘ Information collected from this script:
‘ ===============================
‘ – Computer Name
‘ – User Name of user logged on
‘ – IP Address
‘ – MAC Address
‘ – Drive Letters (only for drives of type 3 = local disk)
‘ – Size of each drive
‘ – Free space of each drive
‘ – Operating System and Version on each drive
‘ – Also sets the Administrator password
‘ ===============================
‘**Start Encode**

The last line includes “Start Encode,” which is for Windows Script Encoder. Rather than pull the administrator password from each machine, review each, and walk around and make sure all the wrong ones are corrected, I decided to make the script just set the password right when it’s run so we know they are all correct. But that means the password is right in the .vbs file, which anyone can read with a text editor. We decided to use Windows Script Encoder to generate a .vbe file which will ultimately be used to run on the workstations. Although it is possible to decode a script that has been encoded with Windows Script Encoder, it’s secure enough for our needs.

Now for the dims and setting variables.

Option Explicit
Dim objWMIService, objItem, objItem2, colItems, colItems2, strComputer
Dim MyConf, MySysInfo, MyCompName, MyIP, MyMac, MyUserName
Dim objFSO, objFolder, objShell, objTextFile, objFile, objUser
Dim strDirectory, strFile, strText
‘ Sets the directory and filename for the exported info
strDirectory = “\\MYSERVER\Audits”
strFile = “\SysInfo.txt”

‘These string variables are for the output. MyConf is formatted to write to the screen when the script is finished and show the
‘results of the information collected. MySysInfo is formatted to write the tab-delimited information to the text file.
MyConf = “”
MySysInfo = “”

On Error Resume Next
strComputer = “.” ‘Sets the computer to the local machine

The next line connects to the root\cimv2 namespace, which is where you’ll find the majority of the WMI classes.

‘ WMI connection to Root CIMV2 Namespace
Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)

Now I use the namespace connection to access the Win32_ComputerSystem class, which, among other things, contains the computer name and the name of the logged in user.

‘Gets Username and Computer name from System
Set colItems = objWMIService.ExecQuery(“Select * from Win32_ComputerSystem”)

For Each objItem in colItems
MyUserName = Split(objItem.UserName,”\”)
MyCompName = objItem.Name
Next
‘Add user and computer name to MyConf and MySysInfo
MyConf = MyConf & “Computer Name: ” & MyCompName & VbCr &_
“User Name: ” & MyUserName(1) & VbCr
MySysInfo = MySysInfo & MyCompName & chr(9) & MyUserName(1) & chr(9)

set colItems = nothing

I used the Split() method with the User Name, since Win32_ComputerSystem returns the logged in user name as a path like Machine\Username. I split it at the “\” to get just the username.

The chr(9)’s are the tabs between each item for my tab-delimited text file.

Now I access the Win32_NetworkAdapterConfiguration class to get the IP and MAC address for the computer, which may come in handy.

‘ Gets info from Network Adapter
Set colItems = objWMIService.ExecQuery(_
“SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True”)

‘Get IP and MAC addresses
For Each objItem in colItems
MyIP = objItem.IPAddress(0)
MyMac = objItem.MACAddress
Next
MyConf = MyConf & “IP: ” & MyIP & VbCr & “MAC: ” & MyMac & VbCr & VbCr
MySysInfo = MySysInfo & MyIP & chr(9) & MyMac & chr(9)

set colItems = nothing

Now for some nested loops. I need to get all the active drive letters from each machine, and I also need to know the operating system each drive is running. Since it’s possible that a machine may have multiple OS’s running on partitions, I need to check each drive for its OS. However, the drive information comes from a different class than the OS information, so I need to nest the queries.

‘ Gets info from Physical Disks, e.g. C:\
Set colItems = objWMIService.ExecQuery(“Select * from Win32_LogicalDisk”)
‘ Gets info from Operating System (done separately for nested operation)
Set colItems2 = objWMIService.ExecQuery(“Select * from Win32_OperatingSystem”)

‘Get machine information and add to MyConf and MySysInfo
For Each objItem in colItems
If objItem.DriveType = 3 Then

MyConf = MyConf & “Physical Drives:” & VbCr &_
“==============================” & VbCr & _
“Drive Letter: ” & objItem.Name & vbCr & _
“Size: ” & Int(objItem.Size /1073741824) & ” GB” & vbCr & _
“Free Space: ” & Int(objItem.FreeSpace /1073741824) & _
” GB” & VbCr
MySysInfo = MySysInfo & objItem.Name & chr(9) &_
Int(objItem.Size /1073741824) & ” GB” & chr(9) &_
Int(objItem.FreeSpace /1073741824) & ” GB” & chr(9)

‘Find OS for each drive
For Each objItem2 in colItems2
If objItem2.SystemDrive = objItem.Name then
MyConf = MyConf & “OS: ” & objItem2.Caption & ” – ” & objItem2.Version & VbCr
MySysInfo = MySysInfo & objItem2.Caption & ” – ” & objItem2.Version
end if
Next

MyConf = MyConf & VbCr
MySysInfo = MySysInfo & chr(9)
End If
Next

I got all my info. Now I need to print it to my text file. I will open a File System Object, point it to a path, and have it create the path if it doesn’t exist (I know it exists because I will create it before running the script, but just in case).

‘ Create the File System Object to begin exporting information
Set objFSO = CreateObject(“Scripting.FileSystemObject”)

‘ Check that the strDirectory folder exists
If objFSO.FolderExists(strDirectory) Then
Set objFolder = objFSO.GetFolder(strDirectory)
Else
Set objFolder = objFSO.CreateFolder(strDirectory)
WScript.Echo “Just created ” & strDirectory
End If

If objFSO.FileExists(strDirectory & strFile) Then
Set objFolder = objFSO.GetFolder(strDirectory)
Else
Set objFile = objFSO.CreateTextFile(strDirectory & strFile)
Wscript.Echo “Just created ” & strDirectory & strFile
End If

set objFile = nothing
set objFolder = nothing
‘ OpenTextFile Method needs a Const value
‘ ForAppending = 8 ForReading = 1, ForWriting = 2
Const ForAppending = 8

Set objTextFile = objFSO.OpenTextFile _
(strDirectory & strFile, ForAppending, True)

‘ Writes to the file and closes it
objTextFile.WriteLine(MySysInfo)
objTextFile.Close

MyConf = MyConf & “===============================” & VbCr & “* Computer information has been exported.” & VbCr

Finally, I need to make sure the administrator password is set correctly.

‘ Set objUser = GetObject(“WinNT://” & strComputer & “/Administrator”)
‘ objUser.SetPassword(“1234567”)

‘ The above two lines are commented in case someone decides to copy/paste this script and accidentally changes their administrator password
‘ If you really want this script to set your administrator password, uncomment the two lines above and change “1234567” to whatever you want the password to be (include the quotes)

MyConf = MyConf & “* Administrator Password has been set.”

Now I popup a confirmation screen to indicate the script is over, clean up my variables, and I’m done!

Wscript.Echo MyConf

WSCript.Quit

‘Clean up memory allocation
set objWMIService = nothing
set objItem = nothing
set objItem2 = nothing
set colItems = nothing
set colItems2 = nothing
set objFSO = nothing
set objFolder = nothing
set objShell = nothing
set objTextFile = nothing
set objFile = nothing
set objUser = nothing

Advertisements

Posted in Scripts, VB, WMI | Leave a Comment »

Transfer (export/import) Commodities in UPS WorldShip 11

Posted by IT Intern on July 22, 2009

UPS WorldShip: A very complicated piece of software, and that’s putting it nicely. At my office we have two buildings we send shipments from, and each building has a copy of WorldShip that need to stay in sync. My first encounter with the software was to upgrade both copies from version 10 to 11 and get the negotiated rates updated correctly on both machines. A bit of a wonky process, but not too bad.

About a week later, a ticket comes in asking to have the commodities transferred from one building to the other. After a lengthy and complicated walkthrough with UPS Tech Support (during which even the UPS rep had difficulty… after getting me several steps in, she realized she had it wrong and actually put me on hold while she figured it out), I finally got the commodities transferred and closed the ticket hoping I never have to deal with that software again.

As soon as the UPS rep began “hmm”-ing and “let’s see”-ing on the phone, I grabbed a pen and wrote down every excruciating step. She walked me through the export process and explained the import process, for which I would have to hang up and go to the machine in the other building, and I wrote down everything. I wanted every detail documented in case I (or anyone else) ever had to do this again. I even took my time taking screenshots through the import for my own reference — I wasn’t taking any chances. I wrote up the steps, which turned into three full Word document pages, and now I post them for your reference (in case you are attempting this process yourself), or for your entertainment (in case you don’t have to deal with this software and wish to chuckle at what a pain it is for me and others who do).

UPS Commodities Import/Export Instructions

UPS WorldShip v.11

STEP 1: EXPORT:

Section 1.1: Create an Export Map

In this section you will create a new map for exporting commodities. If you have previously created an export map, you can skip this section.

  • Go to Import/Export Data > Create/Edit Map…
  • Under “New Map” select the radio button “Export data from Worldship” and check “Export CSV” when it becomes enabled.
  • From the dropdown list, select “Commodity
  • In the “Name” field, enter a memorable name for your map and click “Create
  • The “Commodity” tab should be selected; if not, select it and click “Add all columns
  • Select the “SED specific data” tab and click “Add all columns
  • Select the “NAFTA specific data” tab and click “Add all columns
  • As you add columns, the column names will show up on the right side of the window
  • Select the radio button “Overwrite existing file” and check “Include header row
  • Click “OK
  • Click “Save
  • Click “OK

Section 1.2: Export Commodities

In this section you will perform the actual export of the commodities.

  • Go to Import/Export Data > Batch Export…
  • Highlight the map you created in Section 1.1 and click “Next
  • Click “Next
  • Choose a memorable location to save the file and click “Save
  • You have now created a .csv file. You will need a copy of this file to use for importing commodities to the target computer

STEP 2: IMPORT

Section 2.1: Create an Import Map

In this section you will create a new map for importing commodities using the .csv file you created in Section 1.2. (1) If you have previously created an import map, you can skip this section.

  • Go to Import/Export Data > Connection Assistant…
  • Select the radio button “Create a new map for Import” and click “Next
  • Under “Import Data Types” select “Commodity” from the drop down list and click “Next
  • Select the radio button “By File” and click “Browse…
  • Next to “Files of type:” select “Text Files (*.txt, *.csv)” from the drop down list
  • Select the .csv file you created in Section 1.2 (1)
  • Under “Data Source Name (DSN)” enter a memorable and unique name (it must be different than the name you used in Section 1.1)
  • Under “ODBC Drivers” highlight “Microsoft Text Driver (*.txt, *.csv)
  • Click “Next
  • The radio button “New Map” should be selected; if not, select it
  • Under “New Map Name:” enter the unique name you created for the Data Source Name previously
  • Click “Next
  • Click “Finish
  • The “ODBC Text Setup” window will open
  • Click “Options>>
  • Click “Define Format…
  • Select your .csv file from the list
  • Check “Column Name Header
  • Click “Guess
  • The “Columns” field will be populated with the column names from your .csv file
  • Highlight each column name and verify that the “Data Type” is “Char” and the “Width” is 255, making changes as necessary
  • Click “OK
  • If you see an error that says “Failed to save table attributes of (null) into (null).” Click “OK” (this has no effect on the import)
  • Back in the “ODBC Text Setup” window, click “OK
  • Now you are back to the “Edit <mapname> import map” window, where you will manually define the database relationships. You will see “ODBC Tables” on the left with the column names from your .csv file, and “WorldShip Fields” on the right with field names that match your column names. Under “WorldShip Fields” there is a drop down list that includes “Commodities”, “SED specific data”, and “NAFTA specific data”.
    • On the left under “ODBC Tables” drag the “Column Name” column to the right until you can see the entire description of each item. Each item’s name on the left will correspond to the field names under each drop down category on the right. For example, the item on the left called “COMMODITYDESCRIPTIONOFGOODS” corresponds to the item on the right called “Description of goods” under the “Commodity” drop down category.
    • Highlight the first pair of corresponding items and click “Connect.” You will see a matching red symbol next to the paired items when they are connected.
    • Do this for each pair, except for the “COMMODITYPARTNUMBER” item. For this item, select the corresponding items on each side, click “Define Primary Key for Import” (you will see a key symbol show up next to the left item only), then click “Connect.
  • After you have connected all the items, click “OK
  • When it asks “Do you want to Batch Import now with <mapname>?”, click “Yes
  • Click “Next
  • Click “Save
  • You will see the UPS Truck progress bar. When it is finished, click “Save

Section 2.2: Import Commodities

In this section you will perform only an import of the commodities using the map you created in Section 2.1. (1) If you have just done Section 2.1, you have already done a batch import and do not need to do this section.

  • Go to Import/Export Data > Batch Import…
  • Highlight the map you created in Section 2.1 and click “Next
  • In the “Import/Export Preview“ window, click “Next
  • You will see the UPS Truck progress bar, followed by the “Import/Export Summary“ window
  • Click “Save

NOTES

(1) The Import Map includes a reference to the directory and filename of the .csv export file. When using the Import Map to import from a new Export file, be sure the exported .csv is saved with the exact directory and filename referenced in the Import Map. If you are unsure, follow the steps to create a new Import Map.

Posted in UPS WorldShip | Leave a Comment »