Jump to content

Recommended Posts

Code to read a Spiceworks Database and export text data files in INI format.
Focused on exporting data about PC inventory and useful to migrate to another inventory system.
Tested with a SQLite DB from Spiceworks latest (and LAST on premise) version - (7.5.00107.30 ott 2019)

It creates one  text file for every single machine.

 

;Spiceworks Db Exporter
;
;- NSC - t0nZ 2021

;code to read a Spiceworks Database and export text data files in INI format.
;focused on exporting data about PC inventory.
;useful to migrate to another inventory system.
;tested with a SQLite DB from Spiceworks latest (and LAST on premise) version - (7.5.00107.30 ott 2019)
;It creates one text files for every single machine.

; --> please adapt paths to your environment.

#include <SQLite.au3>
#include <String.au3>
#include <File.au3>

dataINIfromDBspiceworks()

#Region spiceworks immport
Func dataINIfromDBspiceworks()
    ConsoleWrite("start import from db Spiceworks" & @CRLF)
    Local $dbspicepath = "C:\scambio"
    Local $dbspice = "spiceworks_prod.db"

    ; =====================>>>>> START SQL DLL
    _SQLite_Startup()

    ;======================<<<<<<<<<<<<<<<<<<<
    _SQLite_Open($dbspicepath & '\' & $dbspice)
    ;-
    Local $aCPdata
    Local $hQuery
    Local $recordcount = 0
    Local $salvarec = 0
    ;------------------------------------------------
    Local $spiceQuery = "SELECT serial_number," & _
            "server_name," & _
            "manufacturer," & _
            "(model ||  '-' || raw_model)," & _
            "processor_type," & _
            "raw_processor_type," & _
            "processor_architecture," & _
            "raw_processor_type," & _
            "number_of_processors," & _
            "'speed'," & _
            "CAST (memory AS FLOAT) / 1073741824," & _
            "current_user," & _
            "domain," & _
            "network_adapters.dns_domain," & _
            "'logon'," & _
            "operating_system," & _
            "os_architecture," & _
            "version," & _
            "'lang'," & _
            "('C:;Fixed;' || (CAST (disks.size AS FLOAT) / 1048576) || ';' || (CAST (disks.free_space AS FLOAT) / 1048576) )," & _
            "devices.ip_address," & _
            "'(0.0.0.0)'," & _
            "'(0.0.0.0)'," & _
            "'(0.0.0.0)'," & _
            "'(0.0.0.0)'," & _
            "network_adapters.gateway," & _
            "(network_adapters.description ||' - '|| network_adapters.name)," & _
            "devices.mac_address," & _
            "devices.updated_on," & _
            "user_tag " & _
            "FROM devices " & _
            "inner JOIN " & _
            "network_adapters ON network_adapters.computer_id = devices.id " & _
            "inner JOIN " & _
            "disks ON disks.computer_id = devices.id " & _
            "WHERE disks.name = 'C:' " & _
            "ORDER BY devices.updated_on DESC;"

    _SQLite_Query(-1, $spiceQuery, $hQuery)

    While _SQLite_FetchData($hQuery, $aCPdata) = $SQLITE_OK
        writeINI($aCPdata)
        $recordcount += 1
        If $recordcount = $salvarec + 10 Then
            $salvarec = $recordcount
            ConsoleWrite($recordcount & " processed records " & @CRLF)
        EndIf
    WEnd

    ;________________________________________________
    _SQLite_Close()
    _SQLite_Shutdown()
EndFunc   ;==>dataINIfromDBspiceworks


Func writeINI($aCPdata)

    Local $folderdataINI = "c:\scambio\ini"
    If Not FileExists($folderdataINI) Then DirCreate($folderdataINI)

    $aCPdata[28] = StringRegExpReplace($aCPdata[28], "[\D]", "") ; this "2021-04-17 02:34:16" to that "20210417023416

    Local $cpini = $folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini"
    If FileExists($folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini") Then
        FileDelete($folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini") ; deleted previous files !
        ConsoleWrite("deleted: " & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini" & @CRLF)
    EndIf

    ;Section Unique :serial, computer name
    Local $aSectionUnique[2][2] = [["serial", $aCPdata[0]], ["computername", $aCPdata[1]]]
    IniWriteSection($cpini, "UNIQUE", $aSectionUnique, 0)

    ;Section Machine :manufacturer,model,cpuname,cpuid,cpuarc,cpuvendor,cpucores,cpuspeed,RAM,disk1capacity,disk1used
    Local $aSectionMachine[9][2] = [["manufacturer", $aCPdata[2]], ["model", $aCPdata[3]], ["cpuname", $aCPdata[4]], ["cpuid", $aCPdata[5]], ["cpuarc", $aCPdata[6]], ["cpuvendor", $aCPdata[7]], ["cpucores", $aCPdata[8]], ["cpuspeed", $aCPdata[9]], ["ram", $aCPdata[10]]]
    IniWriteSection($cpini, "MACHINE", $aSectionMachine, 0)

    ;Section User : username,domain,DNSdomain,logonServer
    Local $aSectionUser[4][2] = [["username", $aCPdata[11]], ["domain", $aCPdata[12]], ["DNSdomain", $aCPdata[13]], ["logonServer", $aCPdata[14]]]
    IniWriteSection($cpini, "USER", $aSectionUser, 0)

    ;Section OS : OSver, OSarch, OSbuild, OSlang
    Local $aSectionOS[4][2] = [["OSver", $aCPdata[15]], ["OSarch", $aCPdata[16]], ["OSbuild", $aCPdata[17]], ["OSlang", $aCPdata[18]]]
    IniWriteSection($cpini, "OS", $aSectionOS, 0)

    ;Section Disks: username,domain,DNSdomain,logonServer

    Local $aSectionDrives[1][2] = [["drives", $aCPdata[19]]]

    IniWriteSection($cpini, "DRIVES", $aSectionDrives, 0)

    ;Section network:localIP1,localIP2,localIP3,localIP4,publicIP,GW,adapter,mac
    Local $aSectionNetwork[8][2] = [["localIP1", $aCPdata[20]], ["localIP2", $aCPdata[21]], ["localIP3", $aCPdata[22]], ["localIP4", $aCPdata[23]], ["publicIP", $aCPdata[24]], ["GW", $aCPdata[25]], ["adapter", $aCPdata[26]], ["mac", $aCPdata[27]]]
    IniWriteSection($cpini, "NETWORK", $aSectionNetwork, 0)

    ;Section PLUS :date, groupid
    Local $aSectionPlus[2][2] = [["date", $aCPdata[28]], ["groupid", $aCPdata[29]]]
    IniWriteSection($cpini, "PLUS", $aSectionPlus, 0)


EndFunc   ;==>writeINI

#EndRegion spiceworks import

 

Spiceworks on premise, now pretty abadonware, has a non crypted SQLite DB usually located in:

c:\Program Files (x86)\Spiceworks\db\spiceworks_prod.db

The query was the difficult part (at least for me), and I export in INI format because it was part of my effort to migrate from Spiceworks to a custom made system (Computer Plucker see this post) where I already parse .INI files in a custom MySQL and/or SQLite DB.

 

Link to post
Share on other sites

Just a quick glimpse at your code and noted something you may have overlooked.

If your conversion process takes less than one second per file, the unique naming convention (YYYYMMDD HHMMSS) will result in you deleting the previous file.

Maybe use something unique about the PC configuration such as the mac address or computer name to pad out the resulting txt file name?

Link to post
Share on other sites

To be fair... yes I didn't think about this aspect, but I checked and I don't have lost anyone of my 484 machines.

The INI name is 

"cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini"

time -  serial number like 

cp_20210511202511_PF43YXYX_.ini

So I have the unique serial number, and keep in mind the date-time is not the current one but the date-time recorded by the original Spiceworks server/client.

I'am thinking about this but for now I don't see a problem, 🤔 ...

In every case thank you for the note.

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By benners
      I am trying to normalise a database to remove duplicate info. I am using SQLite Expert to design the database and test sql queries.
      The database is going to store information about setup installers, such as paths, installer specific info, users to install for, type and category of installer blah, blah.
      I have attached the database thus far and the tables function are as follows:-
      category - stores text describing the general usage the installer comes under, such as Browser, Compression etc. installer - this is the main table that has relationships with the other tables and stores info about the installer file, install order etc. installer_user - a link table. Stores the user or computer to install the program for or on. package - stores the type of installer, NSIS, Inno  Nullsoft etc. platform - the OS architecture the installer file is compiled for. postinstall - a list of activities to perform when the main install has finished. postinstall_user - a link table. Stores the users\computers that are allowed to run the post install actions user - a list of computers or usernames. I might separate into two tables, undecided yet. Now there wil be one program that deals with the installation side and another that acts as a front end for editing the database suchs as adding new files, removing old files etc.
      The idea with the editing side is to be able to delete an installer from the installer table say with the id of 1 and all other pertinent information in the other tables will also be deleted. The same goes for deleting a user. All the fields relating to that user will be removed.
      I have managed to get that part working for the most part. If I delete either a user or installer, the related info in the installer_user and postinstall tables are removed but since I added the postinstall_user table to link usernames to the postinstall action, this is where I get the foreign key error. If someone can explain why, I am sure it is an obvious reason for someone who knows what they are doing 😄
      Cheers
      Installer - Copy.db
    • By dmob
      So I am trying to implement an archive system of sorts for my (SQLite) DB app. I wrote a function to attach a separate (archive) DB and
      sync the columns with main DB. If archive DB file does not exist, create file with _SQLiteOpen then close the file (and thus connection) with SQLite_Close.
      This works as intended, however, after the create operation, all subsequent _SQLite_* functions returned a "Library misuse error".
      After a little digging I found the problem in the _SQLite_Close function: it clears the "last opened database" handle even when there still is a live
      DB connection open. All other functions then "think" there is no DB connection active. I hacked two functions in the UDF for a quick fix:

      In _SQLite_Close: Change
      ... $__g_hDB_SQLite = 0 __SQLite_hDel($__g_ahDBs_SQLite, $hDB) Return $iRval[0] to:
      $__g_hDB_SQLite = __SQLite_hDel($__g_ahDBs_SQLite, $hDB) Return $iRval[0] and in Func __SQLite_hDel changed
      Func __SQLite_hDel(ByRef $ahLists, $hGeneric) Local $iElement = _ArraySearch($ahLists, $hGeneric) If $iElement > 0 Then _ArrayDelete($ahLists, $iElement) EndFunc ;==>__SQLite_hDel to:
      Func __SQLite_hDel(ByRef $ahLists, $hGeneric) Local $iElement = _ArraySearch($ahLists, $hGeneric) If $iElement > 0 Then _ArrayDelete($ahLists, $iElement) Return $ahLists[UBound($ahLists)-1] ; Return last opened db EndIf Return 0 EndFunc ;==>__SQLite_hDel so it preserves last opened DB again.
       
      My archive function now works great
      I'm not sure if this should be classified as a bug, but I believe so...
      Hope this helps someone before
    • By argumentum
      #include <SQLite.au3> ;-- When SQLite is compiled with the JSON1 extensions it provides builtin tools ;-- for manipulating JSON data stored in the database. ;-- This is a gist showing SQLite return query as a JSON object. ;-- https://www.sqlite.org/json1.html Example() Func Example() _SQLite_Startup() ; "<your path>\sqlite3.dll", False, 1) ; https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Startup.htm _SQLite_Open() ; ...if you can not run this due to errors, get the latest DLL from https://www.sqlite.org/ If _SQLite_Exec(-1, "CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, full_name TEXT NOT NULL, email TEXT NOT NULL, created DATE NOT NULL );") Then Return 4 If _SQLite_Exec(-1, 'INSERT INTO users VALUES ' & _ '(1, "Bob McFett", "bmcfett@hunters.com", "32-01-01"),' & _ '(2, "Angus O''Vader","angus.o@destroyers.com", "02-03-04"),' & _ '(3, "Imperator Colin", "c@c.c", "01-01-01");') Then Return 5 ; -- Get query data as a JSON object using the ; -- json_group_object() [1] and json_object() [2] functions. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_object(" & _ " email," & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS json_result" & _ " FROM (SELECT * FROM users WHERE created > ""02-01-01"");") ; {"bmcfett@hunters.com":{"full_name":"Bob McFett","created":"32-01-01"},"angus.o@destroyers.com":{"full_name":"Angus O'Vader","created":"02-03-04"}} ; -- Get query data as a JSON object using the ; -- json_group_array() function to maintain order. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_array(" & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS my_json_result_OrAnythingReally" & _ " FROM (SELECT * FROM users ORDER BY created);") ; [{"full_name":"Imperator Colin","created":"01-01-01"},{"full_name":"Angus O'Vader","created":"02-03-04"},{"full_name":"Bob McFett","created":"32-01-01"}] ;-- Links ;-- [1] https://www.sqlite.org/json1.html#jgroupobject ;-- [2] https://www.sqlite.org/json1.html#jobj ; example found at https://gist.github.com/akehrer/481a38477dd0518ec0086ac66e38e0e2 EndFunc ;==>Example Func _SQLite_GetTable2d_ArrayToConsole($sSQL, $hDB = -1) Local $aResult, $iRows, $iColumns If _SQLite_GetTable2d($hDB, $sSQL, $aResult, $iRows, $iColumns) Then ConsoleWrite("! SQLite Error: " & _SQLite_ErrCode($hDB) & @CRLF & "! " & _SQLite_ErrMsg($hDB) & @CRLF) Else _SQLite_Display2DResult($aResult) EndIf ConsoleWrite(@CRLF) EndFunc ;==>_SQLite_GetTable2d_ArrayToConsole Based on this example, you can build your own query. 
      The code has all the explanations.
      Enjoy  
    • By argumentum
      #include <SQLite.au3> ;~ #include <SQLite.dll.au3> Local $hQuery, $aRow, $aNames _SQLite_Startup() ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) _SQLite_Open() ; open :memory: Database _SQLite_Exec(-1, "CREATE TABLE aTest (A,B int not null unique ,C text);") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');") _SQLite_Query(-1, "SELECT _ROWID_,* FROM aTest ORDER BY a;", $hQuery) _SQLite_FetchTypes($hQuery, $aNames) ; Read out Column Types ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CRLF) _SQLite_FetchNames($hQuery, $aNames) ; Read out Column Names ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CRLF) While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CRLF) WEnd _SQLite_Exec(-1, "DROP TABLE aTest;") _SQLite_Close() _SQLite_Shutdown() ; Output: ; INTEGER int text ; rowid A B C ; 3 a 1 Hello ; 2 b 3 ; 1 c 2 World Func _SQLite_FetchTypes($hQuery, ByRef $aTypes) Dim $aTypes[1] If __SQLite_hChk($hQuery, 3, False) Then Return SetError(@error, 0, $SQLITE_MISUSE) Local $avDataCnt = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_column_count", "ptr", $hQuery) If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; DllCall error If $avDataCnt[0] <= 0 Then Return SetError(-1, 0, $SQLITE_DONE) ReDim $aTypes[$avDataCnt[0]] Local $avColName For $iCnt = 0 To $avDataCnt[0] - 1 $avColName = DllCall($__g_hDll_SQLite, "wstr:cdecl", "sqlite3_column_decltype16", "ptr", $hQuery, "int", $iCnt) If @error Then Return SetError(2, @error, $SQLITE_MISUSE) ; DllCall error $aTypes[$iCnt] = $avColName[0] Next Return $SQLITE_OK EndFunc ;==>_SQLite_FetchTypes  If you wanna build a proper JSON string, you may want to know if is {"int":123} or {"text":"123"}
      and for that, this can help, obviously only when declared in the SQLite table.
    • By argumentum
      I was thinking but I don't have the experience, so you may have the experience.
      I was thinking to chop a DB with 100.000 ( 20 columns ) in 10.000 DB chunks ( 10 DBs ) and query concurrently all of them to speed up a search, then add the results. Is that a sound idea ? Or will I run in trouble down the road. Should a DB better be keep in one piece ?
      The DB is now in MySQL. I wanna do all this chopping and use SQLite. 
      Thanks
      [solved]
×
×
  • Create New...