Response time widget

[Alisha]

Hello,

I was wondering if it would be possible to create a widget to see how many requests have not been responded to within 30 minutes of it being logged by self-service. The 30 minutes is actually before our response timer.

I was thinking about doing something like this, but don't know if I'm going about it in the right way. I'm hoping someone with a background in SQL might be able to help. 

SELECT h_pk_reference AS Reference, DATE_FORMAT(h_itsm_requests.h_responsetime,' %H:%i') AS 'Response Time',
WHERE h_itsm_requests.h_responsetime BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 MINUTE)
AND h_requesttype IN ('Incident', 'Service Request')  
AND h_status IN ('status.open', 'status.new')

Many thanks,
Alisha

[Conor]

Hi @Alisha

 

Try this 

SELECT h_pk_reference AS Reference, SEC_TO_TIME(h_itsm_requests.h_responsetime) AS 'Response Time'
from h_itsm_requests where h_responsetime > 1800

 

[Alisha]

Hi @Conor,

This is excellent, thank you.

Is there a way to only keep the requests that haven't been responded to on the widget? So if the request hadn't been responded to within 30 minutes it would show, and then it gets removed after it has been responded to.

Many thanks,
Alisha

[Steve Giller]

@Alisha h_withinresponse is set when the Request is responded to, so testing for h_withinresponse IS NULL in the WHERE clause should achieve this, I think.

[Alisha]

Hi @Steve Giller @Conor,

The widget is as below, but it doesn't seem to work. We definitely have requests in the queue that haven't been responded to within 30 minutes. Is there something I'm missing?

SELECT h_pk_reference AS Reference, SEC_TO_TIME(h_itsm_requests.h_responsetime) AS 'Response Time'
FROM h_itsm_requests WHERE h_responsetime > 1800
AND h_withinresponse IS NULL
AND h_fk_servicename = 'IT Services'
AND h_requesttype IN ('Incident', 'Service Request')  
AND h_fk_team_name = 'IT Service Desk'
AND h_status IN ('status.open', 'status.new')
ORDER BY h_responsetime desc

Many thanks,
Alisha

[Steve Giller]

@Alisha Try:
 

SELECT h_pk_reference AS Reference, TIMEDIFF(NOW(), h_datelogged) AS 'Response Time', h_withinresponse
FROM h_itsm_requests WHERE TIME_TO_SEC(TIMEDIFF(NOW(), h_datelogged)) > 1800
AND h_fk_servicename = 'IT Services'
AND h_requesttype IN ('Incident', 'Service Request')  
AND h_fk_team_name = 'IT Service Desk'
AND h_status IN ('status.open', 'status.new')
ORDER BY 'Response Time' desc

If I'm understanding the requirement correctly this should return anything that was logged more than half an hour ago, is an Incident or Service Request under the 'IT Services' Service, owned by the 'IT Service Desk' team, and has a status of New or Open.
I think the h_withinresponse value may be populated before the Request is responded to (e.g. to set the 'breached' status) so I'm looking into that before I can add the final filter.

[Conor]

Hi @Alisha

This is tricky because the h_responsetime field is only set when the ticket is responded to, same with the h_withinresponse field as @Steve Giller mentions. The h_respondby field is populated when the response timer starts so we could use that along with the h_datelogged field for the 30 minute bit...

So you could try something like this:

select h_pk_reference AS Reference, TIMEDIFF(NOW(), h_datelogged) AS Response from h_itsm_requests 
where h_withinresponse is null 
AND h_datelogged < NOW() - INTERVAL 30 MINUTE 
AND h_respondby > NOW() 

This will only show tickets that still haven't breached the response time though, not sure if that is important? You could extend that  value on the last line to an hour or two in the same way as the line above it, like this:

AND h_respondby > NOW() + INTERVAL 60 MINUTE

As soon as the ticket is responded to the h_withinresponse will be populated and won't be null anymore, so will then be excluded from the widget.

The TIMEDIFF bit will still give the counter on the widget for the amount of time since the ticket was logged.

Obviously add in your own clauses as above, but I think this is the kind of thing you are after...

[Alisha]

@Conor Thank you so much!