Jump to content
Sign in to follow this  
antmar904

Insert Software Inventory into SQLite

Recommended Posts

antmar904
I would like to run a program from Nirsoft called "MyUninstaller" that gives me a nice txt file with the software installed on a computer and insert that data into a SQLite db file.
 
What would be the best way to manipulate the data in the output file and insert into sqlite?
 
Here is that link to MyUnistaller:
 
Here is the command line that is run on the computers:
myuninst.exe /stext software.txt
 
Attached is the output of the software.txt
 
Thank you in advance.

software.txt

Share this post


Link to post
Share on other sites
jchd

Which column(s) would you like to have in this SQLite table?


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
antmar904

Hi jchd,

For now I would like Entry Name, Version and Company.

Edited by antmar904

Share this post


Link to post
Share on other sites
jchd

Here you are:

#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

_SQLite_Startup()
Local $hDB = _SQLite_Open(@ScriptDir & "\softwareset.db3")
_SQLite_Exec($hDB, "create table if not exists Software (id integer primary key, Name char collate nocase, Version char, Company char collate nocase)")

Local $input = FileRead(@ScriptDir & "\software.txt")
Local $entries = StringRegExp($input, "(?ims)^Entry Name[\s:]+(\N*?)\R.*?^Version[\s:]+(\N*?)\R.*?^Company[\s:]+(\N*?)\R", 3)
_ArrayDisplay($entries)
If @error Then
    MsgBox(0, "", "No entry found; check input.")
Else
    _SQLite_Exec($hDB, "begin;")
    For $i = 0 To UBound($entries) / 3 - 1
        _SQLite_Exec($hDB, "insert into software (" & _
                                                    "name, " & _
                                                    "version, " & _
                                                    "company " & _
                                        ") values (" & _
                                                    _SQLite_FastEscape($entries[3 * $i]) & ", " & _
                                                    _SQLite_FastEscape($entries[3 * $i + 1]) & ", " & _
                                                    _SQLite_FastEscape($entries[3 * $i + 2]) & _
                                                ");")
    Next
    _SQLite_Exec($hDB, "commit;")
EndIf

; what do we have so far?
Local $aRows, $iRows, $iCols
_SQLite_GetTable2d($hDB, "select * from software order by company, name, version;", $aRows, $iRows, $iCols)
_ArrayDisplay($aRows)

_SQLite_Close($hDB)
_SQLite_Shutdown()

You can use the free version of SQLite Expert to play with any SQLite database.

  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
mikell

jchd,

Local $entries = StringRegExp($input, "(?im)^(?:Entry Name|Version|Company)[\s:]+(\N*)$", 3)

?

:)

Edited by mikell

Share this post


Link to post
Share on other sites
jchd

That would mean we are absolutely sure that the various lines we look for are always in the same order, or we're going to assign them in wrong columns in the table. Better code for robustness and possibly count input entries and double check that we got 3 times as many values from the regexp. As usual I rarely add all error test harness to code sample.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
mikell

As the "software.txt" is software-generated I assumed that the order has unlikely to change

Share this post


Link to post
Share on other sites
jchd

Granted, every individual inventory list is likely to be consistent, but I wouldn't bet my life that every computer in the enterprise carries the same version of the inventory software producing the exact same layout among versions.

That's why flat text files are inferioir to csv, json, xml, or some other structured format.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
antmar904

@jchd

Your script worked great.  I defanitly need to learn more about "StringRegExp".

So how would i create a db and have a different table for each computer with there software inventory.

Could i create a table within a table?

Exp:

Computer1

  > Hardware

  > Software

Computer2

  > Hadware

  > Software

Computer3 

  > Hardware

  > Software

Again thank you all for you help.

Share this post


Link to post
Share on other sites
jchd

This not advisable. Instead, you should add a new column in the table to store computer name or identifier and eventually a departement ID. This way you have one table which is sufficient given the requirements known so far and you can perform queries like:

which computers carries Office version x.y.z?

which Adobe products are more prevalent?

how many versions are found for product PPP?

and with some improvements in the project:

how much would it cost us to update all computers to the same version of software ABC?

If you spare the same data over several tables you won't be able to perform such informative queries and your problems could be worse than taking the time to do thing properly in the first place.

Now if you expect to deal someday with the hardware as well, you'll need a much more involved setup. It could still be worse the pain, depending on your context.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
ZacUSNYR

You can get all this data from the registry using AutoIt too.  Then write it out to a SQL database.  That way an external text file wouldn't be a controlled variable you have to worry about.

  • Like 1

Share this post


Link to post
Share on other sites
antmar904

@Zac

That is also true, thank you all for your responses.

I will post on which route i go.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×