Spiceworks Db Exporter

[t0nZ]

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.

 

expandcollapse popup

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

 

[Confuzzled]

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?

[t0nZ]

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.

[Confuzzled]

Aha, the perils of having a _very_ brief glance. Glad you accounted for the issue in your code.