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

1. 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.
2. The next thing you need to do is open up Firefox browser and go to tools > add ons. 
3. 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".
4. With Firefox you will get a confirmation window asking you to hit "install now" to install the add on.  Do it.
5. Once it is installed, it will ask that you restart Firefox.  Do it.  You can just click the "Restart Firefox" button.
6. 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.
7. Now you need to go to the Firefox menu bar and click on Tools > SQLite Manager.
8. It might take a moment to load it, so be patient.  Once it does load it should look like this.
9. To view your data base click on File > Connect Database.
10. 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.
11. Once the database is open you should see this.
12. 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.
13. 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.
14. To edit the information, change what you like in the text boxes and save your changes by clicking "OK".
15. Now your information in the database has been successfuly manipulated.  Congratulations.