Response Time Widget

[Michael Sharp]

Hi,

Is it possible to have a widget that shows the response time with the least amount of time remaining (or most overdue) that is still outstanding.  My idea is the widget will show the ticket reference and/or the time left e.g. 

----SR00030406, 00:08:40----

OR

----IN00054063, --00:02:30----    (for overdue responses)

Even if we could get the time or the ticket on their own, this would be great?  Obviously both would be ace.

Cheers,

Mike.

 

[Drew Davies]

Hi @Michael Sharp

Sounds like something a custom query data list would be able to accommodate fairly easily

 

Example:

SELECT  h_pk_reference, TIMEDIFF(ADDTIME(h_datelogged, SEC_TO_TIME(h_responsesecs)), NOW()) AS Remaining
FROM    h_itsm_requests
WHERE   h_fk_team_name IN ('YourTeamName, NULL)
AND     (h_status='status.open' OR h_status='status.new')
AND     h_withinresponse IS NULL

You could change the last line to   ' AND    (h_withinresponse IS NULL OR h_withinresponse = 0) ' but this would pull back all calls with a breached response until they're resolved

[Josh Bridgens]

@Drew Davies This is excellent, was thinking of a way of providing this for our wallboard but couldn't quite get it right.

 

Josh

[Michael Sharp]

On 1/17/2019 at 1:23 PM, Drew Davies said:

Hi @Michael Sharp

Sounds like something a custom query data list would be able to accommodate fairly easily

 

Example:

SELECT  h_pk_reference, TIMEDIFF(ADDTIME(h_datelogged, SEC_TO_TIME(h_responsesecs)), NOW()) AS Remaining
FROM    h_itsm_requests
WHERE   h_fk_team_name IN ('YourTeamName, NULL)
AND     (h_status='status.open' OR h_status='status.new')
AND     h_withinresponse IS NULL

You could change the last line to   ' AND    (h_withinresponse IS NULL OR h_withinresponse = 0) ' but this would pull back all calls with a breached response until they're resolved

Hi Drew,

I can't seem to get this to work sorry?  I'm getting a SQL error.

Mike.

[Michael Sharp]

Hi, can I get help on this please?

Regards and thanks,

Mike.

[Steve Giller]

I think there's a missing apostrophe after YourTeamName

[Michael Sharp]

Hi, slight adjustment to requirements sorry.  We would like a single number widget to show all requests that have a response SLA present, not met, and upcoming within the next 2 hours (preferably working hours as per the service times) please?  @Gerry copied you in as discussed earlier.

Cheers,

Mike.

[David Hall]

Hi @Michael Sharp

Perhaps you could give this widget set up a try?  Based on the query:

select count(*) from h_itsm_request_slm_targets
where h_name='Response'
and h_target_met is null
and h_state = 0
and (TIMESTAMPDIFF(HOUR, NOW(), h_target_time) > 0 and TIMESTAMPDIFF(HOUR, NOW(), h_target_time) < 24)

Kind Regards,

Dave

 

[Michael Sharp]

5 hours ago, David Hall said:

Hi @Michael Sharp

Perhaps you could give this widget set up a try?  Based on the query:

select count(*) from h_itsm_request_slm_targets
where h_name='Response'
and h_target_met is null
and h_state = 0
and (TIMESTAMPDIFF(HOUR, NOW(), h_target_time) > 0 and TIMESTAMPDIFF(HOUR, NOW(), h_target_time) < 24)

Kind Regards,

Dave

 

Thanks for this @David Hall however I also need the counter to show me responses that have not been serviced that are now overdue within this number?  I basically need a number which allows my agents to know which responses haven't yet been fulfilled and are breach upcoming (in the next 2 hours)?

Please can you also explain the query to me as I can't match that up with the 2 hour label you have set sorry! :-)

Regards and thanks!

Mike.

[Michael Sharp]

To simplify, we need all outstanding responses filtered by current time + 2 hours.

[David Hall]

Hi @Michael Sharp

So I've just had another look, seems I made a typo above, but in any case the widget queries needed a bit more refining.

For the response due in 2 hours try this query which should return all running response targets due within the next 7200 seconds (2 hours):

select count(*) from h_itsm_request_slm_targets
where h_name='Response'
and h_target_met is null
and h_state = 0
and (TIMESTAMPDIFF(SECOND, NOW(), h_target_time) > 0 and TIMESTAMPDIFF(SECOND, NOW(), h_target_time) < 7200)

and for the second counter you could try this which returns running response targets where the target time is in the past

select count(*) from h_itsm_request_slm_targets
where h_name='Response'
and h_target_met is null
and h_state = 0
and TIMESTAMPDIFF(SECOND, NOW(), h_target_time) < 0

Do these give you what you need?

[Michael Sharp]

28 minutes ago, David Hall said:

Hi @Michael Sharp

So I've just had another look, seems I made a typo above, but in any case the widget queries needed a bit more refining.

For the response due in 2 hours try this query which should return all running response targets due within the next 7200 seconds (2 hours):

select count(*) from h_itsm_request_slm_targets
where h_name='Response'
and h_target_met is null
and h_state = 0
and (TIMESTAMPDIFF(SECOND, NOW(), h_target_time) > 0 and TIMESTAMPDIFF(SECOND, NOW(), h_target_time) < 7200)

and for the second counter you could try this which returns running response targets where the target time is in the past

select count(*) from h_itsm_request_slm_targets
where h_name='Response'
and h_target_met is null
and h_state = 0
and TIMESTAMPDIFF(SECOND, NOW(), h_target_time) < 0

Do these give you what you need?

Hi @David Hall

For some reason, both of these queries give me a zero figure which has been verified as incorrect.  Please can you advise?  Further to this, I need both counters merging if possible?

Regards,

Mike.

[David Hall]

Hi @Michael Sharp

Are you using the service based service levels? e.g. do you have any data in the h_itsm_request_slm_targets table?  If not then we would need to revert back to the request table to get this data.

Also I'm not sure I understand what you mean by merging the numbers?  Do you mean put the two counters into the one widget?

Regards,

Dave.

[Michael Sharp]

12 minutes ago, David Hall said:

Hi @Michael Sharp

Are you using the service based service levels? e.g. do you have any data in the h_itsm_request_slm_targets table?  If not then we would need to revert back to the request table to get this data.

Also I'm not sure I understand what you mean by merging the numbers?  Do you mean put the two counters into the one widget?

Regards,

Dave.

Hi @David Hall, we are using the service level response functionality but don't have any entries in the h_itsm_request_slm_targets table??  

As for merging the numbers, I want to be able to address responses that are overdue as well as ones coming up.

Regards,

Mike.

[David Hall]

Hi @Michael Sharp

Looks like you are using the priority based service levels in which case we need to base the query from the requests table instead.

The following query should give you all of the open requests that have either missed the response time or will hit the response time within the next 2 hours.

SELECT h_pk_reference, TIMESTAMPDIFF(SECOND, NOW(), h_respondby) AS RemainingTime
FROM h_itsm_requests
WHERE (h_status='status.open' OR h_status='status.new')
AND h_respondby IS NOT NULL
AND h_responsetime IS NULL
AND ((TIMESTAMPDIFF(SECOND, NOW(), h_respondby) < 0) OR ((TIMESTAMPDIFF(SECOND, NOW(), h_respondby) >= 0) AND (TIMESTAMPDIFF(SECOND, NOW(), h_respondby) < 7200)))

 Perhaps you can try this one out?

Regards,

Dave

[Michael Sharp]

On 5/21/2019 at 11:47 AM, David Hall said:

Hi @Michael Sharp

Looks like you are using the priority based service levels in which case we need to base the query from the requests table instead.

The following query should give you all of the open requests that have either missed the response time or will hit the response time within the next 2 hours.

SELECT h_pk_reference, TIMESTAMPDIFF(SECOND, NOW(), h_respondby) AS RemainingTime
FROM h_itsm_requests
WHERE (h_status='status.open' OR h_status='status.new')
AND h_respondby IS NOT NULL
AND h_responsetime IS NULL
AND ((TIMESTAMPDIFF(SECOND, NOW(), h_respondby) < 0) OR ((TIMESTAMPDIFF(SECOND, NOW(), h_respondby) >= 0) AND (TIMESTAMPDIFF(SECOND, NOW(), h_respondby) < 7200)))

 Perhaps you can try this one out?

Regards,

Dave

Hi Dave,

I had to remove the first 2 lines and the WHERE from my widget to get this working.  Is that correct?

Regards,

Mike.