Search the Community

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.