Jump to content

Response Time Widget


Recommended Posts

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.

 

Link to post
Share on other sites

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

  • Thanks 1
Link to post
Share on other sites
  • 1 month later...
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.

Link to post
Share on other sites
  • 1 month later...
  • 1 month later...

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.

Link to post
Share on other sites

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)

image.png

Kind Regards,

Dave

 

Link to post
Share on other sites
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)

image.png

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.

Link to post
Share on other sites

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?

Link to post
Share on other sites
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.

Link to post
Share on other sites

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.

Link to post
Share on other sites
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.

Link to post
Share on other sites

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

Link to post
Share on other sites
  • 4 weeks later...
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.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...