On 17 May 2008, Jay Holler wrote:
On Thu, May 15, James Taylor <wrote:
On 15 May 2008, Jay Holler wrote:
I am implementing an OTRS server in my organization and I am
looking for a way to identify the number of hours reported in the
"Time units (work units):" field by an agent when closing a
ticket on a monthly basis by client. I have searched the list
and website but have been unable to find a way to do this.
We need exactly the same thing and have been equally surprised not
to be able to find such a basic report in OTRS already. I have
been using a complex SQL query in the MySQL shell to extract a
monthly table of time used by ticket for each customer, and I can
show you the SQL for this if you'd like to try doing the same.
It's a rather manual process though. :-(
That would be great if you could share your solution. Right now it
appears to be the only way to do it. Rather unfortunate as the rest
of the OTRS system handles our needs quite nicely.
Oops, I've not been checking this list for a while, sorry.
Log into your OTRS box and use the mysql shell to talk SQL to the
database directly like so:
mysql -D otrs -u otrs -p
Enter password:
The -D option specifies the name of the database, the -u is the name
of the user, and -p means to prompt for the password. The correct
values for all these can be found in the /opt/otrs/Kernel/Config.pm
file if you've forgotten them.
Then at the mysql> prompt enter the SQL query. I suggest you
cut'n'paste my example below into a text editor and tidy it up for
your own purposes, then paste the result into the mysql> shell.
Now, in our case, we decided to charge customers each month by how
much agent time they used in that month, *regardless* of whether the
ticket was opened or closed in the current month. This seemed
altogether more manageable than trying to keep track of whether
tickets were closed, and also avoids the issue of having to wait for
a long-running issue/ticket to be closed before getting any money for
(especially when customers can reopen a ticket simply by sending
another email and could keep that going month after month if it were
a regular request).
This first SQL query simply lists how much time each customer (that
used *any* time) has used in the specified time period in a simple
two column table:
select
case when company.name is null then ticket.customer_id else
company.name end company,
sum(time.time_unit) mins
from time_accounting time
left join ticket
on time.ticket_id = ticket.id
left join customer_user cust
on ticket.customer_user_id = cust.login
left join customer_company company
on cust.customer_id = company.customer_id
where (company.name is null or company.name != 'Intern')
and time.create_time >= '2008-04' and time.create_time < '2008-05'
group by company
order by company;
Each month we've been running that same query for the month just
ended by changing the start and end dates (2008-04 and 2008-05 in
this case, ie. all of April). You may be able to tidy it up, for
instance "Intern" was a dummy company we set up for internal testing,
and companies with null names probably ought not to exist but in our
first fumblings this was unfortunately common.
This second query is the main one for listing time spent handling
each ticket, and it requires more explanation (see below):
select
case when cust.first_name is null then '' else cust.first_name end
first_name,
case when cust.last_name is null then '' else cust.last_name end
last_name,
case when company.name is null then ticket.customer_id else
company.name end company,
ticket.tn ticket,
left(ticket.title,60) ticket_title,
time.time_unit mins
from time_accounting time
left join ticket
on time.ticket_id = ticket.id
left join customer_user cust
on ticket.customer_user_id = cust.login
left join customer_company company
on cust.customer_id = company.customer_id
where (company.name is null or company.name != 'Intern')
and time.create_time >= '2008-04' and time.create_time < '2008-05'
group by ticket.tn
order by company, time.create_time;
This produces a six column table that's 144 chars wide in my terminal
window, although yours may differ depending on your data.
Fortunately, my terminal emulator allows me to reduce the font size
until this becomes readable. (I did tell you this was a nasty hack
didn't I?) The number 60 in left(ticket.title,60) truncates the
ticket title to a maximum length of 60 characters because people do
silly things like pasting long URLs into email subject lines. You
could reduce this number to make the table less wide.
The "mins" column is the total number of minutes spent on each ticket
during the period specified (in this case the month of April). If a
ticket spans across multiple months, only the time from this period
is included in the table, which is what we wanted for billing. I hope
that suffices for your purposes until someone comes up with a better
way.
--
James Taylor
_______________________________________________
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 or consulting for your OTRS system?
=> http://www.otrs.com/
