Hard- und Software Infos pro Rechner per WSH-Script auslesen und in eine Datenbank schreiben

Hallo,

ich habe angefangen mir ein kleines Script zu schreiben welches bestimmte Informationen eines Rechner´s über WMI oder Registry ausliesst und diese Info´s in eine Access DB oder MS-SQL DB wegschreiben bzw. aktualisieren soll. Klappt soweit auch ganz gut bis auf die Aktualisierung der Software pro Rechner. Er sollte mit den jeweiligen Datensatz aus der Datenbank zurückgeben der nicht mit der installierten Software des Rechners übereinstimmt. Vielleicht kann mir jemand von euch weiterhelfen. Hier der bisherige Code:

' Software Inventur DB.vbs
' Beispiel VBScript zum inventarisieren von installierter Software und Hardware eines Rechners und schreiben in Access-DB
' Author Thomas Splett
' Version 1.0 - Dezember 2007
' ------------------------------------------------------'
Const HKLM = &H80000002 'HKEY_LOCAL_MACHINE
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Dim DBDriver
Dim IP
Dim varNeueGuuid
Set WshShell = WScript.CreateObject("WScript.Shell")
Set objCN = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set objADSysInfo = CreateObject("ADSystemInfo")
DBDriver = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\db1.mdb;Uid=Admin;Pwd=;"
'strComputerPfad = objADSysInfo.ComputerName
'Set objComputer = GetObject("LDAP:" & strComputerPfad)
'strComputerName = objComputer.cn
'strComputerDomainGuid = objComputer.objectGUID
'WScript.echo strComputerPfad1
'strPCGUID = OctetToHex(strComputerDomainGuid)
Set wmi = GetObject("winmgmts:.")
guidliste = GetGUIDUUID(wmi)
Timestamp = Date & " " & Time

On Error Resume Next

'Computername ermitteln
strComputerName = WshShell.RegRead("HKLM\System\CurrentControlSet\Control\ComputerName\ComputerName\ComputerName")
'WScript.Echo strComputerName

'Betriebssystem ermitteln
OS = WshShell.RegRead("HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProductName")
If InStr(OS, "Microsoft Windows 2000") > 0 Then
OS = "Windows 2000"
End If
If InStr(OS, "Microsoft Windows XP") > 0 Then
OS = "Windows XP"
End If
'WScript.Echo OS
'WScript.Echo varNeueGuid

'IP-Adresse und MAC-Adresse auslesen
wql = "select * from win32_NetworkAdapterConfiguration where IPEnabled = true"

Set colAdapters = wmi.ExecQuery(wql)

For Each objAdapter In colAdapters
If Not IsNull(objAdapter.DefaultIPGateway) Then

list = list & objAdapter.MACAddress
macliste = Split(list, vbCr)
list = ""

If objAdapter.DHCPEnabled = True Then
list = "DHCP " & Join(objAdapter.IPAddress, "/")
Else
list = list & Join(objAdapter.IPAddress, "/")
End If
ipliste = Split(list, vbCr)
list = ""
IP = Join(ipliste, "/")

varNeueGuuid = "{00000000-0000-0000-0000-" & Join(macliste, "/") & "}"
varNeueGuuid = Replace(varNeueGuuid,":", "")
guidliste = varNeueGuuid
End If
Next
WScript.Echo IP

'Funktionen
Function OctetToHex(bArray)
OctetToHex = ""
For k = 1 To LenB(bArray)
OctetToHex = OctetToHex _
& Right("0" & Hex(AscB(MidB(bArray, k, 1))), 2)
Next
End Function

Function GetGUIDUUID(wmiobj)
On Error Resume Next
wql = "select UUID from Win32_ComputerSystemProduct"
Set ergebnis = wmiobj.ExecQuery(wql)

For Each guid In ergebnis
list = list & guid.UUID
Next

If Len(list)>0 Then
list = Left(list, Len(list)-1)
End If

If InStr(list, "00000000-0000-0000-0000-000000000000") Or InStr(list, "FFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF") Then
varNeueGuuid = "00000000-0000-0000-0000-" & Join(macliste, "/")
varNeueGuuid = Replace(varNeueGuuid,":", "")
Else
varNeueGuuid = list
End If
On Error Goto 0
End Function
WScript.Echo varNeueGuuid

'PC Daten auslesen und in DB schreiben
Set objWMIService = GetObject("winmgmts:\\" & strComputerName & "\root\cimv2")
Set colInfos = objWMIService.ExecQuery("SELECT * from Win32_ComputerSystemProduct",,48)
Set colRAM = objWMIService.ExecQuery("SELECT TotalVisibleMemorySize FROM Win32_OperatingSystem")
Set colCPU = objWMIService.ExecQuery("SELECT * FROM Win32_Processor")
Set colHDD = objWMIService.ExecQuery("SELECT * FROM Win32_DiskDrive")
For Each objInfos In colInfos
modell=objInfos.Vendor
If modell <> "" Then
modell = objInfos.Vendor & " " & objInfos.Version & " " & objInfos.Name
Else
modell="Unbekannt"
End If
serial=objInfos.IdentifyingNumber
If serial <> "" Then
serial = objInfos.IdentifyingNumber
Else
serial="0"
End If
Next
'Modell-Eintrag fomatieren
If InStr(modell, "") Then
modell = Replace(modell," ", " ")
Else
modell = modell
End If

If InStr(modell, "") Then
modell = Replace(modell," ", " ")
Else
modell = modell
End If
WScript.Echo modell

For Each Raminfo In colRAM
RAM=Raminfo.TotalVisibleMemorySize
RAM = Round(RAM / 1024 + 1 ) & " MB"
'RAM = "2046 MB"
Next


For Each CPUinfo In colCPU
CPU = CPUinfo.Name & " " & CPUinfo.MaxClockSpeed & " MHz"
Next

'CPU-Eintrag formatieren
If InStr(CPU, "") Then
CPU = Replace(CPU," ", "")
Else
CPU = CPU
End If

For Each HDDinfo In colHDD
HDD=HDDinfo.Size
HDD=Round(HDD / 1073741824, 1) & " GB"
list=list & HDD & vbCr
HDD = list
Next

'Rechner löschen und somit auch die installierte Software zum neueintragen
'objCN.Open DBDriver
'objRecordSet.Open("SELECT * FROM Hauptdaten"), objCN, adOpenStatic, adLockOptimistic
'strSearchCriteria = "Computername = '" & strComputerName & "'"
'objRecordSet.Find strSearchCriteria
'If objRecordSet.EOF Then

'Else
'objRecordSet.Delete
'objRecordSet.Close
'End If
'objCN.Close

'Hauptdaten erstellen/ändern
objCN.Open DBDriver
objRecordSet.Open("SELECT * FROM Hauptdaten"), objCN, adOpenStatic, adLockOptimistic
strSearchCriteria = "Computername = '" & strComputerName & "'"
objRecordSet.Find strSearchCriteria
If objRecordSet.EOF Then
objRecordSet.AddNew
objRecordSet("Computername") = strComputerName
objRecordSet("IP") = IP
objRecordSet("SN") = serial
objRecordSet("Modell") = modell
objRecordSet("CPU") = CPU
objRecordSet("HDD") = HDD
objRecordSet("RAM") = RAM
objRecordSet("OS") = OS
'objRecordSet("Domain-SID") = strPCGUID
objRecordSet("RIS-GUID") = varNeueGuuid
objRecordSet("Createt") = Date & " " & Time
objRecordSet.Update
objRecordSet.Close
objCN.Close
Else
objCN.Open DBDriver
objRecordSet.Open("SELECT Hauptdaten.IP, Hauptdaten.CPU, Hauptaten.HDD, Hauptdaten.RAM, Hauptdaten.OS, Hauptdaten.RISGUID, Hauptdaten.Modified FROM Hauptdaten WHERE Hauptdaten.Computername='"&strComputerName&"'"), objCN, adOpenStatic, adLockOptimistic
'objRecordSet.Open("SELECT * FROM Hauptdaten WHERE Hauptdaten.Computername='"&strComputerName&"'"), objCN, adOpenStatic, adLockOptimistic
WScript.Echo "Rechner in Datenbank gefunden Update!"
objRecordSet("IP") = IP
objRecordSet("CPU") = CPU
objRecordSet("HDD") = HDD
objRecordSet("RAM") = RAM
objRecordSet("OS") = OS
objRecordSet("RIS-GUID") = varNeueGuuid
objRecordSet("Modified") = Date & " " & Time
objRecordSet.Update
objRecordSet.Close
objCN.Close
End If
objCN.Close

'installierte Software auslesen/eintragen/ändern
strKey = "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"
strEntry1a = "DisplayName"
strEntry1b = "QuietDisplayName"
strEntry2 = "InstallDate"
strEntry3 = "VersionMajor"
strEntry4 = "VersionMinor"
strEntry5 = "EstimatedSize"

Set objReg = GetObject("winmgmts://" & strComputerName & "/root/default:StdRegProv")
objReg.EnumKey HKLM, strKey, arrSubkeys
' Jeden Subkey einmal anspringen (sofern vorhanden)
For Each strSubkey In arrSubkeys

If InStr(strSubKey, "KB") Or InStr(strSubKey, "Framework")Then 'zu ausschliessen der Windows Updates aus der DB
strSubkey = "0"
End If

intRet1 = objReg.GetStringValue(HKLM, strKey & strSubkey, strEntry1a, strValue1)

If intRet1 <> 0 Then
objReg.GetStringValue HKLM, strKey & strSubkey, strEntry1b, strValue1
End If

'Hautpsoftware eintragen
If strValue1 <> "" Then
Display_Name = strValue1
End If

objReg.GetStringValue HKLM, strKey & strSubkey, strEntry2, strValue2

If strValue2 <> "" Then
Install_Date = strValue2
End If

objReg.GetDWORDValue HKLM, strKey & strSubkey, strEntry3, intValue3

objReg.GetDWORDValue HKLM, strKey & strSubkey, strEntry4, intValue4

If intValue3 <> "" Then
Version = intValue3 & "." & intValue4
End If

objReg.GetDWORDValue HKLM, strKey & strSubkey, strEntry5, intValue5

If intValue5 <> "" Then
Estimated_Size = Round(intValue5 / 1024, 2)
End If


objCN.Open DBDriver
objRecordSet.Open("SELECT * FROM Software"), objCN, adOpenStatic, adLockOptimistic
strSearchCriteria1 = "Software = '" & Display_Name & "'"
objRecordSet.Find strSearchCriteria1
If objRecordSet.EOF Then
objRecordSet.AddNew
objRecordSet("Software") = Display_Name
objRecordSet("Version") = Version
objRecordSet("Createt") = Date & " " & Time
objRecordSet.Update
objRecordSet.Close
Else
'WScript.Echo "Software in Hauptsoftware gefunden!"
'objRecordSet("Software") = Display_Name
'objRecordSet("Version") = Version
'objRecordSet("Createt") = Date & " " & Time
'objRecordSet.Update
'objRecordSet.Close
End If
objCN.Close

'Software je Rechner eintragen
objCN.Open DBDriver
objRecordSet.Open("SELECT * FROM PC_SW_Temp"), objCN, adOpenStatic, adLockOptimistic
strSearchCriteria2 = "Software = '" & Display_Name & "'"
objRecordSet.Find strSearchCriteria2
If objRecordSet.EOF Then
objRecordSet.AddNew
objRecordSet("Computername") = strComputerName
objRecordSet("Software") = Display_Name
objRecordSet("Install_Date") = Install_Date
objRecordSet("Size") = Estimated_Size
objRecordSet("Createt_Modified") = Date & " " & Time
objRecordSet.Update
objRecordSet.Close
End If
objCN.Close

'Software je Rechner aktuallisieren
objCN.Open DBDriver
objRecordSet.Open("SELECT * FROM PC_SW_Temp WHERE PC_SW_Temp.Computername='" & strComputerName & "'"), objCN, adOpenStatic, adLockOptimistic
strSearchCriteria2 = "Software <> '" & Display_Name & "'"
objRecordSet.Find strSearchCriteria2
If objRecordSet.BOF Then
WScript.Echo objRecordSet("Software") & " und " & Display_Name
End If
objCN.Close
Next


WScript.Echo "ich habe fertig!"

Falls jemand die Datenbank braucht werde ich sie im natürlich zukommen lassen. Danke
Gruß
Thomas

sql = "DELETE FROM dbo.SOFTWARE WHERE VER_ID IN ("  
sql = sql & "SELECT SOFTWARE.VER_ID "  
sql = sql & "FROM dbo.INS_VER_LNK "  
sql = sql & "RIGHT JOIN dbo.SOFTWARE ON dbo.INS_VER_LNK.VER_ID = dbo.SOFTWARE.VER_ID "  
sql = sql & "LEFT JOIN dbo.INSTALLATION ON dbo.INS_VER_LNK.INS_ID = dbo.INSTALLATION.INS_ID "  
sql = sql & "WHERE INSTALLATION.INS_ID Is Null )"  

db.Execute sql