VBS Newbie 2

[Appok]

Hello

I've been stuck on a problem for quiet sometime now with a script that i have for a computer inventory. Basically im trying to figure out how i can make a connection to MYSQL server and insert data into the table.

This is the script that i have

'
'Found most parts of this script available at

http://www.tek-tips.com/viewthread.cfm?qid=798632

On Error Resume Next
'Database Connection
Dim mConnection
Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open "Provider=SQLOLEDB.1;Data Source=wic-314;Initial Catalog=Walters","WIC-314\Support",

Set oShell = CreateObject("wscript.Shell")
Set env = oShell.environment("Process")
strComputer = env.Item("Computername")
Const HKEY_LOCAL_MACHINE = &H80000002
Const UnInstPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"
Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_
".\root\default:StdRegProv")

'Get OS Information and insert into Table
report = strComputer & " Computer Inventory" & vbCrLf & "******************************************" & vbCrLf & vbCrLf

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)

report = report & vbCrLf & "******************************************" & vbCrLf
report = report & "Operating System Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf

'Only one OS per execute
For Each objItem in colItems
Set sOSInformation = "Insert INTO OPERATING_SYSTEM (CAPTION) VALUES('" & objitem.Caption & "')"

report = report & "Caption: " & objItem.Caption & vbCrLf
sOSInformation = sOSInformation & "'" & objItem.Caption & "',"

report = report & "Description: " & objItem.Description & vbCrLf
report = report & "InstallDate: " & objItem.InstallDate & vbCrLf
report = report & "Name: " & objItem.Name & vbCrLf
report = report & "Organization: " & objItem.Organization & vbCrLf
report = report & "OSProductSuite: " & objItem.OSProductSuite & vbCrLf
report = report & "RegisteredUser: " & objItem.RegisteredUser & vbCrLf
report = report & "SerialNumber: " & objItem.SerialNumber & vbCrLf
report = report & "ServicePackMajorVersion: " & objItem.ServicePackMajorVersion
report = report & "ServicePackMinorVersion: " & objItem.ServicePackMinorVersion & vbCrLf
report = report & "Version: " & objItem.Version & vbCrLf

report = report & "WindowsDirectory: " & objItem.WindowsDirectory & vbCrLf

sOSInformation = sOSInformation & "'" & objItem.Description & "')"


' call to execute the insert into Opearting_System table
mConnection.Execute sOSInformation

' dont execute more than once
Exit For

'Get Domain Information
Next
Set objWMISvc = GetObject( "winmgmts:\\.\root\cimv2" )
Set colItems = objWMISvc.ExecQuery( "Select * from Win32_ComputerSystem", , 48 )
For Each objItem in colItems
strComputerDomain = objItem.Domain
If objItem.PartOfDomain Then
report = report & "Computer Domain: " & strComputerDomain & vbCrLf
Else
report = report & "Workgroup: " & strComputerDomain & vbCrLf
End If

'Get Hardware Information
Next
report = report & vbCrLf & "******************************************" & vbCrLf
report = report & "Hardware Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_ComputerSystem")

'
For Each objComputer in colSettings
report = report & "System Name: " & objComputer.Name & vbCrLf
report = report & "System Manufacturer: " & objComputer.Manufacturer & vbCrlF
report = report & "System Model: " & objComputer.Model & vbCrLf
next

'Get BIOS Information
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colBIOS = objWMIService.ExecQuery _
("Select * from Win32_BIOS",,48)
For each objBIOS in colBIOS
report = report & "BIOS Version: " & objBIOS.SMBIOSBIOSVersion & vbCrLf
report = report & "Mother Board: " & objBIOS.Manufacturer & vbCrlF
Next

'Get Video Card Information
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colItems = objWMIService.ExecQuery _
("Select * from Win32_VideoController")

For Each objItem in colItems
For Each strCapability in objItem.AcceleratorCapabilities
Next
objItem.CurrentVerticalResolution
report = report & "Video Card: " & objItem.Description & vbCrLf
report = report & "Video Card Driver Version: " & objItem.DriverVersion & vbCrLf
Next

'Get Service Tag Information
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colSMBIOS = objWMIService.ExecQuery ("Select * from Win32_SystemEnclosure")
For Each objSMBIOS in colSMBIOS
report = report & "Service Tag: " & objSMBIOS.SerialNumber & vbCrLf
next

Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_Processor")
report = report
For Each objProcessor in colSettings
report = report & "Processor Information: " & objProcessor.Name & vbCrlF

'Get RAM Information
Next
Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_ComputerSystem")
report = report & vbCrLf & "******************************************" & vbCrLf
report = report & "Memory Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
For Each objComputer in colSettings
'report = report & objComputer.Name & vbcrlf
report = report & objComputer.TotalPhysicalMemory /1024\1024+1 & "MB Total memory" & vbcrlf
Next

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_PhysicalMemory",,48)
For Each objItem in colItems
report = report & "Manufacturer: " & objItem.objItem.Manufacturer & vbCrLf
report = report & "Speed: " & objItem.Speed & vbCrLf
report = report & "Description: " & objItem.Description & vbCrLf
next


'Get Hard Drive information

report = report & vbCrLf & "******************************************" & vbCrLf
report = report & "Disk Drive Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf

Set objWMIService = GetObject("winmgmts:")
Set objLogicalDisk = objWMIService.Get("Win32_LogicalDisk.DeviceID='c:'")
report = report & objLogicalDisk.FreeSpace /1024\1024+1 & "MB Free Disk Space" & vbCrLf
report = report & objLogicalDisk.Size /1024\1024+1 & "MB Total Disk Space" & vbCrLf

'Get Installed Software

software = software & vbCrLf & "******************************************" & vbCrLf
software = software & "Installed Software" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
Set objWMIService = GetObject("winmgmts:\\ " & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colSoftware = objWMIService.ExecQuery ("Select * from Win32_Product")
For Each objSoftware in colSoftware
software = software & "Name: " & objSoftware.Name & vbCrLf
software = software & "Version: " & objSoftware.Version & vbCrLf
software = software & "RegOwner: " & objSoftware.RegOwner & vbCrLf
Next


'Get Network Information
report = report & vbCrLf & "******************************************" & vbCrLf
report = report & "Network Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf

Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled=True")
For Each objItem in colItems
report = report & "Mac Address: " & objItem.MACAddress & vbCrLf
If Not IsNull(objAdapter.IPAddress) Then
For i = 0 To UBound(objAdapter.IPAddress)
report = report & "IP address: " & objItem.IPAddress(i) & vbCrlF
Next
End If
Next

'Get a list of files in My Documents

report = report & vbCrLf & "******************************************" & vbCrLf
report = report & "My Documents" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
Const MY_DOCUMENTS = &H5&

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(MY_DOCUMENTS)
Set objFolderItem = objFolder.Self

Set colItems = objFolder.Items
For Each objItem in colItems
report = report & objItem.Name & vbCrlF
Next

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile (strComputer & ".txt", ForWriting)
ts.write report
ts.write software

Sometimes when i fiddle around with the connection script it will run but there will be no DATA inserted into the Table

my Database name is Walters
my Table name is Operating_System for one of the parts

there are multiple tables for each part of the script

 

[Appok]

Ok by that you mean i do this

'Only one OS per execute
For Each objItem in colItems
sOSInformation = "Insert INTO OPERATING_SYSTEM (Caption) VALUES ('" & objItem.Caption & "')"

'report = report & "Caption: " & objItem.Caption & vbCrLf
'sOSInformation = sOSInformation & "'" & objItem.Caption & "'"

sOSInformation = "Insert INTO OPERATING_SYSTEM (Description) VALUES ('" & objItem.Description & "')"

'report = report & "Description: " & objItem.Description & vbCrLf
'sOSInformation = sOSInformation & "'" & objItem.Description & "',"

and so on and so on

 

[guitarzan]

No, in one INSERT statement like this:

sOSInformation = _
   "Insert INTO OPERATING_SYSTEM (CAPTION, DESCRIPTION) VALUES(" & _
   "'" & [COLOR=blue]objitem.Caption[/color] & "'," & _
   "'" & [COLOR=blue]objItem.Description[/color] & "')"

 

[Appok]

Okay great I'm able to update multiple columns now, but is there a way to prevent it from spamming the same inserts each time i run the script to test it? like i want it to only update the column if that value has been modified.

thanks

 

[Appok]

Im trying to do this

Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_ComputerSystem")
for Each objComputer in colSettings
sHWInformation = "Insert INTO Hardware (System_Name, Manufacturer, Model) VALUES ('" & objComputer.Name & "','" & objComputer.Manufacturer & "','" & objComputer.Model & "')"

'execute query
mConnection.Execute (sHWInformation)

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colBIOS = objWMIService.ExecQuery _
("Select * from Win32_BIOS",,48)
For each objBIOS in colBIOS
sBIOSinformation = "Insert INTO Hardware (BIOS_Version, Manufacturer) VALUES ('" & objBIOS.SMBIOSBIOSVersion & "','" & objBIOS.Manufacturer & "')"

'Execute query
mConnection.Execute (sBIOSinformation)

it will not allow me to combine these section of scripts into 1 Hardware table, i always get an error about "Null value". The only way i can accomplish everything is that each section of my script has its own individual table when its doing a different WIN32_ Class

Maybe i changed the script so much from its original im forgetting a small part

 

[guitarzan]

If you want the second Insert to just update the record you created earlier, and "System_Name" is unique, try an UPDATE instead:

sBIOSinformation = "UPDATE Hardware SET " & _
   "BIOS_Version = '" & objBIOS.SMBIOSBIOSVersion & "', " & _
   "Manufacturer = '" & objBIOS.Manufacturer & "' " & _
   "WHERE System_Name = '" & objComputer.Name & "'"

 

[Appok]

AH excellent! thank you very much for your help people!

 

[Appok]

this is what i have now, i figured out what was wrong with my tables. and now i run the script and it inputs the values in 1 big table, but how do i make it only for 1 line for each computer is my next problem. hah


'
'Found most parts of this script available at

http://www.tek-tips.com/viewthread.cfm?qid=798632

'Database Connection
On Error Resume Next
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)


'SQL Connection
Dim mConnection
Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=WIC-314\Walters"

Set oShell = CreateObject("wscript.Shell")
Set env = oShell.environment("Process")
strComputer = env.Item("Computername")
Const HKEY_LOCAL_MACHINE = &H80000002
Const UnInstPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"
Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_
".\root\default:StdRegProv")

'Get OS Information and insert into Table
report = strComputer & " Computer Inventory" & vbCrLf & "******************************************" & vbCrLf & vbCrLf

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)

report = report & vbCrLf & "******************************************" & vbCrLf
report = report & "Operating System Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf

'Only one OS per execute
For Each objItem in colItems
sOSInformation = "Insert INTO Inventory ([Operating System]) VALUES ('" & objItem.Caption & "')"
'report = report & "Caption: " & objItem.Caption & vbCrLf
'sOSInformation = sOSInformation & "'" & objItem.Caption & "'"


'report = report & "Description: " & objItem.Description & vbCrLf
'sOSInformation = sOSInformation & "'" & objItem.Description & "',"


'report = report & "InstallDate: " & objItem.InstallDate & vbCrLf
'sOSInformation = sOSInformation & "'" & objItem.InstallDate & "',"


'report = report & "Name: " & objItem.Name & vbCrLf
'sOSInformation = sOSInformation & "'" & objItem.Name & vbCrLf "',"


'report = report & "Organization: " & objItem.Organization & vbCrLf
'sOSInformation = sOSInformation & "'" & objItem.Organization & "',"


'report = report & "OSProductSuite: " & objItem.OSProductSuite & vbCrLf
'sOSInformation = sOSInformation & "'" & objItem.OSProductSuite & "',"


'report = report & "RegisteredUser: " & objItem.RegisteredUser & vbCrLf
'sOSInformation = sOSInformation & "'" & objItem.RegisteredUser & "',"

'report = report & "SerialNumber: " & objItem.SerialNumber & vbCrLf
'sOSInformation = sOSInformation & "'" & objItem.SerialNumber & "',"


'report = report & "ServicePackMajorVersion: " & objItem.ServicePackMajorVersion
'sOSInformation = sOSInformation & "'" & objItem.ServicePackMajorVersion & "',"

'report = report & "ServicePackMinorVersion: " & objItem.ServicePackMinorVersion & vbCrLf
'sOSInformation = sOSInformation & "'" & objItem.ServicePackMinorVersion & "',"


'report = report & "Version: " & objItem.Version & vbCrLf
'sOSInformation = sOSInformation & "'" & objItem.Version & "',"

'" report = report & "WindowsDirectory: " & objItem.WindowsDirectory & vbCrLf

'sOSInformation = sOSInformation & "'" & objItem.Description & "'"

' call to execute the insert into Opearting_System table
mConnection.Execute (sOSInformation)

' dont execute more than once
Exit for

'Get Domain Information
Next
Set objWMISvc = GetObject( "winmgmts:\\.\root\cimv2" )
Set colItems = objWMISvc.ExecQuery( "Select * from Win32_ComputerSystem", , 48 )
For Each objItem in colItems
strComputerDomain = objItem.Domain
sDomain = "Insert INTO inventory (Domain) VALUES ('" & strComputerDomain & "')"
If objItem.PartOfDomain Then
report = report & "Computer Domain: " & strComputerDomain & vbCrLf
Else
report = report & "Workgroup: " & strComputerDomain & vbCrLf
End If
'Execute
mConnection.Execute (sDomain)

'Get Hardware Information
Next
report = report & vbCrLf & "******************************************" & vbCrLf
report = report & "Hardware Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_ComputerSystem")
for Each objComputer in colSettings
sHWInformation = "Insert INTO Inventory ([System Name], [System Manufacturer]) VALUES ('" & objComputer.Name & "','" & objComputer.Manufacturer & "')"
report = report & "System Name: " & objComputer.Name & vbCrLf
report = report & "System Manufacturer: " & objComputer.Manufacturer & vbCrlF
report = report & "System Model: " & objComputer.Model & vbCrLf
next
'execute query
mConnection.Execute (sHWInformation)


'Get BIOS Information
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colBIOS = objWMIService.ExecQuery _
("Select * from Win32_BIOS",,48)
For each objBIOS in colBIOS
sBIOSinformation = "Insert INTO inventory ([BIOS Version],[BIOS Manufacturer]) VALUES ('" & objBIOS.SMBIOSBIOSVersion & "','" & objBIOS.Manufacturer & "')"
report = report & "BIOS Version: " & objBIOS.SMBIOSBIOSVersion & vbCrLf
report = report & "Mother Board: " & objBIOS.Manufacturer & vbCrlF
Next

'Execute query
mConnection.Execute (sBIOSinformation)

'Get Video Card Information
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colItems = objWMIService.ExecQuery _
("Select * from Win32_VideoController")

'For Each objItem in colItems
' For Each strCapability in objItem.AcceleratorCapabilities
' Next
' objItem.CurrentVerticalResolution
' report = report & "Video Card: " & objItem.Description & vbCrLf'
' report = report & "Video Card Driver Version: " & objItem.DriverVersion & vbCrLf
' Next

'Get Service Tag Information
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colSMBIOS = objWMIService.ExecQuery ("Select * from Win32_SystemEnclosure")
For Each objSMBIOS in colSMBIOS
sTag= " Insert into Inventory ([Service Tag]) VALUES ('" & objSMBIOS.SerialNumber & "')"
report = report & "Service Tag: " & objSMBIOS.SerialNumber & vbCrLf
Next
'execute
mConnection.Execute (sTag)

Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_Processor")
report = report
For Each objProcessor in colSettings
sCPU = "Insert into inventory (Processor) values ('" & objProcessor.Name & "')"
'report = report & "Processor Information: " & objProcessor.Name & vbCrlF
'execute
mConnection.execute (sCPU)

'Get Total RAM Information
Next
Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_ComputerSystem")
report = report & vbCrLf & "******************************************" & vbCrLf
report = report & "Memory Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
For Each objComputer in colSettings
sMemoryInformation = "Insert into inventory ([Total RAM]) VALUES ('" & objComputer.TotalPhysicalMemory /1024\1024+1 & "')"
'report = report & objComputer.Name & vbcrlf
'report = report & objComputer.TotalPhysicalMemory /1024\1024+1 & "MB Total memory" & vbcrlf
Next
'Execute
mConnection.Execute (sMemoryInformation)

'RAM Speed/Manufacturer/Description Information
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_PhysicalMemory",,48)
For Each objItem in colItems
sMemory = " Insert into inventory ([RAM SPEED]) VALUES ('" & objItem.Speed & "')"
' report = report & "Manufacturer: " & objItem.Manufacturer & vbCrLf
' report = report & "Speed: " & objItem.Speed & vbCrLf
' report = report & "Description: " & objItem.Description & vbCrLf
Next
Execute
mConnection.execute (sMemory)


'Get Hard Drive information
report = report & vbCrLf & "******************************************" & vbCrLf
report = report & "Disk Drive Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf

Set objWMIService = GetObject("winmgmts:")
Set objLogicalDisk = objWMIService.Get("Win32_LogicalDisk.DeviceID='c:'")
sHDD = "Insert INTO HardDrive ([Total Size (MB)]) Values ('" & objLogicalDisk.FreeSpace /1024\1024+1 & "')"
'report = report & objLogicalDisk.FreeSpace /1024\1024+1 & "MB Free Disk Space" & vbCrLf
'report = report & objLogicalDisk.Size /1024\1024+1 & "MB Total Disk Space" & vbCrLf
'Execute
mConnection.execute (sHDD)

'Get Installed Software
software = software & vbCrLf & "******************************************" & vbCrLf
software = software & "Installed Software" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
Set objWMIService = GetObject("winmgmts:\\ " & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colSoftware = objWMIService.ExecQuery ("Select * from Win32_Product")
For Each objSoftware in colSoftware
software = software & "Version: " & objSoftware.Version & vbCrLf
software = software & "RegOwner: " & objSoftware.RegOwner & vbCrLf
Next
'mConnection.execute (sSoftware)


'Get Network IP/MAC Information
report = report & vbCrLf & "******************************************" & vbCrLf
report = report & "Network Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf

Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled=True")
For Each objItem in colItems
report = report & "Mac Address: " & objItem.MACAddress & vbCrLf
If Not IsNull(objAdapter.IPAddress) Then
For i = 0 To UBound(objAdapter.IPAddress)
report = report & "IP address: " & objItem.IPAddress(i) & vbCrlF
Next
End If
Next

'Get a list of files in My Documents

report = report & vbCrLf & "******************************************" & vbCrLf
report = report & "My Documents" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
Const MY_DOCUMENTS = &H5&

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(MY_DOCUMENTS)
Set objFolderItem = objFolder.Self

Set colItems = objFolder.Items
For Each objItem in colItems
report = report & objItem.Name & vbCrlF
Next

'Create a text file with all the information
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile (strComputer & ".txt", ForWriting)
ts.write report
ts.write software

 

[guitarzan]

I showed you the way earlier. Insert a new record the first time, and once the record exists, use update.

Assuming that "System Name" is the key field, you should locate the block of your code that extracts the System Name and inserts it into the database, and move that to the top of your code:

Set colSettings = objWMIService.ExecQuery _
   ("Select * from Win32_ComputerSystem")
for Each objComputer in colSettings
   [COLOR=blue]strSystemName = objComputer.Name[/color]
   sHWInformation = "Insert INTO Inventory ([System Name], [System Manufacturer]) VALUES ('" & objComputer.Name & "','" & objComputer.Manufacturer & "')"
   report = report & "System Name: " & objComputer.Name & vbCrLf
   report = report & "System Manufacturer: " & objComputer.Manufacturer & vbCrlF
   report = report & "System Model: " & objComputer.Model & vbCrLf
next
mConnection.Execute (sHWInformation)

Now, all the other SQL statements relating to the Inventory table should be UPDATE statements, where the system name equals the current sustem name. For example:

sOSInformation = "UPDATE Inventory SET [Operating System] = '" & objItem.Caption & "' WHERE [System Name] = '" & strSystemName & "'"

and

sDomain = "UPDATE Inventory SET Domain = '" & strComputerDomain & "' WHERE [System Name] = '" & strSystemName & "'"

 

[Appok]

The thing with UPDATE is that i need to reconfig the script each time right? i forgot to mention that this script will be a login script for multiple PC's. But i do have alot of progress and i'll keep messing around with it.

I just need a way to get rid of the null values or somehow combine all the objects into 1 big execute so it will go on 1 line across the table.

But thank you guys for all your help!

 

[guitarzan]

I'm not sure what you mean; you could write one script and execute it as a login script. The same script could work on all computers. Here is some pseudocode for one possible way to tackle this:

1. Determine the name of the computer that the script is being run on, store that in strSystemName.

2. Run a SELECT query to see if a record already exists where "[System Name]" is equal to strSystemName. If it does not, use an INSERT to create the record. At this point you know that your database has exactly one record where the [System Name] field is equal to this computer name.

3. One by one, extract information you need for a component, and update that record. An example of that is below.

Run WMI Query on Win32_OperatingSystem, extracting the OS Name.
Run UPDATE query: 
   UPDATE Inventory 
   SET [Operating System] = '" & strOSName & "'"
   WHERE [System Name] = '" & [COLOR=blue]strSystemName[/color] & "'"

Run WMI Query on Win32_ComputerSystem, extract the Domain Name.
Run UPDATE query: 
   UPDATE Inventory 
   SET [Domain] = '" & strComputerDomain & "'"
   WHERE [System Name] = '" & [COLOR=blue]strSystemName[/color] & "'"

etc. etc. etc.

 

[Appok]

HI all i just wanna give an update on my script. I have done pretty much what i wanted to accomplish! aside from the script spamming new lines each time it has run and also i'd like it to just update its values that have change on the users information in the database.

The script all started with this thread

http://www.tek-tips.com/viewthread.cfm?qid=798632

Here is what i been doing to it and it works, i'm able to connect and upload the information to a Database and view all the data with Microsoft excel. Everything gets listed on 1 line.


'
'Found most parts of this script available at

http://www.tek-tips.com/viewthread.cfm?qid=798632

On Error Resume Next
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)

'SQL Database Connection
Dim mConnection
Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open = "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=**Database name goes here**;Data Source=**SQL Server name goes here**; UID=*User Name*; PWD=*Password*"

'extra connections
'"Provider=SQLOLEDB.1;Server=*Server Name*;Database=*database name*;Uid=*user name*; Pwd=*password*;"
'"Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=*Database Name*;Data Source=*SQL server name*; UID=*Username*; PWD=*Password*"

Set oShell = CreateObject("wscript.Shell")
Set env = oShell.environment("Process")
strComputer = env.Item("Computername")
Const HKEY_LOCAL_MACHINE = &H80000002
Const UnInstPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"
Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_
".\root\default:StdRegProv")

'Get OS Information and insert into Table
report = strComputer & " Computer Inventory" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)


For Each objOS in colItems

'report = report & "Caption: " & objItem.Caption & vbCrLf
'report = report & "Description: " & objItem.Description & vbCrLf
'report = report & "InstallDate: " & objItem.InstallDate & vbCrLf
'report = report & "Name: " & objItem.Name & vbCrLf
'report = report & "Organization: " & objItem.Organization & vbCrLf
'report = report & "OSProductSuite: " & objItem.OSProductSuite & vbCrLf
'report = report & "RegisteredUser: " & objItem.RegisteredUser & vbCrLf
'report = report & "SerialNumber: " & objItem.SerialNumber & vbCrLf
'report = report & "ServicePackMajorVersion: " & objItem.ServicePackMajorVersion
'report = report & "ServicePackMinorVersion: " & objItem.ServicePackMinorVersion & vbCrLf
'report = report & "Version: " & objItem.Version & vbCrLf
'report = report & "WindowsDirectory: " & objItem.WindowsDirectory & vbCrLf
exit For
'Get Domain Information
Next
Set objWMISvc = GetObject( "winmgmts:\\.\root\cimv2" )
Set colItems = objWMISvc.ExecQuery( "Select * from Win32_ComputerSystem", , 48 )
For Each objItem in colItems
strComputerDomain = objItem.Domain
If objItem.PartOfDomain Then
'report = report & "Computer Domain: " & strComputerDomain & vbCrLf
Else
'report = report & "Workgroup: " & strComputerDomain & vbCrLf
End If
Exit for

Next

'Get Hardware Information
'report = report & vbCrLf & "******************************************" & vbCrLf
'report = report & "Hardware Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_ComputerSystem")
for Each objComputer in colSettings
'report = report & "System Name: " & objComputer.Name & vbCrLf
'report = report & "System Manufacturer: " & objComputer.Manufacturer & vbCrlF
'report = report & "System Model: " & objComputer.Model & vbCrLf
'report = report & "User:" & objComputer.UserName & vbCrLF
exit for
next

'Get BIOS Information
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colBIOS = objWMIService.ExecQuery _
("Select * from Win32_BIOS",,48)
For each objBIOS in colBIOS
'report = report & "BIOS Version: " & objBIOS.SMBIOSBIOSVersion & vbCrLf
'report = report & "Mother Board: " & objBIOS.Manufacturer & vbCrlF
exit for
Next

'Get Video Card Information
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery ("Select * from Win32_VideoController")
For Each objVC in colItems
exit for
Next
' report = report & "Video Card: " & objVC.Description & vbCrLf'
' report = report & "Video Card Driver Version: " & objVC.DriverVersion & vbCrLf

'Get Service Tag Information
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colSMBIOS = objWMIService.ExecQuery ("Select * from Win32_SystemEnclosure")
For Each objSMBIOS in colSMBIOS
'report = report & "Service Tag: " & objSMBIOS.SerialNumber & vbCrLf
exit for
Next

'Processor Information
Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_Processor")
report = report
For Each objProcessor in colSettings
'report = report & "Processor Information: " & objProcessor.Name & vbCrlF
exit for

'Get Total RAM Information
Next
Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_ComputerSystem")
'report = report & vbCrLf & "******************************************" & vbCrLf
'report = report & "Memory Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
For Each objComputer in colSettings
'report = report & objComputer.Name & vbcrlf
'report = report & objComputer.TotalPhysicalMemory /1024\1024+1 & "MB Total memory" & vbcrlf
exit for
Next

'RAM Speed Information
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_PhysicalMemory",,48)
For Each objItem in colItems
' report = report & "Manufacturer: " & objItem.Manufacturer & vbCrLf
' report = report & "Speed: " & objItem.Speed & vbCrLf
' report = report & "Description: " & objItem.Description & vbCrLf
exit for
Next

'Get Hard Drive information
'report = report & vbCrLf & "******************************************" & vbCrLf
'report = report & "Disk Drive Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
Set objWMIService = GetObject("winmgmts:")
Set objLogicalDisk = objWMIService.Get("Win32_LogicalDisk.DeviceID='c:'")
'report = report & objLogicalDisk.FreeSpace /1024\1024+1 & "MB Free Disk Space" & vbCrLf
'report = report & objLogicalDisk.Size /1024\1024+1 & "MB Total Disk Space" & vbCrLf

'Get Installed Software
'software = software & vbCrLf & "******************************************" & vbCrLf
'software = software & "Installed Software" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
Set objWMIService = GetObject("winmgmts:\\ " & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colSoftware = objWMIService.ExecQuery ("Select * from Win32_Product")
For Each objSoftware in colSoftware
'software = software & "Version: " & objSoftware.Version & vbCrLf
'software = software & "RegOwner: " & objSoftware.RegOwner & vbCrLf
'software = software & "Name: " & objSoftware.Name & vbCrLf
Exit for
Next

'Get Network IP/MAC Information
'report = report & vbCrLf & "******************************************" & vbCrLf
'report = report & "Network Information" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled=True")
For Each objIP in colItems
'report = report & "Mac Address: " & objItem.MACAddress & vbCrLf
If Not IsNull(objAdapter.IPAddress) Then
For i = 0 To UBound(objAdapter.IPAddress)
'report = report & "IP address: " & objItem.IPAddress(i) & vbCrlF
next
End If
exit for
Next

'My Documents Location
Const MY_DOCUMENTS = &H5&
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(MY_DOCUMENTS)
Set objFolderItem = objFolder.Self

sInsert = "Insert into Inventory ([User], [System Name], [System Manufacturer], [System Model],[Operating System], [OS Serial Number], [Domain], [Service Tag], [BIOS Version], [BIOS Manufacturer], [Video Card], [Video Card Driver Version], [HDD Free Space (GB)], [Processor], [IP Address], [MAC], [Total RAM], [RAM SPEED], [My Documents Location])" &_
" VALUES ('"& objComputer.UserName & "','" & objComputer.Name & "','" & objComputer.Manufacturer & "','" & objComputer.Model & "','" & objOS.Caption & "','" & objOS.SerialNumber & "','" & strComputerDomain & "','" & objSMBIOS.SerialNumber & "','" & objBIOS.SMBIOSBIOSVersion & "','" & objBIOS.Manufacturer & "','" & objVC.Description & "','" & objVC.DriverVersion & "','" & objLogicalDisk.FreeSpace /1073741824 & "','" & objProcessor.Name & "','" & objIP.IPAddress(i) & "','" & objIP.MACAddress & "','" & objComputer.TotalPhysicalMemory /1024\1024+1 & "','" & objItem.Speed & "','" & objFolderItem.Path & "')"
mConnection.Execute (sInsert)

I'd thought i'd do my part and help someone out if they needed a quick inventory