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.
Installing Crystal Ease for all users
Posted by: David
Why won't Crystal Ease install for all users?
There are two reasons why it might not be working even though you might have installed is successfully.
- You installed the software on a computer using an account that is not an administrator.
- If you did install it as an admin and can't get it to run on other accounts that is because Crystal Ease doesn't support all accounts to run from the initial installation without some modification.
What can you do about it?
A: You installed the software on an account that wasn't an administrator
- Uninstall Crystal Ease.
- Log onto an account that is a computer administrator.
- Install Crystal Ease.
- Enter your release code.
- Enter upgrade release code if necessary.
B: You installed the software as an admin, but want other users to have access to the program.
Since Crystal Ease doesn't support this necessarily, it won't be a popular solution but there isn't much for choices. Crystal Ease will always work on the account which installed it and entered the release codes. Here is what you do for other accounts that will use the computer.
- Go to C:\Program Files\Surfing Gecko Software\Crystal Ease\ (or wherever you installed it)
- Find CEV.EXE and make it a shortcut on the desktop of the account (or all users account) that will be using it. Delete the old shortcut
- When that user goes to open Crystal Ease for the first time, it will ask for the release codes. Unfortunately they will have to have someone enter in the release codes if they don't have access to them.
- Once someone has the release codes set up on their account on a particular computer they can continue opening Crystal Ease as they please.
Big time tip for success
Create a generic account for users to log into if you expect more than a few people to log onto and use Crystal Ease on a computer. This way you will only have to prepare Crystal Ease one time instead of doing it several times.
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