Running ADO Queries from within WinPE

I was asked to figure out how to make a script to do the following:

1. Query WMI for the Win32_BIOS.SerialNumber property
2. Connect to a remote data source using ADO
3. Query a “table” (view, etc.) for matching SerialNumber
4. Return the adjacent “AssetNumber” for the row
5. Build a BIOS/CMOS import file
6. Run the HP BIOS import utility to flash the BIOS with the new ID and other custom settings

However, there’s a few problems I encountered, which led me to a nice solution:

problem 1 – MDAC (and ADO) are not installed in WinPE 3.0 by default.  You have to run the winpe-mdac script to install it.

problem 2 – MDAC in WinPE does not behave exactly the same as it does in a “normal” Windows environment.  Some ADO properties are not supported, some methods do not return the same results, etc.

problem 3 – Security context.  The WinPE session is not part of a domain scope yet, so it’s like trying to connect from a workgroup computer to a domain resource.  It can be done, but you have to keep that in mind, especially if you are accustomed to using UNC path references within DSN-less connections.  Doh!

problem 4 – The MDAC behavior varies by what type of data source you are connecting to.  For example: connecting to SQL Server or LDAP may work fine, but connecting to a TXT, CSV or Excel XLS or XLSX may act “strange”.

Solution: FileSystemObject

Ta Da!!!

Yep.  The good ole FSO works great.  Here’s how:

• Save the data to a standard “flat-file” (I prefer tab-delimited ASCII text)
• Use FSO to read the file
• Parse each line to Split() on tabs
• Check the desired array index (i.e. table column value) and fetch the adjacent index (adjacent column value)

And the bestest best better news?  It’s more efficient anyway:

• The VBscript file for reading a text file is half as big as the same for reading an Excel spreadsheet using ADO.
• For text files up to 1000 lines the performance is nearly identical
• Our text files would rarely exceed 50, since that’s how many they image with multicast at any given time.

The code:

'****************************************************************
' Filename..: sn2computer.vbs
' Author....: skatterbrainz.blogspot.com
' Date......: 10/26/2010
' Purpose...: read comments below
'****************************************************************
' comment: this script is intended to be executed from within a _
' comment: sccm osd task sequence as part of an overall desktop _
' comment: image deployment process. it depends on the task seq _
' comment: for mapping drive Z: to the appropriate UNC share.
'----------------------------------------------------------------

Const strDataFile = "z:\deployments\computers.txt"

'----------------------------------------------------------------
' comment: constants and variable defs
'----------------------------------------------------------------

Const ForReading = 1
Const ForWriting = 2
Dim objFSO, objFile, strLine, arrRow, computerName
Dim objWMI, colItems, objItem, serialNum : serialNum = ""

'----------------------------------------------------------------
' comment: query WMI for BIOS system serial number
'----------------------------------------------------------------

Set objWMI = GetObject("winmgmts:\\.\root\CIMV2") 
Set colItems = objWMI.ExecQuery("SELECT * FROM Win32_BIOS",,48) 
For Each objItem in colItems 
	serialNum = Trim(objItem.SerialNumber)
Next

If serialNum = "" Then
	wscript.Echo "fail: unable to query serial number from BIOS!"
	wscript.Quit(1)
End If

'----------------------------------------------------------------
' comment: look-up computer asset number using serial number
'----------------------------------------------------------------

Set objFSO  = CreateObject("Scripting.FileSystemObject")
On Error Resume Next

wscript.Echo "info: opening data file..."
Set objFile = objFSO.OpenTextFile(strDataFile, ForReading)
If err.Number = 0 Then
	Do Until objFile.AtEndOfStream
		strLine = Trim(objFile.Readline)
		If strLine <> "" Then
			arrRow = Split(strLine, vbTab)
			If arrRow(0) = serialnum Then
				computerName = arrRow(1)
			End If
		End If
	Loop
	objFile.Close
	If computerName <> "" Then
		wscript.Echo "info: computer name found! --> " & computerName
	Else
		wscript.Echo "fail: computer name not found"
	End If
Else
	wscript.Echo "fail: unable to open data file for input"
	wscript.Quit(2)
End If
 
'----------------------------------------------------------------
' comment: open bios import data file and replace computername
'----------------------------------------------------------------

' <<<
' ...code for opening bios data file, replacing name, updating
' file contents and saving file - goes here...
' >>>

'----------------------------------------------------------------
' comment: finish up
'----------------------------------------------------------------

Set objFSO = Nothing

'----------------------------------------------------------------
' comment: sccm osd task sequence takes care of running hp bios _
' comment: import utility after this script is finished running
'----------------------------------------------------------------