SW Inventory to DB Challenge - KiXtart.org

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Basic Scripting » SW Inventory to DB Challenge

Page 1 of 2

Topic Options

#22117 - 2002-05-24 03:55 PM SW Inventory to DB Challenge
CLPowell CLPowell Getting the hang of it Registered: 2000-05-08 Posts: 59 Loc: San Angelo, Texas, USA 76903	;I have 2 scripts that Do basically the same thing with ;differant output types. My challenge is that the first ;script writes to a text file AND lists 36 installed ;programs (on my wksta) AND the second writes to a database AND lists ;only 16 (on same wksta). I am on my third day fighting this one AND I ;turn to the board For help. ;** FIRST SCRIPT WITH TEXT OUTPUT**** $LogDir = "\\fdadmfs1\inventory$$" $WSInfoFile = %TEMP% + "\" + @WkSta + ".ini" Del $WSInfoFile $RC = WriteProfileString("$WSInfoFile","WsInfo","WsName","@WkSta") $RC = WriteProfileString("$WSInfoFile","LogonInfo","Date","@DATE") $RC = WriteProfileString("$WSInfoFile","LogonInfo","Time","@Time") $RC = WriteProfileString("$WSInfoFile","LogonInfo","Logon server","@LServer") $RC = WriteProfileString("$WSInfoFile","UserInfo","UserID","@UserID") $RC = WriteProfileString("$WSInfoFile","UserInfo","Fullname","@FullName") $RC = WriteProfileString("$WSInfoFile","UserInfo","Comment","@Comment") $RC = WriteProfileString("$WSInfoFile","UserInfo","Privilege","@Priv") $num = 1 $RootKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\" Gosub GetProgInfo Shell '%COMSPEC% /C Copy "$WSInfoFile" "$LogDir" >Nul' Return :GetProgInfo $Index = 0 $Key = EnumKey($RootKey, $Index) While @Error = 0 $RC = EnumValue($RootKey + $Key, 1) If @Error = 0 $Value = ReadValue($RootKey + $Key, "DisplayName") If $Value = "" $Value = ReadValue($RootKey + $Key, "QuietDisplayName") If $Value = "" $Value = $Key EndIf EndIf $RC = WriteProfileString("$WSInfoFile", "ProgramInfo", "$Num", "$Value") $Num = $Num + 1 EndIf $Index = $Index + 1 $Key = EnumKey($RootKey, $Index) Loop Return :END ;SECOND SCRIPT WITH DB OUTPUT * ; must have an access mdb named swinv.mdb with the following table and fields ; Table - inventory ; Fields - computername, program, date, time $DBpath = "\\fdadmfs1\inventory$$\swinv.mdb" If Exist("$DBpath") = 0 ? "Database Not Found. Aborting..." Sleep 3 Goto end EndIf $CNstring = "provider=microsoft.jet.oledb.4.0;data source=$DBpath;persist security info=false" $CMDtxt = "select * from Inventory" $cn = CreateObject ("ADODB.Connection") $cmd = CreateObject ("ADODB.Command") $rs = CreateObject ("ADODB.RecordSet") $cn.connectionstring = $CNstring $cn.Open $cmd.activeconnection = $cn $rs.cursortype = 3 $rs.locktype = 3 $rs.activecommand = $cmd $cmd.commandtext = $CMDtxt $rs.Open ($cmd) $RootKeydb = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\" Gosub GetInfo Return :GetInfo $Indexdb = 0 $Keydb = EnumKey($RootKeydb, $Indexdb) While @Error = 0 $RC = EnumValue($RootKeydb + $Keydb, 1) If @Error = 0 $Valuedb = ReadValue($RootKeydb + $Keydb, "DisplayName") If $Valuedb = "" $Valuedb = ReadValue($RootKeydb + $Keydb, "QuietDisplayName") If $Valuedb = "" $Valuedb = $Keydb EndIf EndIf $rs.addnew $rs.fields.item("computername").value = @WKSTA $rs.fields.item("program").value = $valuedb $rs.fields.item("date").value = @DATE $rs.fields.item("time").value = @TIME $rs.update EndIf $Indexdb = $Indexdb + 1 $Keydb = EnumKey($RootKeydb, $Indexdb) Loop $rs.Close Return :END _________________________ Chris Powell Fire Technology Manager
Top

#22118 - 2002-05-24 04:06 PM Re: SW Inventory to DB Challenge
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	If you comment out the database functionality in the second script, do you still get only 16 apps listed? I just tested your script, and they indeed return a different number of applications. The error must be somewhere in your enumeration. Can you rewrite the enumeration as a UDF that returns the list of applications in an array? _________________________ There are two types of vessels, submarines and targets.
Top

#22119 - 2002-05-24 04:41 PM

Re: SW Inventory to DB Challenge

Howard Bullock Offline

KiX Supporter

Registered: 2000-09-15
Posts: 5809
Loc: Harrisburg, PA USA

Try this UDF:

code:

Function GetUninstallInfo()
	dim $Index, $Key, $RC, $Value, $RootKey
	dim $progs[0]
	$Index = 0

	$RootKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"

	$Key = EnumKey($RootKey, $Index)
	While @Error = 0
		ReDim PRESERVE $progs[$Index]
		$RC = EnumValue($RootKey + $Key, 1)
		If @Error = 0
			$Value = ReadValue($RootKey + $Key, "DisplayName")
			If $Value = ""
				$Value = ReadValue($RootKey + $Key, "QuietDisplayName")
				If $Value = ""
					$Value = $Key
				EndIf
			EndIf
		Else
			$Value = $Key
		EndIf
		$progs[$Index] = $Value
		$Index = $Index + 1
		$Key = EnumKey($RootKey, $Index)
	Loop
	$GetUninstallInfo = $progs
EndFunction

$array = GetUninstallInfo()
$i=0
for each $item in $array
	? "$i: $item"
	$i = $i +1
next

Copy/Paste this into "Word", then Copy/Paste from "Word" to your text editor in order to preserve formatting.

[ 24 May 2002, 16:58: Message edited by: Howard Bullock ]

_________________________
Home page: http://www.kixhelp.com/hb/

Top

#22120 - 2002-05-24 04:41 PM Re: SW Inventory to DB Challenge
CLPowell CLPowell Getting the hang of it Registered: 2000-05-08 Posts: 59 Loc: San Angelo, Texas, USA 76903	No it writes the full 36 the enumeration should not be the problem as the same code works for the txt file. I am very weak on array's and UDF's (asking for help). I suspected that there might be a limitation on #records going into access in this manor? [ 24 May 2002, 16:43: Message edited by: CLPowell ] _________________________ Chris Powell Fire Technology Manager
Top

#22121 - 2002-05-24 04:45 PM Re: SW Inventory to DB Challenge
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	Look into the UDF section for the DB* UDFs, they allow you to connect to a database and use pure SQL for updates. I used them to import about 15.000 events into a SQL Server database without any problems. Microsoft Access should be able to handle your script, too. Anyway, the problem is not the Access part but the enumeration in your scripts, they return different results (at least on my computer after removing the .INI file code and the DB code). _________________________ There are two types of vessels, submarines and targets.
Top

#22122 - 2002-05-24 05:01 PM Re: SW Inventory to DB Challenge
Howard Bullock Howard Bullock KiX Supporter Registered: 2000-09-15 Posts: 5809 Loc: Harrisburg, PA USA	If you use the UDF above then you have isolated the information gathering from the data output processes. This will permit you to determine if the Access output was interfering with your enum loop. After commenting out the output code in your original post both scripts output the same 75 products for me on Kix 4.02. Could the be some extended character in one of your key or value name that cause a premature termination of the loop? _________________________ Home page: http://www.kixhelp.com/hb/
Top

#22123 - 2002-05-24 05:20 PM

Re: SW Inventory to DB Challenge

CLPowell Offline

Getting the hang of it

Registered: 2000-05-08
Posts: 59
Loc: San Angelo, Texas, USA 76903

Howard,
I guess I have some more learning to do. I ran your func and got 51 items to screen. I added my
db items to your function and now get 26 to db and 51 to screen. Maybe you could look at code again please.

code:

 $DBpath = "\\fdadmfs1\inventory$$\swinv.mdb"
If Exist("$DBpath") = 0 ? "Database Not Found. Aborting..."
	Sleep 3
	Goto end
EndIf
$CNstring = "provider=microsoft.jet.oledb.4.0;data source=$DBpath;persist security info=false"
$CMDtxt = "select * from Inventory"
$cn = CreateObject ("ADODB.Connection")
$cmd = CreateObject ("ADODB.Command")
$rs = CreateObject ("ADODB.RecordSet")
$cn.connectionstring = $CNstring 
$cn.Open
$cmd.activeconnection = $cn
$rs.cursortype = 3 
$rs.locktype = 3 
$rs.activecommand = $cmd
$cmd.commandtext = $CMDtxt 
$rs.Open ($cmd)
Gosub GetInfo
Return
:GetInfo
Function GetUninstallInfo()
	Dim $Index, $Key, $RC, $Value, $RootKey
	Dim $progs[0]	
	$Index = 0	
	$RootKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"	
	$Key = EnumKey($RootKey, $Index)	
	While @Error = 0
			ReDim PRESERVE $progs[$Index]
			$RC = EnumValue($RootKey + $Key, 1)		
			If @Error = 0
					$Value = ReadValue($RootKey + $Key, "DisplayName")
					If $Value = ""
						$Value = ReadValue($RootKey + $Key, "QuietDisplayName")
						If $Value = ""
							$Value = $Key
						EndIf
					EndIf
				Else
					$Value = $Key
				EndIf		
				$progs[$Index] = $Value		
				$Index = $Index + 1		
				$Key = EnumKey($RootKey, $Index)	
			Loop	
			$GetUninstallInfo = $progs
EndFunction
	$array = GetUninstallInfo()
	$i=0
For Each $item in $array
			$rs.addnew
			$rs.fields.item("computername").value = @WKSTA
			$rs.fields.item("program").value = $item
			$rs.fields.item("date").value = @DATE
			$rs.fields.item("time").value = @TIME
			$rs.update
	? "$i: $item"
	$i = $i +1
Next 
$rs.Close
Return
:END

[ 24 May 2002, 17:44: Message edited by: CLPowell ]

_________________________
Chris Powell
Fire Technology Manager

Top

#22124 - 2002-05-24 05:30 PM Re: SW Inventory to DB Challenge
Howard Bullock Howard Bullock KiX Supporter Registered: 2000-09-15 Posts: 5809 Loc: Harrisburg, PA USA	Please edit your last post and use the "CODE" button. Post your code between the CODE controls. _________________________ Home page: http://www.kixhelp.com/hb/
Top

#22125 - 2002-05-24 05:41 PM

Re: SW Inventory to DB Challenge

Howard Bullock Offline

KiX Supporter

Registered: 2000-09-15
Posts: 5809
Loc: Harrisburg, PA USA

Use this code to determine if you have any error when writing to Access. Change the properties of your DOS window and increase the "Screen Buffer Size" "Height" to 2000 lines. This will permit you to see all of the output.

code:

Function GetUninstallInfo()
	dim $Index, $Key, $RC, $Value, $RootKey
	dim $progs[0]
	$Index = 0

	$RootKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"

	$Key = EnumKey($RootKey, $Index)
	While @Error = 0
		ReDim PRESERVE $progs[$Index]
		$RC = EnumValue($RootKey + $Key, 1)
		If @Error = 0
			$Value = ReadValue($RootKey + $Key, "DisplayName")
			If $Value = ""
				$Value = ReadValue($RootKey + $Key, "QuietDisplayName")
				If $Value = ""
					$Value = $Key
				EndIf
			EndIf
		Else
			$Value = $Key
		EndIf
		$progs[$Index] = $Value
		$Index = $Index + 1
		$Key = EnumKey($RootKey, $Index)
	Loop
	$GetUninstallInfo = $progs
EndFunction


$DBpath = "\\fdadmfs1\inventory$$\swinv.mdb"
If Exist("$DBpath") = 0 ? "Database Not Found. Aborting..."
	Sleep 3
	Goto end
EndIf


$CNstring = "provider=microsoft.jet.oledb.4.0;data source=$DBpath;persist security info=false"
$CMDtxt = "select * from Inventory"
$cn = CreateObject ("ADODB.Connection")
$cmd = CreateObject ("ADODB.Command")
$rs = CreateObject ("ADODB.RecordSet")
$cn.connectionstring = $CNstring 
$cn.Open
$cmd.activeconnection = $cn
$rs.cursortype = 3 
$rs.locktype = 3 
$rs.activecommand = $cmd
$cmd.commandtext = $CMDtxt $rs.Open ($cmd)

$array = GetUninstallInfo()

$i=0
For Each $item in $array
	? ? "$i: $item"
	$rs.addnew
	? "Addnew: @error @serror"
	$rs.fields.item("computername").value = @WKSTA
	? "computername: @error @serror"
	$rs.fields.item("program").value = $item
	? "program: @error @serror"
	$rs.fields.item("date").value = @DATE
	? "date: @error @serror"
	$rs.fields.item("time").value = @TIME
	? "time: @error @serror"
	$rs.update
	? "Update: @error @serror"

	$i = $i +1
Next 

$rs.Close

:END

[ 24 May 2002, 17:45: Message edited by: Howard Bullock ]

_________________________
Home page: http://www.kixhelp.com/hb/

Top

#22126 - 2002-05-24 05:53 PM Re: SW Inventory to DB Challenge
CLPowell CLPowell Getting the hang of it Registered: 2000-05-08 Posts: 59 Loc: San Angelo, Texas, USA 76903	Howard, You are the Man... I had an installed SW that the name was a couple of chr's (53) longer than my field length (50). Corrected length to 150 and it ran like a champ. Thanks for the help _________________________ Chris Powell Fire Technology Manager
Top

#22127 - 2002-05-24 06:03 PM Re: SW Inventory to DB Challenge
Shawn Shawn Administrator Registered: 1999-08-13 Posts: 8611	Howard, aren't those long, drawn-out com messages a pain in a$$ ? This statement, placed at the top of one's script, tends to help a bit: $previousstate = SetOption( "WrapAtEOL", "ON" ) -Shawn
Top

#22128 - 2002-05-24 06:40 PM Re: SW Inventory to DB Challenge
Howard Bullock Howard Bullock KiX Supporter Registered: 2000-09-15 Posts: 5809 Loc: Harrisburg, PA USA	They sure are a pain... thanks for the tip. I have never looked at the SetOption function before. _________________________ Home page: http://www.kixhelp.com/hb/
Top

#22129 - 2002-05-24 06:55 PM Re: SW Inventory to DB Challenge
Howard Bullock Howard Bullock KiX Supporter Registered: 2000-09-15 Posts: 5809 Loc: Harrisburg, PA USA	CLPowell, please note that you originally only reported 36 items from your enumeration and now you report getting 51 items. This was the result of me changing your code and adding an ELSE clause to one of your IF statements. Please review the code below to be sure you wish to keep this modification. While @Error = 0 ReDim PRESERVE $progs[$Index] $RC = EnumValue($RootKey + $Key, 1) If @Error = 0 $Value = ReadValue($RootKey + $Key, "DisplayName") If $Value = "" $Value = ReadValue($RootKey + $Key, "QuietDisplayName") If $Value = "" $Value = $Key EndIf EndIf Else $Value = $Key EndIf $progs[$Index] = $Value $Index = $Index + 1 $Key = EnumKey($RootKey, $Index) Loop [ 24 May 2002, 19:08: Message edited by: Howard Bullock ] _________________________ Home page: http://www.kixhelp.com/hb/
Top

#22130 - 2002-05-24 06:59 PM Re: SW Inventory to DB Challenge
CLPowell CLPowell Getting the hang of it Registered: 2000-05-08 Posts: 59 Loc: San Angelo, Texas, USA 76903	Yes I am keeping change Thanks _________________________ Chris Powell Fire Technology Manager
Top

#22131 - 2002-05-25 01:09 AM Re: SW Inventory to DB Challenge
badboii badboii Fresh Scripter Registered: 2002-03-07 Posts: 32 Loc: Seattle, WA	Can someone help a guy out with "Basic" SQL commands. I want my script to look first if there are any entries in the database with the same computername and if so delete ALL the entries associated with that computername THEN add enter in all the software installed. Thanks!! brian_zamora@hotmail.com _________________________ ========================== Brian Zamora Computer Technician brian_zamora@hotmail.com ==========================
Top

#22132 - 2002-05-25 02:28 AM Re: SW Inventory to DB Challenge
Howard Bullock Howard Bullock KiX Supporter Registered: 2000-09-15 Posts: 5809 Loc: Harrisburg, PA USA	Delete from Tablename where Fieldname = 'ComputerName' Insert TableName (Field1, Field2, Field3) values (1, 'computername', 'software') This is all I can offer without knowing more. _________________________ Home page: http://www.kixhelp.com/hb/
Top

#22133 - 2002-05-25 07:13 PM Re: SW Inventory to DB Challenge
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	code: UPDATE Tablename SET Field1='value1' WHERE Field2='value2' See also http://www.sqlcourse.com/ for a short SQL tutorial or www.highcroft.com/highcroft/sql_intro.pdf for a very comprehensive one (I'm using it myself) _________________________ There are two types of vessels, submarines and targets.
Top

#22134 - 2002-08-07 10:40 PM Re: SW Inventory to DB Challenge
Waltz Waltz Seasoned Scripter Registered: 2002-08-01 Posts: 485 Loc: Waterloo, Ontario, Canada	When I run clpowell's "output to DB script" it breaks at "$cmd.commandtext = $CMDtxt $rs.Open ($cmd)" with @error @serror as follows: -2147352567 COM exception error "Open" (ADODB.Recordset - The connection cannot be used to perform this operation. It is either closed or invalid in this context.) [-2147352567/80020009] A simple test script (using Jen's db udf collection) just to open a conn, open a set, do nothing, and close also fails on "dbrecordopen" with the same @error @serror. (Since I've run a non-related vbs script with output to a DB successfully, I am guessing that my OLE stuff is correct.) In other words, I can output to db using vbs but not with kix... The environment is XP Pro, Access97, Kix410a Any troubleshooting inspiration would be helpful... tia _________________________ We all live in a Yellow Subroutine...
Top

#22135 - 2002-08-07 10:44 PM Re: SW Inventory to DB Challenge
CLPowell CLPowell Getting the hang of it Registered: 2000-05-08 Posts: 59 Loc: San Angelo, Texas, USA 76903	I currently use this code daily on W2K and WP Pro writing to Access 2002 DB on a W2K Server with no errors. Other than that I cannot help much, when I got it working here I refuse to touch or tweak for fear of breaking it. CLPowell _________________________ Chris Powell Fire Technology Manager
Top

#22136 - 2002-08-07 10:46 PM Re: SW Inventory to DB Challenge
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	I would say that your ADODB connection string is not formatted correctly. Please post the small test script that you used. do you have correct database drviers installed ont he computer. You shoudl ahve Microsoft Access database drivers installed under 'Data Sources (ODBC)' _________________________ There are two types of vessels, submarines and targets.
Top

Page 1 of 2

Previous Topic View All Topics

Index Next Topic

Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart

Hop to:

Shout Box

Search the board with:
superb Board Search
or try with google:

	Web kixtart.org