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

there might be parts that i have tried to insert but was unsuccessful

first and foremost is you should remove on error resume next. This command hides ALL errors. If errors are hiddens how can you possibly dettermine whats wrong? On error resume next should the LAST thing you implement - only when your script runs flawlesly.

second, set is used to create objects. sOSInformation is a string.
So because you have on error resume next, you are not seeing the error that occurs when you try to set a string to an object or when the execute the object as a string:

Number one priority is to remove on error resume next. Peices will start to fall into place once you do

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

Ok i removed the on error resume to next, which should have been gone in the first place but i dont know why i kept it lol, noobie mistake :(. Now i run the script and it all comes down to my connection, I am getting an error that says "[DBNETLIB][ConnectionOpen (Connect()).SQL Server does not exist or access is denied.


Oh and thank you for your help! much appreciated!

Hmmm it seems im having the issue with the authenication portion of the connection. My login credentials when i use MYSQL Database Mangement will not work in that string. Is there a way to have no authenication in the string and allow it to be open for now to see if it works?

Have a look here:
http://www.connectionstrings.com/mysql

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Hmm im creeping closer and closer to finally getting a connection.


mConnection.Open = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=Walters; User=******;Password=****;Option=3;"

what i am using but i get [MYSQL][ODBC5.1 Driver]Can't connect to Mysql server on 'localhost' (10061)

and i have a feeling its the MySQL ODBC Driver that i installed but when i go into System DSN and add a MySQL ODBC 5.1 i dont know my servers IP address or know how to add a named piped.

I did go into the 32bit driver and added a SQL server connection but still will not connect

(Using a 64bit laptop)

mConnection.Open = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=WIC-314\SQLEXPRESS"

when i ran this connection it went through an gave me another error about incorrect syntex on line 54. so i went to check the Database to see if other data was inserted and im now unable to connect to my own database with the server management tool... lol.... awesome

OR, you could use Spiceworks to do this for you. It's free, and does other stuff too.

No, I don't work for them...

JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)

Yeah i know there is already stuff out there that is automated but i wanna learn how to do this and so far what i got makes me feel proud of myself :D

I think i got my connection to the SQL server cause now when it is trying to insert the caption i believe, i get an error "Incorrect syntax near 'Microsoft Windows 7 Professional'

You have, I think, come up with the best possible reason to stick with the VB scripty option. Go to it!

Do you know which line in your code is producing the error?

JJ
Variables won't. Constants aren't
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)

I believe it is this one

mConnection.Execute sOSInformation

which doesn't really make any sense on why it would be that causing an syntax error

but the error says line 56 char 2 which leads me to that piece of line of execution.

Looking only at the assignments for sOSInformation, the SQL generated is incorrect.

Either remove the second two lines, or adjust the first line if you want to include "DESCRIPTION" in the sql insert.

'Found most parts of this script available at http://www.tek-tips.com/viewthread.cfm?qid=798632

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

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 OPERATING_SYSTEM (Caption) VALUES ('" & objItem.Caption & "')"

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

'report = report & "Description: " & objItem.Description & vbCrLf
'sOSInformation = sOSInformation & "'" & objItem.Description & vbCrLf "',"
'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)


I'm getting a syntax error when its trying to insert "Windows 7 Professional" in the caption value.

You missed one:

For Each objItem in colItems
sOSInformation = "Insert INTO OPERATING_SYSTEM (Caption) VALUES ('" & objItem.Caption & "')"

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

holy smokes removing that line finally was able to insert the data into the table! now to try for all the other lines! ha

Thank you thank you thank you

Another issue has occurred each time i run the script it inserts multiple values each time, i like it to over write that value instead and when it processes the second value into the second column nothing shows only nulls. I'm sure messing around with it i'll get it. But so far its great to finally get data inserted heh

Use only one INSERT instruction for all the columns.

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

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

No, in one INSERT statement like this:

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

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

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

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

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

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:


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:

and

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!

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.

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