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:

1. In spiceworks click on the reporting tab
2. Click on New Report
3. Make sure "Build this report using SQL" is checked
4. 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.