Hello. Has anyone created a report that would tell you how many tickets that each user has closed in a given amount of time? We'd like a way to evaluate how much work each user is doing. Thanks, Aaron
On Sun, 24 Oct 2004 23:42:50 -0400 (EDT), Aaron
Hello.
Has anyone created a report that would tell you how many tickets that each user has closed in a given amount of time?
We'd like a way to evaluate how much work each user is doing.
Here is an SQL statement that will poll this information. Change the date/time to reflect the time period you want. If you don't want all the way to current date, then add AND t.change_time <= 'YYYY-MM-DD HH:MM:SS' SELECT s.login, count( * ) FROM system_user s, ticket t WHERE s.id = t.user_id AND t.ticket_state_id = '2' AND t.change_time >= 'YYYY-MM-DD HH:MM:SS' GROUP BY s.login Regards, Tyler Hepworth
Tyler:
Thanks so much - this is a great help. Is there a simple query that will
tell us how many tickets each person worked on in a given period?
I'm not even sure how you'd tell if a ticket had been worked on or not -
maybe by looking to see if a message has been added by a user? Maybe a
better way to get this kind of info is to query how many messages each user
has added to tickets in a given period.
Thanks again,
Aaron
-----Original Message-----
From: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of
Tyler Hepworth
Sent: Monday, October 25, 2004 10:58 AM
To: User questions and discussions about OTRS.
Subject: Re: [otrs] Question about reports
On Sun, 24 Oct 2004 23:42:50 -0400 (EDT), Aaron
Hello.
Has anyone created a report that would tell you how many tickets that each user has closed in a given amount of time?
We'd like a way to evaluate how much work each user is doing.
Here is an SQL statement that will poll this information. Change the date/time to reflect the time period you want. If you don't want all the way to current date, then add AND t.change_time <= 'YYYY-MM-DD HH:MM:SS' SELECT s.login, count( * ) FROM system_user s, ticket t WHERE s.id = t.user_id AND t.ticket_state_id = '2' AND t.change_time >= 'YYYY-MM-DD HH:MM:SS' GROUP BY s.login Regards, Tyler Hepworth _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support oder Consulting f|r Ihr OTRS System? => http://www.otrs.de/
On Wed, 27 Oct 2004 00:16:26 -0400, aaron
Tyler:
Thanks so much - this is a great help. Is there a simple query that will tell us how many tickets each person worked on in a given period?
I'm not even sure how you'd tell if a ticket had been worked on or not - maybe by looking to see if a message has been added by a user? Maybe a better way to get this kind of info is to query how many messages each user has added to tickets in a given period.
No I'm sorry I don't have anything to show tickets that an agent worked on. That hasn't been a priority for me so I haven't looked into it. However, this is what I would do (after a quick look at the database) Use the tables: system_user ticket_history ticket_history_type ticket_history contains "create_by" and "history_type_id" entries. The create_by entries identify the operating agent. The history_type_id entries define the kind of action performed. To get logical names instead of integer identifiers, you have to use the system_user table and ticket_history_type table. Search ticket_history for events that you want to know about and what agents performed them. Hth, Tyler Hepworth
participants (2)
-
Aaron -
Tyler Hepworth