Re: [otrs] Time Accounting

21 May 2008

      On 17 May 2008, Jay Holler wrote:
...
On Thu, May 15, James Taylor 
...
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