Using spiceworks to see old comments from a particular person and or from a particular day
Posted by: David
Possible problems that might have you thinking about finding old tickets
- Someone is trying to see if they worked on a particular day
- You are trying to see what anyone said on a particular day
- You are trying to find out what someone has been saying
- You want to know what Public or Private comments someone has been using in tickets
Solution:
- In spiceworks click on the reporting tab
- Click on New Report
- Make sure "Build this report using SQL" is checked
- Type in the following code....
To find comments from someone on a particular day
SELECT tickets.id as Ticket, comments.body as Note, strftime('%Y-%m-%d %H:%m', comments.created_at) as Day FROM users, comments, tickets
WHERE tickets.id = comments.ticket_id
AND tickets.created_by = users.id
AND comments.created_by = '12'
AND strftime('%Y-%m-%d', comments.created_at) = '2009-09-09'
ORDER BY tickets.id DESC
LIMIT 0, 500
The code above will find ticket comments by (users.id='12'), that were created on 9-9-09 (strftime('%Y-%m-%d', comments.created_at) = '2009-09-09'), and they will be displayed in descending order by the ticket number an limited to a number of 500 results. To find the comments made by a particular person you will have to find their user id in the database.
To find comments from anyone on a particular day
SELECT tickets.id as Ticket, comments.body as Note, strftime('%Y-%m-%d %H:%m', comments.created_at) as Day FROM users, comments, tickets
WHERE tickets.id = comments.ticket_id
AND tickets.created_by = users.id
AND strftime('%Y-%m-%d', comments.created_at) = '2009-09-09'
ORDER BY tickets.id DESC
LIMIT 0, 500
If you notice, this is the exact same code as before, but without the users.id='12'. The code above will find all comments dated 9-9-09.
To find only private comments by someone on a particular day
SELECT tickets.id as Ticket, comments.body as Note, strftime('%Y-%m-%d %H:%m', comments.created_at) as Day FROM users, comments, tickets
WHERE tickets.id = comments.ticket_id
AND tickets.created_by = users.id
AND comments.created_by = '12'
AND comments.is_public = 'f'
AND strftime('%Y-%m-%d', comments.created_at) = '2009-09-09'
ORDER BY tickets.id DESC
LIMIT 0, 500
The code above is the same as the first code but with the addition of AND comments.is_public = 'f'. This means you will have all ticket comments by a particular person (12) on a particular day (9-9-09) that were false as to being public (aka private).
To find only public comments by someone on a particular day
SELECT tickets.id as Ticket, comments.body as Note, strftime('%Y-%m-%d %H:%m', comments.created_at) as Day FROM users, comments, tickets
WHERE tickets.id = comments.ticket_id
AND tickets.created_by = users.id
AND comments.created_by = '12'
AND comments.is_public = 't'
AND strftime('%Y-%m-%d', comments.created_at) = '2009-09-09'
ORDER BY tickets.id DESC
LIMIT 0, 500
To find the public comments just change the comments.is_public = 'f' to = 't'.
Tip for success
Don't limit yourself to the code that I have above to find the information you are looking for. Open up SQLlite and try playing around with it and adding different parameters to the query's.
Also, if you want to find your users ids easier than manually opening up your database just make an SQL report with the following code.
Select * from users
This will list all of your users with all of their information. Find the one you want to use and insert their user id into the code I provided above.
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
Spiceworks review – from the help desk guy
Posted by: David
Overall Help Desk Score - 7/10
Spiceworks is a very robust networking system. It does a lot of work for your IT department and has a lot of available resources that allow you to customize it to fit your needs. The help desk/ticketing system in spiceworks is not very bad either. Although since Spiceworks is not exclusively a ticketing system is lacks some core necessities for help desk ticketing systems.
I'll start with the positives.
Positives:
- Easy to use and navigate
- Customizable reports
- Integration with Microsoft Active Directory
- Good user interface for those receiving tickets.
Spiceworks is very easy for the help desk to use and navigate. Once you have it set up for your Help Desk the usage of it is very simple and customizable. If you don't like the ticket categories that spiceworks comes with you can designate your own to meet your companies needs. This also means that you don't have to use this for only IT purposes. If you wanted you could have your entire company have a Spiceworks logon and have them document their work by using the ticketing system.
The customizable reports are amazing. If you have one person in your staff who can work with SQLite and understand the database table structure for Spiceworks you can really start to breakdown your tickets. I personally have made a customizable report for our Help Desk that lets us know who has done the most tickets so far during the month. Since spiceworks allows you to run reports as widgets on the dashboard, all of the help desk can see how they are performing compared to the others. It really keeps the workplace very competitive, but only in a friendly nature. (You can find that report on SpiceWorks Community, it is called "This Month Tickets".)
The integration with our Windows network was also amazing. Now whenever we have problems everything is almost completely automated when people submit their e-mails for tickets. We do the work, the only tickets we have to manually enter in information for is when we get direct calls from users.
Spiceworks has also done a lot of work in updates recently allowing HTML e-mail responses to ticket actions. In the Spiceworks control settings you can change the template to match your corporate colors or customize it to have an appropriate message or even tell Spiceworks when to send e-mails if they are necessary.
Negatives:
- Slow web based controls
- Editing tickets is not easy
- Hard to navigate to see all tickets
Now while Spiceworks has more Positives than negatives, these negatives are rather crucial.
The slow web based controls really slow down timing. When you are on the phone with a user and you need to type information while they are talking it isn't ok to be waiting on the controls to react. When you need to type you need to type fast. Also, the controls are so slow at times that when you make changes you can click on something else and the changes not take effect. The database shouldn't be that slow, the controls on the website are slowing it down so much that sometimes the database changes never get made when you navigate away after making ticket changes.
Now, in the past editing tickets used to be really easy. Now it is incredibly inconvenient. In the past all of the ticket information could be edited at the top of the notes for the ticket. Now you have to hit edit and wait for it to load the editing screen. Once the editing screen is up you have to hit save to return to the notes section. It was much better in the past when all you had to do was edit whatever you wanted for the ticket on one screen.
Also, in the Help Desk ticketing you can't just see someone's tickets by navigating to them. You have to look at all open or all closed tickets or just your own or unassigned tickets. Sometimes you need to look at another persons tickets without sorting through hundreds of other tickets. This is where the customizable reports saves Spiceworks because you can find them with the customizable reports, but you shouldn't have to create a report in Spiceworks just to be able to view someone's tickets in particular.
In review of the review:
Spiceworks is a very flexible and robust system. It's ticketing system however is slow and could use some more features. Once the speed and features of the ticketing system are updated the ticketing system will be hard to beat. I gave the software a 7/10 on its ticketing system because it obviously has a ways to go but for a starting system it has a great start and has a great development team that has always made some good improvements with each update.