Statistics - SolutionInMin

[dolfiz]

Hi all,

I'm trying to make some report and I'm interested in the value of field "SolutionInMin". As I can see from other posts this value should show how many minutes spent for ticket resolution (from ticket creation till ticket closing). I've made several tries but I was not able to see a report with a ticket having this value different from 0

What I've done:
- defined a custom calendar
- defined a custom service
- linked every Customer Users to the service just created
- linked every queue to the custom calendar just created
- created a SLA linked to custom service created and to the calendar created (Escalation - solution time 30 mins)
- created a custom report (object: Ticketlist, fields: almost all, restrictions: none)

In my OTRS I've got many tickets and some of them are closed (successfull/unsuccessfull). But when I try to download the report via CSV every value of the column "SolutionInMin" and "SolutionDiffInMin" is 0

What am I missing?

Any help appreciated.

Luca

[dolfiz]

Any suggestion? I'm having hard time to find proper documentation for the statistics feature, especially using it in connection with SLA features.

[crythias]

If I recall correctly, EscalationTime and SolutionTime get reset to 0 upon ticket closed, as they are benchmark times (When it needs to happen by), not "when it occurred" times.

[dolfiz]

Actually, Solution Time has same values as Close Time. As far as I know SolutionInMin should calculate total minutes from the difference between Solution and Open times, excluding the time spent on pending states. Does this make sense or I'm wrong?

[crythias]

Does this make sense or I'm wrong?

If SolutionTime = DateTimestamp of solution then that's what it means. When it was solved.

[dolfiz]

Yep. But my hesitation is on the meaning of SolutionInMin more than on Solution Time. Why SolutionInMin is always = 0?

I think that
- or SolutionInMin has a different meaning
- or I missed some steps on SLA-Service-Queue-Calendar-User configuration

I can't see other reasons behind the value always = 0

[MarkusFrank]

Same here,

After Update to OTRS 3.3.5 is the solutiontimemin always 0.
Does anybody has a solution for that?

Is that a known bug??

[dolfiz]

Same here,

After Update to OTRS 3.3.5 is the solutiontimemin always 0.
Does anybody has a solution for that?

Is that a known bug??

I don't have the answers for your requests. Actually, we switched to custom statistics using query on db due to limits of the otrs built-in functionalities. For this reason we did not need to clarify the problem above and we simply applied a math logic based on ticket and ticket_history tables.

P.s.: by the way, we experienced this problem on OTRS 3.3.3.

[MarkusFrank]

Hi dolfiz,

can you explain me how you resolve that problem or is that a company secret?

[dolfiz]

Hi dolfiz,

can you explain me how you resolve that problem or is that a company secret?

We simply develop custom logics that loop ticket_history records computing datetime differences. I can show an example (I took the first one that comes into my hands):

Code: Select all

DROP FUNCTION IF EXISTS compute_ticket_completion_handle_time;
DELIMITER $$
CREATE FUNCTION compute_ticket_completion_handle_time(p_ticket_id INT)
	RETURNS INT
BEGIN
	DECLARE LOG_TAG VARCHAR(64) DEFAULT 'compute_ticket_completion_handle_time';
	DECLARE v_result INT DEFAULT 0;

	DECLARE v_id BIGINT(20);
	DECLARE v_create_time DATETIME;
	DECLARE v_type VARCHAR(200);
	DECLARE v_state_type_id SMALLINT(6);

	DECLARE v_interval_start DATETIME;
	DECLARE v_interval_end DATETIME;

	DECLARE v_is_pending_state BOOLEAN DEFAULT FALSE;
	DECLARE v_is_closed_state BOOLEAN DEFAULT FALSE;
	DECLARE v_is_recording BOOLEAN DEFAULT TRUE;

	DECLARE no_more_rows BOOLEAN DEFAULT FALSE;
	DECLARE current_row INT DEFAULT 0;
	DECLARE num_rows INT DEFAULT 0;

	DECLARE get_history_cur CURSOR
	FOR
	SELECT 
		h.id						AS id,
		h.create_time				AS create_time,
		ht.name 					AS type,
		(SELECT type_id
		FROM ticket_state s
		WHERE s.id = h.state_id)	AS state_type_id
	FROM 
		ticket_history h,
		ticket_history_type ht
	WHERE h.history_type_id = ht.id
		AND h.ticket_id = p_ticket_id
		AND h.history_type_id = 27	-- StateUpdate
	ORDER BY h.id ASC;

	DECLARE	CONTINUE HANDLER FOR NOT FOUND 
	SET no_more_rows = TRUE;

	-- recupero l'istante di PRIMO lock e lo imposto come istante iniziale
	SELECT 
		h.create_time
	INTO
		v_interval_start
	FROM 
		ticket_history h
	WHERE h.ticket_id = p_ticket_id
		AND h.history_type_id = 17	-- Lock
	ORDER BY h.create_time ASC
	LIMIT 1;

	OPEN get_history_cur;

	SELECT FOUND_ROWS()	INTO num_rows;

	get_history_cur: LOOP

		FETCH  get_history_cur
		INTO
			v_id,
			v_create_time,
			v_type,
			v_state_type_id;

		IF no_more_rows THEN
			LEAVE get_history_cur;
		END IF;

		SET current_row = current_row + 1;

		IF v_state_type_id = 4 THEN
			SET v_is_pending_state = TRUE;
		ELSE
			SET v_is_pending_state = FALSE;
		END IF;

		IF v_state_type_id = 3 THEN
			SET v_is_closed_state = TRUE;
		ELSE
			SET v_is_closed_state = FALSE;
		END IF;

		IF v_is_closed_state THEN
			IF v_is_recording THEN
				SET v_interval_end = v_create_time;
				SET v_result = v_result + compute_work_time_diff(v_interval_start, v_interval_end);
			END IF;

			SET v_is_recording = FALSE;
		ELSEIF NOT v_is_pending_state THEN
			IF NOT v_is_recording THEN
				SET v_interval_start = v_create_time;
			END IF;

			SET v_is_recording = TRUE;
		ELSE
			IF v_is_recording THEN
				SET v_interval_end = v_create_time;

				SET v_result = v_result + compute_work_time_diff(v_interval_start, v_interval_end);
			END IF;

			SET v_is_recording = FALSE;
		END IF;
			
	END LOOP get_history_cur;

	CLOSE get_history_cur;

	RETURN v_result;
END;
$$

DELIMITER ;

The compute_work_time_diff function simply computes datetime differences taking into account only the work hours (so, it does not take into account night hours and weekend/holiday days).

By the way, the function, as you can see, check if the ticket is locked, is on pending state and maybe some other condition. But it's just an example, you can adjust it to match your needs.

Hope it helps.

Cheers,
Dolfiz