Tickets solved by Agent in specific time range

Moderator: crythias

Post Reply
rafnic
OTRS newbie
Posts: 2
Joined: 19 Mar 2018, 12:39
OTRS Version?: OTRS 5

Tickets solved by Agent in specific time range

Post by rafnic » 19 Mar 2018, 15:02

Hello,
I would like to know how many tickets were solved at a certain time.

For example:
Agent X worked two hours on Saturday
Agent Y worked 8 hours on Sunday

How many tickets were closed directly and tickets that were set to pending autoclose on that day.

In the OTRS itself I find no possibilities to indicate times.

Right now I have the problem that my SQL query also ejects tickets where the agent had already set the ticket to autoclose a few days before. For example, I'm issued an agent who didn't work on the weekend but closed 50 tickets. (autopending tickets from the week before)
But I only need the values for the day itself.

Code: Select all

SELECT CONCAT( first_name,  ' ', last_name ) Name, u.login user, COUNT( t.id ) AS  '# of tickets closed'
FROM ticket t
LEFT JOIN users u ON u.id = t.user_id
AND u.valid_id =1
LEFT JOIN queue q ON q.id = t.queue_id
LEFT JOIN ticket_state ON ( ticket_state.id = t.ticket_state_id ) WHERE (ticket_state.id = '2' or ticket_state.id = '3' or ticket_state.id = '7' or ticket_state.id = '8')
AND t.change_time between '2018-03-16 18:00:00' AND '2018-03-19 05:00:00'
GROUP BY u.id

seberget2
OTRS expert
Posts: 71
Joined: 17 May 2013, 09:30
OTRS Version?: 6.0.4
Real Name: Stein Erik Berget

Re: Tickets solved by Agent in specific time range

Post by seberget2 » 19 Mar 2018, 18:42

If you are not using the Time accounting module, I would have had a look at the ticket_history table. If you are using Time accounting to record time spent on tickets, look at the time_accounting* tables.

Hope this help you a bit further!
OTRS 6.0.4 on Ubuntu with MySQL DB, and various plug-ins and a hack or two :-D

rafnic
OTRS newbie
Posts: 2
Joined: 19 Mar 2018, 12:39
OTRS Version?: OTRS 5

Re: Tickets solved by Agent in specific time range

Post by rafnic » 20 Mar 2018, 09:11

@seberget2 thx for reply

i dont use the time accounting module and don't want to know how long an agent spent on a ticket.

I would like to know how many tickets has an agent closed on Saturday between 12:00 and 19:00 and another agent closed on Sunday between 9:00 and 15:00? or on the day set to pending autoclose.

An idea for an SQL query?

seberget2
OTRS expert
Posts: 71
Joined: 17 May 2013, 09:30
OTRS Version?: 6.0.4
Real Name: Stein Erik Berget

Re: Tickets solved by Agent in specific time range

Post by seberget2 » 20 Mar 2018, 14:52

[quote="rafnic"An idea for an SQL query?[/quote]
The below SQL is probably a bit more verbose than need be.

Code: Select all

SELECT
	U3.login AS 'login',
	TH.ticket_id,
    TH.change_time

FROM
	otrs.ticket_history AS TH,
	otrs.ticket_history_type AS THT,
	otrs.users AS U3,
	otrs.ticket_state AS TS,
	otrs.ticket_state_type AS TST

WHERE
		TH.history_type_id = THT.id
	AND
		TH.change_by = U3.id
	AND
		TH.state_id = TS.id
	AND
		TS.type_id = TST.id
	AND
		THT.name = 'StateUpdate'
	AND
		TST.name = 'closed'
Divide an conquer as needed, you will need to add your filters for a time range, a count and a group by. Please note that a ticket might be counted more than once, if it has been put into state 'closed' more than once. Choose your time period with care. It should be possible to select the 'final state' of an article in given time period to make sure it is counted once.

Best of luck.
OTRS 6.0.4 on Ubuntu with MySQL DB, and various plug-ins and a hack or two :-D

Post Reply