Insert Software Inventory into SQLite

[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:

http://www.nirsoft.net/utils/myuninst.html

 

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

[jchd]

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

[antmar904]

Hi jchd,

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

Edited March 17, 2014 by antmar904

[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.

[mikell]

jchd,

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

?

Edited March 17, 2014 by mikell

[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.

[mikell]

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

[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.

[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.

[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 March 17, 2014 by jchd

[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.

[antmar904]

@Zac

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

I will post on which route i go.