Time_Accounting SQL query

Dont create your support topics here! No new topics with questions allowed!

Moderator: crythias

Forum rules
Dont create your support topics here! No new topics with questions allowed!
Post Reply
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Time_Accounting SQL query

Post by crythias »

Code: Select all

select t.tn ticketnumber, a.create_by user_id, CONCAT(u.first_name, " ", u.last_name) name, t.title title, q.name queue, t.customer_user_id customer_user, t.customer_id customer, ta.time_unit time_unit, DATE_FORMAT(ta.create_time, "%m/%d/%y") create_time,  a.a_subject subject, a.a_body body from ticket t left join time_accounting ta on ta.ticket_id=t.id left join queue q on t.queue_id = q.id  left join article a on a.id=ta.article_id left join users u on u.id = a.create_by where ta.time_unit is not null order by create_time
"What does it do?" Stick it in your SQL box and find out.
Ticket number, Agent user_id, Agent First Name Agent Last Name, ticket title, queue name, customer information, customer_id, time_accounting units, when it was entered.

So, if you want to find out what was billed on what, done by whom, for whom, what queue and when ... here you go.

Updated 11/16/11: adjusted for submitter of the time, not owner of the ticket. Also show title and body of time entry (article).
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Time_Accounting SQL query

Post by crythias »

If you want to include the ticket owner at the same time as the agent who added time, here's the query:

Code: Select all

select t.tn ticketnumber, t.user_id ownerid, CONCAT(u2.first_name, " ", u2.last_name) owner, a.create_by user_id, CONCAT(u.first_name, " ", u.last_name) name, t.title title, q.name queue, t.customer_user_id customer_user, t.customer_id customer, ta.time_unit time_unit, DATE_FORMAT(ta.create_time, "%m/%d/%y") create_time,  a.a_subject subject, a.a_body body from ticket t left join time_accounting ta on ta.ticket_id=t.id left join queue q on t.queue_id = q.id  left join article a on a.id=ta.article_id left join users u on u.id = a.create_by left join users u2 on u2.id=t.user_id where ta.time_unit is not null order by create_time
ticketnumber, ticketownerid, ticketownername, articlecreatoruser_id, articlecreatorname, titleofticket, queue, customerusername, customercompanyname (or customer_id), howMuchTimeRecorded, whatDateArticlecreated, subjectofarticlethathastime, bodyofarticle

This is rather extensive. In general, you should at this point know a bunch of things with regard to the ticket. If you want more information, in the sql box, DESCRIBE ticket and clear the LIMIT. You should be able to add *any* field from ticket in this list. Note that some fields are different from the display names. freekey1, freetext1, for example-- just add t.freekey1 or t.freetext1 in the list. You can do the same thing with article (a.a_freekey1, a.a_freetext1).

If you're wanting to get names for ticket_state_id, for instance, you'll want to put
t.ticket_state_id state_id, s.name state,
in the top somewhere after select and then something like
left join ticket_state s on s.id=t.ticket_state_id
somewhere after ticket t and before "where".

If you don't follow what I'm saying, I would completely understand your confusion here.

here's a breakdown of what is being accomplished (notes after #. Do not copy and use the following.):
select
#sql command. what are we going to do? Select what follows
t.tn ticketnumber,
# I'm using shorthand here. t stands for the ticket table. tn is the field in ticket for ticket number. ticketnumber is a label for the output
t.user_id ownerid,
CONCAT(u2.first_name, " ", u2.last_name) owner,
#CONCAT is a mysql command to concatenate (or "smoosh together") stuff that follows separated by commas.
#u2 is a shortcut to a copy of the users table that is different from the one I will use later.
#This time I'm looking up the owner of the ticket.
#owner is the label for the column
a.create_by user_id,
CONCAT(u.first_name, " ", u.last_name) name,
#same as before, but this is because I'm looking up the user for the agent who added the article with time
t.title title,
q.name queue,
#use the q result to give me the name of the queue and put it in a column named queue
t.customer_user_id customer_user,
#customer_user is the username of the customer who submitted the ticket
t.customer_id customer,
#customer_id is the customer_id (maybe an email address, maybe a company name) of the customer attached to the ticket
ta.time_unit time_unit,
#this is what was entered as time units for a given article
DATE_FORMAT(ta.create_time, "%m/%d/%y") create_time,
#this is a mysql command that formats the create_time as month/date/year. If you need to change that, you can look up formats in mysql help)
a.a_subject subject,
#this is the subject of the article that has had time added. Chances are it's a reply or a note, but it may be the first entry.
a.a_body body
#this is the full body of the article. It may not be useful in a spreadsheet. Don't use it if you don't want it.
from
#from is "where are we getting the data from?"
ticket t
#this is shorthand for "use 't' instead of typing out the whole word 'ticket'"
left join time_accounting ta on ta.ticket_id=t.id
#use "ta" instead of time_accounting, and return all the time_accounting entries where the time_accounting.ticket_id is the same as the current ticket id.
left join queue q on t.queue_id = q.id
#use "q" instead of queue, and return the record(s) in queue for the ticket's queue_id. (Useful to lookup a queue name)
left join article a on a.id=ta.article_id
#give me the article that matches the time_accounting article_id. I need to know that article's creator.
left join users u on u.id = a.create_by
#the article's create_by is who made the time entry. I'm using the users table to get the first and last name for that user.
left join users u2 on u2.id=t.user_id
#the ticket's user_id is who owns the ticket. I'm using the users table to get information, but since I've used u already, I added u2 for this purpose.
where
#where is mysql speak for "what conditions?"
ta.time_unit is not null
#I'm only reporting on things where I have time submitted.
order by
#"how do I sort this?"
create_time
#I got sloppy here, but it's the create_time of the entry in time_accounting
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
earwax
Znuny newbie
Posts: 43
Joined: 07 Dec 2010, 23:54
Znuny Version: 3.0.11
Location: Seattle, WA
Contact:

Re: Time_Accounting SQL query

Post by earwax »

If I am understanding corretly, there is no way to generate this report within OTRS? Nonetheless, helpful to have this query. Thanks!
OTRS 3.0.12
Ubuntu Linux 10.04
MySQL
Wolfgangf
Znuny ninja
Posts: 1029
Joined: 13 Apr 2009, 12:26
Znuny Version: 6.0.13
Real Name: Wolfgang Fürtbauer
Company: PBS Logitek GmbH
Location: Pinsdorf

Re: Time_Accounting SQL query

Post by Wolfgangf »

a more comprehensive way with summing accounted time per ticket

Code: Select all

select t.tn ticketnumber, 
	t.title title, 
	CONCAT(u2.first_name, " ", u2.last_name) owner,
	q.name queue, 
	t.customer_id customer, 
	sum(ta.time_unit) time_unit, 
	DATE_FORMAT(ta.create_time, "%m/%d/%y") create_time,  
	a.a_subject subject
from ticket t 
	left join time_accounting ta on ta.ticket_id=t.id 
	left join queue q on t.queue_id = q.id  
	left join article a on a.id=ta.article_id 
	left join users u on u.id = a.create_by 
	left join users u2 on u2.id=t.user_id 
where ta.time_unit is not null 
group by t.tn
order by create_time ;
Produktiv:
OTRS 6.0.13/ ITSM 6.0.13
OS: SUSE Linux (SLES 12, Leap), MySql 5.5.x, 5.6.x
Windows 2012 AD Integration (agents and customers), Nagios integration (incidents, CMDB), Survey, TimeAccounting
andreaconsadoriw
Znuny newbie
Posts: 6
Joined: 08 Nov 2016, 15:48
Znuny Version: 6.0.4

Re: Time_Accounting SQL query

Post by andreaconsadoriw »

I just upgrade to OTRS 6.x and i see that this sql is not working after the upgrade,
can you show us an example working with 6.x version?
fullhouse
Znuny newbie
Posts: 36
Joined: 13 Sep 2018, 11:58
Znuny Version: 6.0.12

Re: Time_Accounting SQL query

Post by fullhouse »

i'm also interested in how it would work in newer version. would be much appreciated
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Time_Accounting SQL query

Post by crythias »

try changing article to article_data_mime.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Post Reply