Create report based on Agents involved

[Cleverton]

Hi,

Is it possible to generate a report where I can list tickets filtered by the involved agents?

The tickets on our OTRS installation are treated by multiple agents, that transfer the ticket based on the necessity, so, to measure the tickets by agent using only the default options (filtered by the original creator or the last owner of it), doesn't reflects the actual ticket count treated by an agent.

[wurzel]

Hi,

you can enable Agent Element in Stats in sysconfig.

But you may not receive your needed statistics with this option. But it is worth a try.


Flo

[Cleverton]

Hi wurzel,

Thanks for the answer.

I have the option already enabled on sysconfig, but it only filter tickets created by an agent, or the last owner of the ticket, not the other agents involved on the ticket treatment.

I made a workaround creating a multiselect dynamic field with all agents (except the ones that always are the last owners of the tickets) and configuring some Generic Agent instructions to mark the current owner of a ticket, triggered by owner update. Then, making a report that counts tickets based on that dynamic field data.

The problem on that is, as I am currently checking, the Generic Agent overwrites the last saved data on the Dynamic Field, even if it's a multiselect field, so it only works if there's a single agent involved other than the last owner of the ticket.

[wurzel]

Hi,

the statistic module does not support the agent-history data. For this you need to build your own select statements with an SQL Tool on the ticket history table. (but please do not ask me how)

Flo

[crythias]

An example of what you're trying to accomplish that makes some sort of sense would be helpful. ...

For instance, starting with

Code: Select all

SELECT t.tn as Ticket_Number, u.login as Owner, resp.login as Responsible, inv.login as Article_creator, count(inv.login) as Articles_created, q.name as queue FROM `article` as a left join `ticket` AS t on t.id=a.ticket_id left JOIN queue as q on q.id=t.queue_id left join users AS u on u.id=t.user_id left join users as resp on resp.id=t.responsible_user_id left join users as inv on inv.id=a.create_by group by Ticket_Number,Article_creator limit 10

You'll have a ticket number, owner, responsible, a list of article creators, the number of articles each created and what's the ticket's queue for tickets.

Add something else... maybe you want to know how much self-reported time they accumulated on the ticket.

Code: Select all

SELECT t.tn as Ticket_Number, u.login as Owner, resp.login as Responsible, q.name as queue, inv.login as Article_creator, count(inv.login) as Articles_created, sum(ta.time_unit) as Time_reported FROM `article` as a left join `ticket` AS t on t.id=a.ticket_id left JOIN queue as q on q.id=t.queue_id left join users AS u on u.id=t.user_id left join users as resp on resp.id=t.responsible_user_id left join users as inv on inv.id=a.create_by left join time_accounting as ta on ta.article_id=a.id group by Ticket_Number,Article_creator order by Ticket_Number desc limit 100

This would be cumulative time over the whole article table.

You might be interested in articles created in a specific time frame (here 1 week?):

Code: Select all

WHERE a.create_time >= ( CURDATE() - INTERVAL 1 WEEK )

Or specific types of articles, etc.