Ticket Audit Time SQL Report Query Offering

joegass · 2021-09-17T13:46:54.000Z

Hello Spicies,

A couple of years ago I had posted asking for help on a SQL report that returns some useful information. Assignee, average response time, number of tickets responded to, close time, that kind of thing. We needed the report to only look at and report back for a specific timeframe. For example if someone submits a ticket at 4:50 PM and my tech does not get to it until 8:10 the next day, the response time for that ticket would be 20 minutes since our hours are 8-5. it was not acting as we thought it should. The original link, including the query, instructions for parameters, and links to referenced posts is here .

The issue was that the report was returning inconsistent or “bad” data… information would change from month to month AFTER the month was closed. Long story short, we hired someone to re-write the report for us. In the spirit of giving back, I wanted to share that new query in the hopes that it will help someone else out. There is a section to ignore specific categories (we don’t count HR [onboarding/offboarding] times against the techs), which you may edit. There is also space at the end of the report to exclude specific techs.

feel free to ask questions.

This is for the On-prem version of spiceworks since you can’t run SQL reports in the Cloud based version.

10016d90-5756-44ff-af12-4837021b17cd-Screenshot_2021-09-17_114543.jpg

select 
    case when CT.a_month is not null then CT.a_year || '-' || CT.a_month else RT.a_year || '-' || RT.a_month end as "Month",  
    RT.assignee as "Assignee", 
    sum(RT.tickets_responded) as "Tickets Responded To", 
    round(avg(RT.response_time), 2) as "Response Time (hr)", 
    sum(coalesce(CT.tickets_completed, 0)) as "Tickets Completed", 
    round(avg(coalesce(CT.completion_time, 0)), 2) as "Completion Time (hr)"
from
(select u.first_name || ' ' || u.last_name as assignee, strftime('%Y', C.created_at) as a_year, strftime('%m', C.created_at) as a_month, t.id as ticket_id, count(T.id) as tickets_responded,
      ROUND(avg(
						((CAST((
  								julianday(date(c.created_at))
  								-julianday(date(t.created_at))
								+1)AS INTEGER) --as P1
					-CASE WHEN julianday(date(c.created_at)) = julianday(date(t.created_at)) THEN -1
					ELSE ((cast((julianday(c.created_at)-julianday('1989-01-01')) / 7 as Integer)
      					-cast((julianday(t.created_at)-julianday('1989-01-01')) / 7 as Integer))*2)
					  end
				 
					- 2 /* Remove StartDate and EndDate from full days */
					  /*-(case WHEN strftime('%w', DATE(t.created_at)) = '6' then -1 else 0 end)
					  -(case WHEN strftime('%w', DATE(c.created_at)) = '0' then -1 else 0 end)*/
					)*8
	)+case WHEN strftime('%w', DATE(t.created_at)) = '0' then 0
        WHEN strftime('%w', DATE(t.created_at)) = '6' then 0
      /* Special Handling for created and closed same day */  
      	when date(c.created_at) = date(t.created_at) and cast(strftime('%H', t.created_at) as real) < 17
      	THEN	case when cast(strftime('%H', c.created_at) as real) < 9 
        			THEN 9
					 when cast(strftime('%H', c.created_at) as real) >= 17
					THEN 17
					ELSE (cast(strftime('%H', c.created_at) as real) + (cast(strftime('%M', c.created_at) as real) / 60))
        			END
        		- 
				case when cast(strftime('%H', t.created_at) as real) < 9
					THEN 9
					ELSE (cast(strftime('%H', t.created_at) as real) + (cast(strftime('%M', t.created_at) as real) / 60))
        			END
        ELSE case when cast(strftime('%H', t.created_at) as real) < 9 
        			THEN 8
        		  WHEN cast(strftime('%H', t.created_at) as real) < 17        			
					then 17 - cast(strftime('%H', t.created_at) as real) - 1 + ((60 - cast(strftime('%M', t.created_at) as real)) / 60) 
					else 0 END
        END
	+case WHEN strftime('%w', DATE(c.created_at)) = '0' then 0
              WHEN strftime('%w', DATE(c.created_at)) = '6' then 0
              when date(c.created_at) = date(t.created_at) THEN 0
              when cast(strftime('%H', c.created_at) as real) >= 17
        		THEN 8
        	  when cast(strftime('%H', c.created_at) as real) >= 9 
					then cast(strftime('%H', c.created_at) as real) - 9 + (cast(strftime('%M', c.created_at) as real) / 60) 
					else 0 end), 2) as response_time
 from tickets t
    inner join users u on t.assigned_to = u.id and t.category not in ('Waiting on vendor', 'IT Team', 'HR', 'Other', 'Unspecified') /*add or remove categories here*/
    left join comments C on C.id = (select IC.id from comments IC inner join users U on IC.created_by = U.id where IC.ticket_id = T.id and IC.created_at >= T.created_at and ((U.role != 'end_user' and U.first_name || U.last_name != 'SystemAdmin' and IC.body not like 'Assigned to %') or IC.created_by = T.assigned_to)  order by IC.created_at limit 1)
    group by 1, 2, 3, 4) as RT
left join
(select u.first_name || ' ' || u.last_name as assignee, strftime('%Y', T.closed_at) as a_year, strftime('%m', T.closed_at) as a_month, t.id as ticket_id, count(T.id) as tickets_completed,
      round(avg(
	((CAST((
  				julianday(date(t.closed_at))
  				-julianday(date(t.created_at))
				+1)AS INTEGER) --as P1
	-CASE WHEN julianday(date(t.closed_at)) = julianday(date(t.created_at)) THEN -1
    ELSE ((cast((julianday(t.closed_at)-julianday('1989-01-01')) / 7 as Integer)
      	-cast((julianday(t.created_at)-julianday('1989-01-01')) / 7 as Integer))*2)
      end
	- 2 /* Remove StartDate and EndDate from full days */
      /*-(case WHEN strftime('%w', DATE(t.created_at)) = '6' then -1 else 0 end)
      -(case WHEN strftime('%w', DATE(t.closed_at)) = '0' then -1 else 0 end)*/
	)*8
	)+case WHEN strftime('%w', DATE(t.created_at)) = '0' then 0
        WHEN strftime('%w', DATE(t.created_at)) = '6' then 0
      /* Special Handling for created and closed same day */  
      	when date(t.closed_at) = date(t.created_at) and cast(strftime('%H', t.created_at) as real) < 17
      	THEN	case when cast(strftime('%H', t.closed_at) as real) < 9 
        			THEN 9
					 when cast(strftime('%H', t.closed_at) as real) >= 17
					THEN 17
					ELSE (cast(strftime('%H', t.closed_at) as real) + (cast(strftime('%M', t.closed_at) as real) / 60))
        			END
        		- 
				case when cast(strftime('%H', t.created_at) as real) < 9
					THEN 9
					ELSE (cast(strftime('%H', t.created_at) as real) + (cast(strftime('%M', t.created_at) as real) / 60))
        			END
        ELSE case when cast(strftime('%H', t.created_at) as real) < 9 
        			THEN 8
        		  WHEN cast(strftime('%H', t.created_at) as real) < 17        			
					then 17 - cast(strftime('%H', t.created_at) as real) - 1 + ((60 - cast(strftime('%M', t.created_at) as real)) / 60) 
					else 0 END
        END
	+case WHEN strftime('%w', DATE(t.closed_at)) = '0' then 0
              WHEN strftime('%w', DATE(t.closed_at)) = '6' then 0
              when date(t.closed_at) = date(t.created_at) THEN 0
              when cast(strftime('%H', t.closed_at) as real) >= 17
        		THEN 8
        	  when cast(strftime('%H', t.closed_at) as real) >= 9 
					then cast(strftime('%H', t.closed_at) as real) - 9 + (cast(strftime('%M', t.closed_at) as real) / 60) 
					else 0 end), 2) as completion_time
	 from tickets t
	 inner join users u on t.assigned_to = u.id
	 where t.status = 'closed' 
    group by 1, 2, 3, 4) as CT
on RT.ticket_id = CT.ticket_id
/* want to exclude one or more techs from the report? Put their names on the next line: */
where RT.assignee not in ('Excluded Tech 3', 'Tech 2', 'Tech 1')
group by 1, 2
order by 1, 2

scheff1 · 2021-09-17T17:17:47.000Z

BackersofPackers:

This is for the On-prem version of spiceworks since you can’t run SQL reports in the Cloud based version.

Thanks for sharing and for giving back.

But are you aware the the on-premise edition is already legacy under limited support and reaching EOL by end of this year?
Which migration plans do you already have?
Or which alternate precautions did you take in order to remain able to use the on-premise edition after reaching EOL?

You’ll find these announcements in the Spiceworks product roadmap and in this article . I only don’t understand why a product roadmap claiming to be actual as of 6 November 2020 including data and links published half a year later. Seems to me the mysteries of the Spiceworks time machine. (Couldn’t find neither the URL, terms and conditions nor product reviews to this time machine.)