VBS script to SQL table

[Sabotage23]

Hi all

i put together a VBS inventory script that can be run as a login script and output the information to a text file.

But i would like to upload the information to a SQL Table that i have created, im new to VBS and SQL and im learning along the way.

So far what i have will update the table but i get into some problems for example


'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 Hardware (System_Name, Manufacturer, Model) VALUES ('" & objComputer.Name & "','" & objComputer.Manufacturer & "','" & objComputer.Model & "')"
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)

but when i try to do this in the same table

'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 Hardware (BIOS_Version, 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)

i get errors it is like if i cannot do 2 inserts in one table for different values. Is this even possible? will a UPDATE table work instead ???

Thanks for your help

 

[Wiseguy2001]

Wow, you're still on this?

It's impossible to say what the problem is with out seeing how the table is setup. If you're using SQL management studio, right click on the table and select script table as/ create to, then post the code here. I have a hunch what is going wrong here, but I don't think the table is setup right to start with.

Also, please can you post the exact error message? These are really important in troubleshooting.

 

[Sabotage23]

Yes im still on this but almost near the end.


'
'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=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 OPERATING_SYSTEM (Caption, [Serial Number], Version) VALUES ('" & objItem.Caption & "','" & objItem.SerialNumber & "','" & objItem.Version & "')"
'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 Domains ([Computer 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 Hardware ([System Name], [System Manufacturer], Model) VALUES ('" & objComputer.Name & "','" & objComputer.Manufacturer & "','" & objComputer.Model & "')"
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 BIOS ([BIOS Version],[Motherboard 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
report = report & "Service Tag: " & objSMBIOS.SerialNumber & vbCrLf
next

Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_Processor")
report = report
For Each objProcessor in colSettings
sCPU = "Insert into CPU ([Processor Information]) 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 Memory ([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 Memory ([RAM Speed], [RAM Manfacturer]) VALUES ('" & objItem.Speed & "','" & objItem.Manufacturer & "')"
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

Error message = line 169 char 2
Cannot insert the value NULL into column 'RAM Speed', table 'Test.dbo.Memory'; column does not allow nulls. INSERT fails.

I'm trying to do 2 different WMI classes and integrate them into 1 table by doing it with the script

The way my tables are setup i just create a new table and input my column names and deselect allow nulls and set the char values

 

[Sabotage23]

USE [Test]
GO

/****** Object: Table [dbo].[Memory] Script Date: 25/06/2012 10:36:35 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Memory](
[Total RAM] [nvarchar](max) NOT NULL,
[RAM Speed] [nvarchar](max) NOT NULL,
[RAM Manufacturer] [nvarchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


**but if i make tables for each section of different Win32 classes and Insert all the values to the columns it works for the most part (aside from the video card and network info )

 

[renixinq]

The error pretty much tells you what is wrong. On your memory table you have speed set as not null which means some value has to be specified to be inserted. Quick fix is set it to allow nulls then query the table for nulls to find the offenders and troubleshot them.

 

[Sabotage23]

Ok great!

I also have another question, is there a way i can stop spamming inserts in the SQL tables every time i run the script? i'd like to only have 1 line for each table and if the value changes it updates that 1 line.

Been trying to search for that for a bit now on google and i don't think UPDATE is what i need

Thanks

 

[Sabotage23]

I finally was able to make all my insert into 1 table! my issue was the tables! apperently when i created my tables (Right click - new table) it must of had some blocking issues. But i read how to create a table with New query and bingo! everything worked!

But now i have a bunch of null values showing up which i need to get rid of hehe

 

[mikeblas]

Sounds like you're stumbling your way to success.

 

[Sabotage23]

Sounds like you're stumbling your way to success.

better then not moving

 

[Sabotage23]

I have accomplished my goal, i am able to run the script and its able to write the information to my SQL database and i am also able to use Excel to create a connection that will display the table nicely.

 

[Wiseguy2001]

Nice one, you certainly showed some determination. Was you boss impressed?

 

[Sabotage23]

I showed him what i was doing on my laptop, he liked what he saw. Now i am in the transition of backing up the Database and importing it onto his SQL server

I just need to now figure out how to overwrite existing data for a specific user, and also do a loop for installed software.

And i believe that will be it finally, i learned alot about SQL and VBS heh

 

[Kataki]

for updating existing data:

update <table> set <field1> = new_value where <user_field> = specific user

Good luck mate, cheers!