1

Hi,

Can someone help me on generating or creating a report showing itemized time spent on a ticket by multiple tech/agent based on specific update date with the total time per month?

Example: ticket 1152 created June 1, 2014 and still open or re-open August 2014.

Sort by Update date- July 1 to 31.

Updated by: Nathan (2hrs - July 21); Paul (3hrs - July 30) …

Total Time Spent: 5hrs

I don’t want June & August update or hours to show up.

Thanks,

Myra

3 Spice ups
2

Hey Myra,

It sounds like you’ll want to create a SQL report within Spiceworks to grab that data. I’m going to flip this post over the “Extending Spiceworks” forum as a number of the folks there have a lot of SQL knowledge and should be able to help out.

3

Have you tried looking through the shared reports to see if there is a report that does this already? that is likely the best way to start.

here is the link to the shared reports

http://community.spiceworks.com/reports

4

Thanks guys for your immediate response.

I have tried looking on those published reports from the community site but haven’t found exactly what I needed (unless I missed something). The closes SQL program we have is Time Spent on Tickets Last Day (that displays the total amount of time per tech last day with graph).

When I try generating a report based on update date currently (ex. June 1 to 30; ticket #: 1143), it just gives me the total time spent by all tech and the ticket # doesn’t show up next month (July 1 to 31) even though there’s clearly an update made.

I really need an SQL program that will itemized the time spent by all tech (w/ total) who updated a ticket based on the required update date with graph.

Our target minutes per month is 48000 and my boss want’s to know if everyone is doing their job.

Please help guys.

Thanks,

Myra

5

A report that tracks the Tech assigned work hours on a specific ticket real-time.

6

Here’s the SQL Time Spent on Tickets for Today programming but the problem is, it gives the actual work time of the tech on which the ticket is assigned to not the real-time update by each tech (multiple tech) on a specific ticket.

Can someone (SQL programmer) help me modify this syntax to generate an accurate result please?

SELECT first_name ||" "||Last_name as Admin,
(sum(ticket_work.time_spent)/3600) ||‘.’
|| (case round((sum(ticket_work.time_spent)%3600)/60)
when 0 then ‘0’
when 1 then ‘0’
when 2 then ‘0’
when 3 then ‘0’
when 4 then ‘0’
when 5 then ‘0’
when 6 then ‘0’
when 7 then ‘0’
when 8 then ‘0’
when 9 then ‘0’
else ‘’
end) || ((sum(ticket_work.time_spent)%3600)/60) as "Work

Time"
from tickets,ticket_work,users
Where (tickets.assigned_to=users.id) and

(ticket_work.ticket_id = tickets.id) and

(ticket_work.updated_at >=date(‘now’)
group by Admin
Order by (sum(ticket_work.time_spent)/3600) DESC

7

Hi Myra,

You may have a look at the report below, where is shown, for each admin, the number of worked tickets and total worked time for current month.

Just a small note, for SQlite the ‘now’ modifier returns UTC date&time and therefore you’ll need that extra ‘localtime’

I guessed that you need worked time formatted as HH.MM

SELECT 
  (select first_name ||" "||last_name from users where users.id=tw.user_id) as Admin,
  count(distinct tw.ticket_id) as "Worked tickets",
  replace(strftime('%H:%M',sum(tw.time_spent),'unixepoch'),':','.') as Work_Time
  from tickets t join ticket_work tw on t.id = tw.ticket_id
  where 
  date(tw.created_at) >=date('now','localtime','start of month')
  group by Admin
  Order by work_time DESC
1 Spice up