Vig
|
(Starting to like KiXtart)
|
2002-09-05 11:11 PM
|
|
|
|
|
Softwhere? - Solution to software inventory data
|
|
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.
|
NTDOC
|
(KiX Master)
|
2002-09-06 12:45 AM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
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.
|
Shawn
|
(KiX Supporter)
|
2002-09-06 06:41 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
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 [ 06. September 2002, 18:46: Message edited by: Shawn ]
|
Vig
|
(Starting to like KiXtart)
|
2002-09-07 11:52 AM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
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.
|
|
Re: Softwhere? - Solution to software inventory data
|
|
Very Good script !! but the second part don't connect to the base i'v not find the pb.! :-( Bruno
|
Vig
|
(Starting to like KiXtart)
|
2002-09-10 06:39 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
The second script will not work with Kixtart version 4.10+, I'm using 4.02 to run that script.
|
Shawn
|
(KiX Supporter)
|
2002-09-10 06:54 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
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
|
|
Re: Softwhere? - Solution to software inventory data
|
|
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
|
Vig
|
(Starting to like KiXtart)
|
2002-09-14 08:21 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
Hey thanks Bruno. 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.
|
operagost
|
(Fresh Scripter)
|
2002-09-18 04:36 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
Uhh... where does join() come from in the UDF?
code:
Script error : unknown command ! join($programs,"~*~")
|
Lonkero
|
(KiX Master Guru)
|
2002-09-18 04:40 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
it is function added at somepoint to kix 4.xx
{edit} yep, in 4.10   [ 18. September 2002, 16:42: Message edited by: Lonkero ]
|
operagost
|
(Fresh Scripter)
|
2002-09-18 05:24 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
Ok... I'm using 4.02, so I guess I have to go to 4.1x now?
|
Lonkero
|
(KiX Master Guru)
|
2002-09-18 05:51 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
well, it is one option. other is to modify the lines to work with older versions...
|
NTDOC
|
(KiX Master)
|
2002-09-18 07:48 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
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?
|
Vig
|
(Starting to like KiXtart)
|
2002-09-18 09:28 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
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.
|
Darren_W
|
(Hey THIS is FUN)
|
2003-05-19 04:01 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
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
|
masken
|
(MM club member)
|
2003-05-20 10:58 AM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
Cool script
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 ]
|
masken
|
(MM club member)
|
2003-05-20 06:03 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
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
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).
|
Darren_W
|
(Hey THIS is FUN)
|
2003-05-22 12:05 AM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
Thanks
Will look into it
|
GMAN
|
(Fresh Scripter)
|
2003-06-02 04:16 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
Great Software...2 questions about you NetView.udf and SerialDate.udf....how do I get these?
|
|
Re: Softwhere? - Solution to software inventory data
|
|
UDFs are posted in the UDF Forum.
|
GMAN
|
(Fresh Scripter)
|
2003-06-02 04:20 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
Do you know where?
|
|
Re: Softwhere? - Solution to software inventory data
|
|
Take a look at my signature or read the FAQ Forum under Cannot find what you are looking for?
|
GMAN
|
(Fresh Scripter)
|
2003-06-02 04:28 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
Stupid me....Thank you very much will look into it!
Thanks again
|
|
Re: Softwhere? - Solution to software inventory data
|
|
|
Les
|
(KiX Master)
|
2005-03-02 05:00 PM
|
|
|
|
|
Re: Softwhere? - Solution to software inventory data
|
|
KRAY, Why are you dredging up these corpses without adding a single word to it?
|