Page 1 of 2 12>
Topic Options
#69653 - 2002-09-05 11:11 PM Softwhere? - Solution to software inventory data
Vig Offline
Starting to like KiXtart

Registered: 2001-11-14
Posts: 166
Loc: Saudi Arabia
At long last I think I'm ready to post my solution to inventorying/viewing installed software across a network.

I'm using 4.10, 4.02, and KixForms 2.0.3

There are three phases to the process.

First the data collection which is done by the SoftwareUDF.kix script. It's in the form of a UDF so it can be used for "scanning" remote computers. It hangs when it cannot connect to the remote host so when I use it I work a ping in there to test network connectivity.

Example:
Computer($computer,$logpath)

The $computer var should not contain "\\" and the $logpath should end in a "\".
code:
Function Software($Computer,$LogPath)
Dim $programs
Dim $Subkeys
$index = 0
While @error = 0
ReDim preserve $subkeys[Ubound($subkeys)+1]
$index = $index+1 ?$computer
$subkeys[Ubound($subkeys)] = EnumKey("\\$Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall",$index)
Loop
$nul = ReDirectOutput ("$logpath$computer.log",1)
For Each $key in $subkeys
$Displayname = ReadValue ("\\$Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\$key","Displayname")
If $Displayname <> ""
ReDim preserve $programs[Ubound ($programs)+1]
$programs[Ubound($programs)] = $Displayname
EndIf
Next
join($programs,"~*~")
$nul = ReDirectOutput("")
EndFunction

Second phase is parsing all of the .log files obtained from the first script and adding the data to a microsoft access data base.

You'll need to create an access database with one table called TBL_PROGRAM. In the table there should be two columns, first "Computer" second "Program".

Since I'm no expert on COM scripting, this script will only work with 4.02. Props to Breaker since I only modified his script. I should also add that any program name with a ' in it will have it striped out.

$Loggpath should point to the directory where the .dat files are located (should end in "\"). $DBPATH should point to where the data base is located (should end in "\"). $DBFile should be the name of your data base file.

code:
$Loggpath = "H:\loggs\"
$DBPath = "H:\loggs\"
$DBFile = "inventory.mdb"

$RC=SetOption("WrapAtEOL","On")
$DSN="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBPath$DBFile"
$Connection = CreateObject("ADODB.Connection")
$Command = CreateObject("ADODB.Command")
$Recordset = CreateObject("ADODB.Recordset")
If $Connection
? "Connected"
$Connection.ConnectionString = $DSN
$Connection.Open()
$Command.ActiveConnection = $Connection
$Recordset.CursorType = 3
$Recordset.LockType = 3
$Recordset.ActiveCommand = $Command
$file = Dir("$loggPath*.log")
While $file <> "" AND @error = 0
$computer = SubStr($file,1,InStr($file,".log")-1)
$nul = Open(1,"$loggpath$file") If @error <> 0 ?@serror Exit EndIf
$ProgArray = ReadLine(1) If @error <> 0 ?@serror Gosub End EndIf
$ProgArray = Split($ProgArray,"~*~")
For Each $program in $ProgArray
If InStr($Program,"'") <> 0 $A = Split($Program,"'") For Each $B in $A $C=$C+$B Next $Program = $C $C="" EndIf
$CHECK_ENTRY_PROGRAM = "SELECT * FROM TBL_PROGRAM WHERE COMPUTER='$computer' AND PROGRAM='$program';"
$Command.CommandText = $CHECK_ENTRY_PROGRAM
$Recordset.Open($Command)
If $Recordset.RecordCount < 1
$Recordset.AddNew If @ERROR <> 0 ?@SERROR EndIf
$Recordset.Fields("Computer").Value = "$computer" If @ERROR <> 0 ?@SERROR EndIf
$Recordset.Fields("Program").Value = "$program" If @ERROR <> 0 ?@SERROR EndIf
EndIf
$Recordset.Update
$Recordset.Close()
Next
?"$computer Completed Successfully"
:End
$nul=Close(1)
$file = Dir()
Loop
$Connection.Close()
$Connection = 0
$Recordset = 0
$Command = 0
Else
Goto error
EndIf
:end
?$numarr
Exit 321
:error
Exit

Last phase is the feedback form. Basically there’s two ways to view the information in the data base. First is a list of all computers in the DB, as you select a computer name it shows what software is installed on that computer. Second is list of all software on the computers you collected data from. As you select a software name it returns a list of computers that software is installed on.

Since the current release of KixForm does not automatically sort the listbox, the software listbox is a bit of a mess. I'm hoping the KixForm 2.0.4 release will include the Sorted property which will make the software list much easier to navigate through. I could use one of the sort array UDF's, but that will dramatically lengthen the time between clicking on "Search for Software by Computer" and the list popping up. Because this process is already lengthy (longer depending on how many computers you collected data from) I've added a progress bar so you don't think the script is hanging.

$DBPath should point to where you data base is located (should end in "\"). $DBFile is the name of your data base.

code:
Break on

Dim $SearArr
$DBPath = "h:\loggs\"
$DBFile = "inventory.mdb"

$FORM = CreateObject("Kixtart.FORM")

$FORM.CAPTION = "Software Inventory"
$FORM.HEIGHT = 200
$FORM.WIDTH = 210
$FORM.FONTNAME = "Arial"
$FORM.FONTSIZE = 9
$FORM.CENTER

$SoftButton = $FORM.CommandButton("Search for Software by Computer",3,3,$FORM.Width-12,$FORM.Height/2-34)
$CompButton = $FORM.CommandButton("Search for Computer by Software",3,$SoftButton.Height+7,$FORM.Width-12,$FORM.Height/2-34)
$QuitButton = $FORM.CommandButton("Quit",3,$CompButton.Top + $CompButton.Height + 4,$FORM.Width-12,30)
$SoftButton.OnClick = '$$CompForm.Show if ubound($$ReturnData) = -1 $$ReturnData=GetDBData() CompForm($$ReturnData) endif'
$CompButton.OnClick = '$$SoftForm.Show if ubound($$ReturnData2) = -1 $$ReturnData2=GetDBData() SoftForm($$ReturnData2) endif'
$QuitButton.OnClick = 'Quit()'


$CompForm = CreateObject("Kixtart.FORM")
$CompForm.CAPTION = "Search for Software by Computer"
$CompForm.HEIGHT = 437
$CompForm.WIDTH = 337
$CompForm.FONTNAME = "Arial"
$CompForm.FONTSIZE = 9
$CompForm.CENTER
$FraComp = $CompForm.Frame("",5,5,320,402)
$SelCompTxt = $FraComp.Label("Select a Computer:",10,20,150,20)
$SelCompBox = $FraComp.ListBox(,10,40,150,150)
$InsProTxt = $FraComp.Label("Installed Programs:",10,200,150,20)
$InsProBox = $FraComp.ListBox(,10,220,300,150)
$ExitButton = $FraComp.CommandButton("Exit",165,40,150-2,30)
$ProgComp = $FraComp.ProgressBar(,10,375,300,20)
$ProgComp.Min = 0
$ExitButton.OnClick = '$$CompForm.Show(0)'

$SoftForm = CreateObject("Kixtart.FORM")
$SoftForm.CAPTION = "Search for Computer by Software"
$SoftForm.HEIGHT = 437
$SoftForm.WIDTH = 337
$SoftForm.FONTNAME = "Arial"
$SoftForm.FONTSIZE = 9
$SoftForm.CENTER
$FraSoft = $SoftForm.Frame("",5,5,320,402)
$SelProgTxt = $FraSoft.Label("Select Software:",10,20,150,20)
$SelProBox = $FraSoft.ListBox(,10,40,300,150)
$InsCompTxt = $FraSoft.Label("Installed on this Computer:",10,200,150,20)
$InsCompBox = $FraSoft.ListBox(,10,220,150,150)
$ExitButton2 = $FraSoft.CommandButton("Exit",165,220,150-2,30)
$ProgSoft = $FraSoft.ProgressBar(,10,375,300,20)
$ExitButton2.OnClick = '$$SoftForm.Show(0)'

$FORM.Show
$Form.SetFocus

While $FORM.Visible OR $CompForm.Visible OR $SoftForm.Visible
$=Execute($FORM.DoEvents)
Loop

Function CompForm($ReturnData)
$1=1
$0=0
$CompForm.SetFocus
$ProgComp.Max = Val(Ubound($ReturnData[0]))
$SelCompBox.List = StripCopies($ReturnData[0])
$SelCompBox.OnClick = '$$InsProBox.List = GetInfo($$SelCompBox.Value,$$0,$$1,$$ReturnData)'
EndFunction

Function SoftForm($ReturnData)
$1=1
$0=0
$SoftForm.SetFocus
$ProgSoft.Max = Val(Ubound($ReturnData[1]))
$SelProBox.List = StripCopies($ReturnData[1])
$SelProBox.OnClick = '$$InsCompBox.List = GetInfo($$SelProBox.Value,$$1,$$0,$$ReturnData2)'
EndFunction

Function GetDBData()
Dim $CompArr
Dim $ProgArr
Dim $DBArr[2]
$connect = CreateObject("ADODB.Connection")
$connectstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$DBPath$DBFile"
$connect.Open($connectstring)
$queryDB = "SELECT * FROM TBL_PROGRAM"
$queryDB = $connect.Execute($queryDB)
While NOT $queryDB.EOF
$dataComp=$queryDB.Fields("Computer").value
$dataProg=$queryDB.Fields("Program").value
$queryDB.movenext
ReDim preserve $CompArr[Ubound($CompArr)+1]
ReDim preserve $ProgArr[Ubound($ProgArr)+1]
$CompArr[Ubound($CompArr)] = $dataComp
$ProgArr[Ubound($ProgArr)] = $dataprog
Loop
$connect.Close
$DBArr[0] = $CompArr
$DBArr[1] = $ProgArr
$GetDBData = $DBArr
EndFunction

Function GetInfo($SearchFor,$SMultiNum,$RMultiNum,$Array)
Dim $InfoArray
$inter = 0
For Each $find in $Array[$SMultiNum]
If $find = "$SearchFor"
ReDim preserve $InfoArray[Ubound($InfoArray)+1] $InfoArray[Ubound($InfoArray)] = $Array[$RMultiNum][$inter]
EndIf
$inter = $inter + 1
Next
$GetInfo = $InfoArray
EndFunction

Function StripCopies($striArray)
$on = 1
Dim $searArr
For Each $add in $StriArray
If Ubound($SearArr) = -1
ReDim $searArr[0] $SearArr[0] = $add
EndIf
$counter = 0
While Ubound($SearArr)+1 <> $counter
If $add = $searArr[$counter]
$on = 1
EndIf
$counter = $counter + 1
Loop
$counter = 0
If $on = 0 ReDim preserve $SearArr[Ubound($SearArr)+1] $SearArr[Ubound($SearArr)] = $add EndIf
$on = 0
$ProgComp.Value = $ProgComp.Value+1
$ProgSoft.Value = $ProgSoft.Value+1
Next
$ProgComp.Value = 0
$ProgSoft.Value = 0
$StripCopies = $SearArr
EndFunction

Don't know why I bother but here are the pics hosted from geocities.

 -
 -
 -

If those don't work just go here http://www.geocities.com/greenmaze/ and take a look at menu.jpg sbc.jpg and cbs.jpg.

Let me know how to improve this. Thanks.

Top
#69654 - 2002-09-06 12:45 AM Re: Softwhere? - Solution to software inventory data
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11623
Loc: CA
Vig... nice work. (I think - looks impressive)

I will try to put it together and try it out within the next couple of weeks if I can.

As far as a suggestion for improvement, It would be nice to click on a Software name and then be able to print out a list of ALL computers that are running that software, as well as a different printable report that will print out all the software installed on any given/selected workstation.

Top
#69655 - 2002-09-06 06:41 PM Re: Softwhere? - Solution to software inventory data
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Vig,

Great script ... nicely done ... however, not too sure if you noticed or not, but your script has uncovered a bug in Kixforms. The individual dialogs are not handling the TAB key between controls ... think I know what it is ... just hope the fix doesn't upset the BBChecker crowd [Wink]

[ 06. September 2002, 18:46: Message edited by: Shawn ]

Top
#69656 - 2002-09-07 11:52 AM Re: Softwhere? - Solution to software inventory data
Vig Offline
Starting to like KiXtart

Registered: 2001-11-14
Posts: 166
Loc: Saudi Arabia
NTDOC, good idea about being able to print out. I'll work on that in the next couple of days.

Shawn, no I sure didn't notice that.

Top
#69657 - 2002-09-10 05:38 PM Re: Softwhere? - Solution to software inventory data
bruno-France Offline
Fresh Scripter

Registered: 2002-08-30
Posts: 7
Very Good script !! but the second part don't connect to the base i'v not find the pb.! :-(
Bruno

Top
#69658 - 2002-09-10 06:39 PM Re: Softwhere? - Solution to software inventory data
Vig Offline
Starting to like KiXtart

Registered: 2001-11-14
Posts: 166
Loc: Saudi Arabia
The second script will not work with Kixtart version 4.10+, I'm using 4.02 to run that script.
Top
#69659 - 2002-09-10 06:54 PM Re: Softwhere? - Solution to software inventory data
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Vig,

Can you add your weight to this thread:

COM Problems with 4.10+

and include any observations and thoughts as to
what might have broke ? Im going to be
referring this thread back to Ruud in a day or
two. But we need more information to go on.

-Shawn

Top
#69660 - 2002-09-13 05:06 PM Re: Softwhere? - Solution to software inventory data
bruno-France Offline
Fresh Scripter

Registered: 2002-08-30
Posts: 7
Your super script working with 4.11 !
Have replaced :
.../....
$RC=SetOption("WrapAtEOL","On")
$DSN="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBPath$DBFile"
$Connection = CreateObject("ADODB.Connection")$Command = CreateObject("ADODB.Command")$Recordset = CreateObject("ADODB.Recordset")
etc...

With :
$connect = Server.CreateObject("ADODB.Connection")
$connectstring="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$DBPath$DBFile"
$connect.Open($connectstring)

$Command = CreateObject("ADODB.Command")
$Recordset = CreateObject("ADODB.Recordset")

If $Connect
$Connection.Open($connectstring)
$Command.ActiveConnection = $Connectstring
.../...

etc...

Bruno

Top
#69661 - 2002-09-14 08:21 PM Re: Softwhere? - Solution to software inventory data
Vig Offline
Starting to like KiXtart

Registered: 2001-11-14
Posts: 166
Loc: Saudi Arabia
Hey thanks Bruno. [Smile]

I've been to busy lately to tackle that problem.

I've got a few more improvements to make then I'll update the scripts.

Top
#69662 - 2002-09-18 04:36 PM Re: Softwhere? - Solution to software inventory data
operagost Offline
Fresh Scripter

Registered: 2002-07-05
Posts: 13
Uhh... where does join() come from in the UDF?

code:
 Script error : unknown command !
join($programs,"~*~")


Top
#69663 - 2002-09-18 04:40 PM Re: Softwhere? - Solution to software inventory data
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
it is function added at somepoint to kix 4.xx

{edit}
yep, in 4.10
 

[ 18. September 2002, 16:42: Message edited by: Lonkero ]
_________________________
!

download KiXnet

Top
#69664 - 2002-09-18 05:24 PM Re: Softwhere? - Solution to software inventory data
operagost Offline
Fresh Scripter

Registered: 2002-07-05
Posts: 13
Ok... I'm using 4.02, so I guess I have to go to 4.1x now?
Top
#69665 - 2002-09-18 05:51 PM Re: Softwhere? - Solution to software inventory data
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
well, it is one option.
other is to modify the lines to work with older versions...
_________________________
!

download KiXnet

Top
#69666 - 2002-09-18 07:48 PM Re: Softwhere? - Solution to software inventory data
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11623
Loc: CA
VIG,

Is the current code posted here the latest?

Did you get a chance to implement printing out the list of Computers that are running a specific piece of software by clicking on it?

Thanks... also, what is the MINIMUM version of KiXtart and KiXforms required for this to work properly and what version of MS Access is required?

Top
#69667 - 2002-09-18 09:28 PM Re: Softwhere? - Solution to software inventory data
Vig Offline
Starting to like KiXtart

Registered: 2001-11-14
Posts: 166
Loc: Saudi Arabia
NTDOC,
Currently reworking the entire script/process. My goal is to have one form that can do all of these functions plus more (creating reports, printing, faster and more search options). This is taking some time, especially since smaller time consuming projects keep coming up. My weekend is thursday and friday so expect something by the end of the week. All requests and questions will be answered then. Thanks.

Top
#69668 - 2003-05-19 04:01 PM Re: Softwhere? - Solution to software inventory data
Darren_W Offline
Hey THIS is FUN
*****

Registered: 2001-10-10
Posts: 208
Loc: Bristol, England
Hi,

Great script, Has any work been done on it latley?

Having problems with the UDF, sometimes it does not produce any data on the pc it audits, consequently it bombs out the db convert script.

Any ideas what could be going wrong, I'm using the most recent version of kixtart on windows 98 machines
_________________________
I want to share something with you - the three sentences that will get you through life.
Number 1, 'cover for me.'
Number 2, 'oh, good idea, boss.'
Number 3, 'it was like that when I got here'.

Top
#69669 - 2003-05-20 10:58 AM Re: Softwhere? - Solution to software inventory data
masken Offline
MM club member
*****

Registered: 2000-11-27
Posts: 1222
Loc: Gothenburg, Sweden
Cool script [Cool]

Although I really have to give a shout for one freeware application: AIDA32 - www.aida32.hu (all that's required is that you register your company at Aida, no charge).

Just make a customized reportformat, and do inventories really slick with something like (this will do an inventory once a month):

code:
? "*** PC Auditing"
$DoInvent = "no"
$InventFile = "\\someserver\Inventory$$\" + @WKSTA + ".csv"
IF EXIST("$InventFile") = 0
$DoInvent = "yes"
ELSE
$FileMonth = SUBSTR(GETFILETIME("$InventFile"), 6, 2)
$ThisMonth = SUBSTR(@DATE, 6, 2)
IF $FileMonth <> $ThisMonth
DEL "$InventFile"
$DoInvent = "yes"
ENDIF
ENDIF
IF $DoInvent = "yes"
COLOR w/b ?" - PC audit, please wait..." COLOR y+/b
SHELL '%COMSPEC% /C $HomeServer\fileserver$$\Files\Aida32\aida32.exe /R "\\someserver\Inventory$$\$$HOSTNAME.csv" /CUSTOM "$HomeServer\fileserver$$\Files\Aida32\some_cust_report.rpf" /CSV /SHOWP /SILENT'
ELSE
COLOR w/b ?" - PC audit already made" COLOR y+/b
ENDIF



[ 20. May 2003, 11:02: Message edited by: masken ]
_________________________
The tart is out there

Top
#69670 - 2003-05-20 06:03 PM Re: Softwhere? - Solution to software inventory data
masken Offline
MM club member
*****

Registered: 2000-11-27
Posts: 1222
Loc: Gothenburg, Sweden
I should perhaps add that I've removed all DirectX, event log & performance test related inventory options from the inventory file through the customized report format. It's more or less the "net audit" template available. This produces a 20-50kb inventory file. The default, with all options turned on, could produce a 5mb file [Eek!]

But this is a really good application. I've inventoried everything from Win95a to WinXP SP1 with it, and it works like a charm (early Win95's need a Comctl update).
_________________________
The tart is out there

Top
#69671 - 2003-05-22 12:05 AM Re: Softwhere? - Solution to software inventory data
Darren_W Offline
Hey THIS is FUN
*****

Registered: 2001-10-10
Posts: 208
Loc: Bristol, England
Thanks [Smile]

Will look into it
_________________________
I want to share something with you - the three sentences that will get you through life.
Number 1, 'cover for me.'
Number 2, 'oh, good idea, boss.'
Number 3, 'it was like that when I got here'.

Top
#69672 - 2003-06-02 04:16 PM Re: Softwhere? - Solution to software inventory data
GMAN Offline
Fresh Scripter

Registered: 2003-06-01
Posts: 11
Loc: Florida
Great Software...2 questions about you NetView.udf and SerialDate.udf....how do I get these?
Top
Page 1 of 2 12>


Moderator:  Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Ruud van Velsen, Mart 
Hop to:
Shout Box

Who's Online
1 registered (Allen) and 382 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.07 seconds in which 0.024 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org