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]

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

 

[Geates]

first and foremost is you should remove [tt]on error resume next[/tt]. 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, [tt]set[/tt] is used to create objects. sOSInformation is a string.

[s]Set[/s] sOSInformation = "Insert INTO OPERATING_SYSTEM (CAPTION) VALUES('" & objitem.Caption & "')"

So because you have on error resume next, you are not seeing the error that occurs when you try to [tt]set[/tt] a string to an object or when the execute the object as a string:

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

Number one priority is to remove [tt]on error resume next[/tt]. 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

 

[Appok]

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!

 

[Appok]

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?

 

[PHV]

Have a look here:

http://www.connectionstrings.com/mysql

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Appok]

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.

 

[Appok]

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

(Using a 64bit laptop)

 

[Appok]

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

 

[JPJeffery]

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
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]

 

[Appok]

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

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'

 

[JPJeffery]

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
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]

 

[Appok]

I believe it is this one

mConnection.Execute sOSInformation

 

[Appok]

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.

 

[guitarzan]

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

sOSInformation = "Insert INTO OPERATING_SYSTEM (CAPTION) VALUES('" & objitem.Caption & "')"
sOSInformation = sOSInformation & "'" & objItem.Caption & "',"
sOSInformation = sOSInformation & "'" & objItem.Description & "')"

mConnection.Execute sOSInformation

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

 

[Appok]

'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.

 

[guitarzan]

You missed one:

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

'report = report & "Caption: " & objItem.Caption & vbCrLf
[highlight]sOSInformation = sOSInformation & "'" & objItem.Caption & "'"[/highlight]

 

[Appok]

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

 

[Appok]

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

 

[PHV]

Use only one INSERT instruction for all the columns.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886