Returning all WMI values

[jsneed]

This code is working but it only returns the first value that is queried into the database. How do I make it write all info it finds into the database?

Sub subWriteSoftwareInfo
set colItems = GetObject(strWinMgt).ExecQuery("select * from Win32_Product&quot
objRS.CursorType = iCursorType
objRS.LockType = iLockType
objRS.Source = "tblSoftware"
objRS.ActiveConnection = objCon
objRS.Open
for each objItem in colItems
objRS.AddNew
objRS("intCompID&quot = intCompID
objRS("txtCaption&quot = objItem.Caption
objRS("txtDescription&quot = objItem.Description
objRS("txtName&quot= objItem.Name
objRS("txtVendor&quot= objItem.Vendor
objRS("txtVersion&quot= objItem.Version
objRS.Update

next
objRS.Close
End Sub

 

[jsneed]

nevermind I figured it out

 

[scriverb]

I am not sure about this because this is ADO but I don't see a Set statement for objItem You have set the variable colItems but there is no reference, except in your For Each statement, to objItem.

Let me know. I am a little shaky on ADO yet so maybe I will learn something here.

Bob Scriver

 

[jsneed]

Dim strComputerName ' The Computer Name to be queried via WMI
Dim strWinMgt ' The WMI management String
Dim objCon ' A Connection Object for database connectivity
Dim objRS ' A Recordset Object for database connectivity
Dim sProviderName ' The OLE Provider Type
Dim iCursorType ' The Cursor Type for the Recordset
Dim iLockType ' The Lock Type for the Recordset
Dim sDataSource ' The name and location of the database
Dim intCompID ' A computer ID asssigned when the computer is added to the database
Dim intRam ' The amount of RAM in the computer.

'
' Get the Computer Name to Collect Information For
'
strComputerName = InputBox("Enter the name of the computer you wish to query&quot
strWinMgt = "winmgmts://" & strComputerName &""

'
' Write Computer Info to Database
'

call subConnectionOpen
call subWriteComputerInfo
call subWriteDiskInfo
call subWriteIPInfo
call subWriteSoftwareInfo
call subConnectionClose

wscript.echo "Finished Collection Information for the System."


Sub subConnectionOpen
Set objCon = CreateObject("ADODB.Connection&quot
Set objRS = CreateObject("ADODB.Recordset&quot
sProviderName = "Microsoft.Jet.OLEDB.4.0"
iCursorType = 1
iLockType = 3
sDataSource = "\\d01svr67\desktop\jeff_tools\wmi_database_current\dbassets.mdb"
objCon.Provider = sProviderName
objCon.Properties("Data Source&quot = sDataSource
objCon.Open
End Sub

Sub subConnectionClose
set objRS = nothing
set objCon = nothing
End Sub


Sub subWriteComputerInfo
Set CompSysSet = GetObject(strWinMgt).ExecQuery("select * from Win32_ComputerSystem&quot
for each CompSys in CompSysSet
strDescription = CompSys.Description
strModel = CompSys.Model
strName = CompSys.Name
intRAM = Clng(CompSys.TotalPhysicalMemory/1000000)
next

objRS.CursorType = iCursorType
objRS.LockType = iLockType
objRS.Source = "tblComputer"
objRS.ActiveConnection = objCon
objRS.Open
objRS.AddNew
objRS("strName&quot = strName
objRS("strModel&quot = strModel
objRS("strDescription&quot= strDescription
objRS.Update
intCompID = objRS("ID&quot
objRS.Close

objRS.CursorType = iCursorType
objRS.LockType = iLockType
objRS.Source = "tblMemory"
objRS.ActiveConnection = objCon
objRS.Open
objRS.AddNew
objRS("intCompID&quot = intCompID
objRS("intRAM&quot= intRam
objRS.Update
objRS.Close

End Sub


Sub subWriteDiskInfo
'
' Get Disk Information
'
Set DiskSet = GetObject(strWinMgt).ExecQuery("select FreeSpace,Size,Name from Win32_LogicalDisk where DriveType=3&quot
objRS.CursorType = iCursorType
objRS.LockType = iLockType
objRS.Source = "tblDisk"
objRS.ActiveConnection = objCon
objRS.Open
For each Disk in DiskSet
strDisplayDiskInfo = strDisplayDiskInfo & "Drive " & Disk.Name & " Free Space: " & Clng(Disk.FreeSpace/1000000) & " MB Total Size: " & Clng(Disk.Size/1000000) & " MB"& vbCrlf
if len(Left(disk.name,1)) >0 then
objRS.AddNew
objRS("intCompID&quot = intCompID
objRS("strDriveLetter&quot = Left(disk.name,1)
objRS("strFreeSpace&quot = Clng(Disk.FreeSpace/1000000)
objRS("strTotalSpace&quot= Clng(Disk.Size/1000000)
objRS.Update
end if
Next
objRS.Close
' Wscript.echo strDisplayDiskInfo
End Sub

Sub subWriteIPInfo
set IPConfigSet = GetObject(strWinMgt).ExecQuery("select * from Win32_NetworkAdapterConfiguration where IPEnabled=TRUE&quot
objRS.CursorType = iCursorType
objRS.LockType = iLockType
objRS.Source = "tblIPInfo"
objRS.ActiveConnection = objCon
objRS.Open
for each IPConfig in IPConfigSet
if Not IsNull(IPConfig.IPAddress) then
for i=LBound(IPConfig.IPAddress) to UBound(IPConfig.IPAddress)
' WScript.Echo "IPAddress: " & IPConfig.IPAddress(i) & " Subnet: " &_
' IPConfig.IPSubnet(i) & "MAC Address " & IPConfig.MACAddress
objRS.AddNew
objRS("intCompID&quot = intCompID
objRS("intNICID&quot = i
objRS("txtIPAddress&quot = IPConfig.IPAddress(i)
objRS("txtSubnetMask&quot= IPConfig.IPSubnet(i)
objRS("txtMACAddress&quot= IPConfig.MACAddress
objRS("txtServiceName&quot= IPConfig.ServiceName
objRS("txtDescription&quot= IPConfig.Description
objRS.Update
next
end if
next
objRS.Close
End sub

Sub subWriteSoftwareInfo
Dim rsPackage, sSQL, iPackageID
set colItems = GetObject(strWinMgt).ExecQuery("select * from Win32_Product&quot

for Each objItem In colItems
sSQL = "SELECT * FROM tblPackage WHERE txtName = '" & objItem.Name & "'"
sSQL = sSQL & " AND txtVendor = '" & objItem.Vendor & "'"
sSQL = sSQL & " AND txtVersion = '" & objItem.Version & "'"

Set rsPackage = objCon.Execute(sSQL)

If rsPackage.EOF Then
sSQL = "INSERT INTO tblPackage (txtCaption,txtDescription,txtName,txtVendor,txtVersion) "
sSQL = sSQL & " VALUES ('" & objItem.Caption & "','" & objItem.Description & "','" & objItem.Name & "','" & objItem.Vendor & "','" & objItem.Version & "')"
objCon.Execute sSQL
sSQL = "SELECT max(intPackageID) As intPackageID FROM tblPackage"
Set rsPackage = objCon.Execute(sSQL)
iPackageID = rsPackage("intPackageID&quot
Else
iPackageID = rsPackage("intPackageID&quot
End If
sSQL = "INSERT INTO tblComputerPackage(intCompID, intPackageID) VALUES (" & intCompID & "," & iPackageID & &quot"
objCon.Execute sSQL
Next

rsPackage.Close
End Sub