SLA Responsetime and Fixtime values incorrect

[Lyonel]

Hi guys,

I don't know if ti is a known issue or if you are aware of this but I am having troubles with my reporting regarding SLAs and performances. Indeed, the fields h_responsetime and h_fixtime in the requests table do not seem to match what I can see on screen. For example, here is my request:

And what I have in the database for that same request:

As you can see, none of the values match what is on screen...

One common denominator in pretty all of the similar cases is that the priority and the SLA was changed at some point during the life of the requests.

Any idea on:

• Is it an issue? 
• Are you aware of it?
• Where does the screen gets its data from?
• Which table should I query to get the correct data for my report?

Thanks in advance for your help.

[Lyonel]

Might have found something, but I cannot use it in a report...

SQL Query:

SELECT h_name, h_target_duration, h_target_met, h_target_start, h_target_completed_time, TIME_TO_SEC(TIMEDIFF(h_target_completed_time, h_target_start)) as diff
FROM h_itsm_request_slm_targets 
where h_request_id in ('IN00019039') and h_state = 0

The "diff" column gives me a more or less accurate value (albeit does not consider working hours!). Even if this could work, I cannot do calculations in my report...

[David Hall]

Hi @Lyonel,

As you have already identified the data shown on the request details targets is driven from the h_itsm_request_slm_targets table.

If you are just looking to identify how long the target took to be completed in seconds, then there is a column in that same table named "h_target_completed_duration" that should give you that data.

If that is not what you are looking for then let me know what you are trying to get as an output and I'll see if I can advise on that.

Kind Regards,

Dave

[Lyonel]

@David Hall thanks for the tip! I totally missed that column  I feel really silly right now...

I just changed my SQL and it works like a charm:

That still does not explain why the requests table does not contain the correct information, which defies the point of having these columns. I would like to hear from Hornbill on this particular point.

But at least thanks to you @David Hall I can carry on my reporting 

[David Hall]

Hi @Lyonel,

Glad you have the information you need

With regards to the columns in the requests table, I will review why these columns are still being set for the response in your case, however there is no negative effect from these values being populated.  These columns are used for the original legacy implementation of service levels where we only maintained a fix and response timer.  When the current implementation of SLM (that you are using) was introduced we altered the data structure so that the target data was in its own table so that should we need to add more target types in future then we could do so, however we also needed to maintain backwards compatibility hence the columns remain in use in the requests table for some users.

Regards,

Dave.

[Lyonel]

@David Hall it would be really fantastic if the columns in the request table could also be updated accordingly as it prevents having to join tables together in reports or measures / widgets. The fact that these columns are available in the requests table is absolutely fantastic, but it would be top class if it could also have the correct data inside.

I use them a lot in measures and reports, so what do you think I should do:

• Change all my measures and reports to look into h_itsm_request_slm_targets table?
• Wait until you "fix" this?

[Steve Giller]

@Lyonel from the previous posts I don't think there will be a "fix" as nothing is broken.

It appears you've changed from using Priorities to using Service Levels - therefore you can no longer report on Priorities and have to start reporting on Service Levels, to change the contents of the database would break it for everyone who is still using Priorities.

(I appreciate that's easy for me to say, not being the one who has to update a billion measures, however, that is my understanding of what's happened.)

[Lyonel]

@DeadMeatGF, @David Hall I did not quite understand the legacy bit hence me mentioning a potential "fix". Apologies if it was misunderstood. It's just that for reporting purposes, having the right data in the columns direcly in h_itsm_requests table would far more beneficial for all, whether you use simple priorities or SLM. Now I am sure it is not that simple to change, but if Hornbill could at least consider it and think about it would be nice. 

And if it simply not compatible with the legacy, then so it is and I will change all my reporting. It represents a large amount of hours that I would rather not spend if I don't need to 

[David Hall]

Hi @Lyonel,

Sorry by "legacy" I meant that the first version of service levels in service manager was only ever allowed for 2 timers (response & fix) so the data was stored in the requests table.  When we implemented the new version of SLM in service manager we altered the underlying structure to be more flexible for the future (hence the h_itsm_request_slm_targets table) allowing us the option to add more timers in the future, but we still had to maintain the request table columns for users of the original service level implementation.

With all that said, having reviewed the flowcodes that record the response and fix times in the BPM (Application->Timer->Mark Response Time && Application->Timer->Mark Resolve Timer), both should still be populating the columns in the requests table that you highlighted in your image. I tried a very quick test locally and after marking the response the response data was present, and after marking the resolution that data was present.  Perhaps you can try another test through to resolution and see if the data is marked? 

[Lyonel]

@David Hall that's what we've been doing from day 1:

But if the SLM gets changed during the life of the request (Incident for example), then the fields in h_itsm_requests do not get updated "properly" but h_itsm_request_slm_targets has the right information. So if you are saying that it should update h_itsm_requests table then something is not working as expected.

[David Hall]

Hi @Lyonel,

I've tried a basic scenario of logging a request, marking the response, changing the service level, then resolving the fix time but this still populates all of the data for me.  I can only assume there is a specific scenario which means the data is not being populated or is being reset.  I'll try a few more things and see if I can identify how this may be happening.

Regards,

Dave.

[Lyonel]

@David Hall I already have a call logged with you guys if it is of any value which shows a few strange examples of SLM behaviour: IN00146205. You might find more information on some of the scenarios on your system.

[David Hall]

Hi @Lyonel,

Thanks for that..will see if I can identify anything. 

Regards,

Dave.

[David Hall]

Hi @Lyonel,

I've just been able to access the log files for your instance and look to see what happened in terms of setting the data for the example request you mentioned above "IN00019039".

I can see in the logs that when the request was resolved, 2 update calls were made, 1 to the data in the requests table and 1 to the targets table.. so at that point the data should have been complete and matching in both locations, there were no errors to suggest the update failed.  If they are now empty as per your screenshot then I can only guess that they must have been reset after resolution? Were there any actions on that request after the resolution at 10:16?  If so then I may need to look at those events to see if the values are reset, but the only place I can see that we would reset them is when changing an SLA.

Regards,

Dave.

[Lyonel]

@David Hall nope nothing, except for the closure on 31/03/2017 09:51:32