Agent Ticket closed monthly SQL

[PriteshP23]

Hello,
Do you know sql to get ticket information which are closed by an agent in one month ?

Need these details in sql:
ticket_number / creation_date / closed / first_lock / first_response / state / queue / locked /owner /user_first_name /user_last_name
customer_code / customer_realname / from

What i did so far:

Code: Select all

select 
ticket.tn as ticket_id, 	
ticket.create_time,
ticket.ticket_state_id,
ticket.queue_id as queue_id,
ticket.create_by AS owner,
ticket_history.change_time AS closed,
ticket_state.name,
queue.name,
article.a_to as FROM,
users.first_name,
users.last_name,
users.login AS owner
FROM ticket
join ticket_history ON ticket.id = ticket_history.ticket_id
join queue ON queue.id = ticket.queue_id
join users ON users.id = ticket_history.owner_id
join article ON article.create_by = ticket_history.create_by
join ticket_state ON ticket_state.id = ticket.ticket_state_id
WHERE ticket.create_time between 'XXXX' and 'XXXX' AND users.id = XX

No idea for First_response time and FirstLock time

Thanks in advanced!

[wurzel]

Hi,

why not use the statistic module for that? Should be possible without SQL.

Flo

[PriteshP23]

why not use the statistic module for that? Should be possible without SQL.

Because i dont want agent can track his performance. thanks. any idea for sql to get First_response time and FirstLock time ?

[reneeb]

If you use the stats module, you can set the group to define who can use the stat...

[PriteshP23]

@raneeb

I see. But there is no First_response time and FirstLock time in otrs Stat module.
There is a huge database to look over and get all details via SQL because for stat module it took more than 8 hours to display on dashboard and i have not all info that i want.
Thanks !