Deleting from and editing the spiceworks database
Posted by: David
Why edit the spiceworks database?
- You need to correct an error you made
- You need to delete an old item
- You feel more comfortable navigating the database
WARNING
- If you don't understand what you are trying to do before you attempt to do this you might as well not bother. If you delete something in your database that was included as a foreign key for something else you could really mess things up. Be careful!
How to edit the database
- First thing you need is the ability to edit the database. The one prerequisite for this walk through is that you have Firefox. I find it hard to believe someone in IT wouldn't have this browser right now, but if not, download it.
- The next thing you need to do is open up Firefox browser and go to tools > add ons.
- After the Firefox add on window is up, search for "sqlite manager" and it should be the first result. Compare to the image below and hit "Add to Firefox".
- With Firefox you will get a confirmation window asking you to hit "install now" to install the add on. Do it.
- Once it is installed, it will ask that you restart Firefox. Do it. You can just click the "Restart Firefox" button.
- Once Firefox is restarted the add ons window should come back up saying that 1 add on had been installed. You can just close this window.
- Now you need to go to the Firefox menu bar and click on Tools > SQLite Manager.
- It might take a moment to load it, so be patient. Once it does load it should look like this.
- To view your data base click on File > Connect Database.
- Now you should see a file explorer asking you to find the database you want to open. The first thing you want to do is change your file type to "All Files" because you won't see the spiceworks database with the default selection. Next you will need to browse to C:\Program Files\Spiceworks\db and select the spiceworks_prod.db file and hit open.
- Once the database is open you should see this.
- On the left you see an expandable list of tables that contain your information. In this example I am going to edit the information in a ticket. Browse down the table list and click on the "tickets" table. Click the "Browse & Search" tab.
- The rows that are displayed to the right in the "Browse & Search" tab are the actual rows of information in your database. To delete a row, just right click the row and select delete. To edit a row, right click the row and select edit.
- To edit the information, change what you like in the text boxes and save your changes by clicking "OK".
- Now your information in the database has been successfuly manipulated. Congratulations.
Exporting a spiceworks database
Posted by: David
Spiceworks Logo
Export your spiceworks database
I've had a few search queries hit my site looking for information on the spiceworks database. Here is some key information:
- The database store all of the information you need for spiceworks
- You will want to stop running spiceworks before you attempt to export it
- Once you export the database, it should be exactly like the old one
- This will allow you to put a spiceworks database on a different server/computer with the same information it had before
What do you do?
In file explorer go to C:\Program Files\Spiceworks\db\ and find the file spiceworks_prod.db.
Make sure Spiceworks is closed and the service is not running. You don't want anything manipulating the database file while you attempt to export it.
Once you have made sure that Spiceworks is not running, copy the database file locally on the hard disk renaming it as you please.
Once you have copied the database locally, you can go ahead and start Spiceworks up again if you need to.
Now you need to put the copy of your Spiceworks database file onto some media that you can transfer it to the computer that it will be going on. Or just transfer it over the network.
When you are ready to use the database on the next computer you will need to already have Spiceworks installed. Make sure the the program is not running and the service is stopped.
Browse back to C:\Program File\Spiceworks\db and replace the spiceworks_prod.db file with the copy of your database from the last computer. You can do this by deleting spiceworks_prod.db and renaming the copied .db file to spiceworks_prod.db.
Start spiceworks and you should have all your settings and information copied over from the last computer.
It is that easy!
Tip for success
Make sure that anyone using or that is going to be effected by Spiceworks being down knows ahead of time that there will be a short down time in exporting a database.
Keep your stakeholders happy.
Export a device software list from Spiceworks to Excel
Posted by: David
Here is how how you can export the software list for a specific device to excel in Spiceworks.
- On the Spiceworks menu, go to the reporting tab.
2. On that reporting tab, in the reports window click on "New Report".
3. On the new report screen, click the image below to see how you should select the information on that screen.
You will need to build the report using SQL. Just replace 'devicename' with the name of the device you want the report for. The code for the SQL is below
SELECT software.name as Name, software_installations.version as Version, software_installations.install_date as Install_Date FROM devices, software_installations, software WHERE software_installations.computer_id = devices.id AND software.id = software_installations.software_id AND devices.name = 'devicename' ORDER BY software.name ASC
4. After you have built the report the way you have needed, hit "save and run".
5. After running the report you should see a list of the software from the device below. However to export the report to excel all you have to do is hit the export button and choose XLS next to the excel icon and save it or open the file as you see fit!
ENJOY