Query out some data from OTRS Database

[alvarocsoares]

Hi All,

I am a newbie in OTRS and have to extract some ticket's data from OTRS database. I've already noticed that the main tables are QUEUE and TICKET, but i am stuck at some points about to gather these information.

Did someone get to run these queries in OTRS database that could help me out?

Thanks in advance

regards

[jojo]

which queries? What do you want to achieve? did you have a look on the database schema?

[alvarocsoares]

Hi Jojo,

Indeed, i've been looking at database schema to understand how to extract these data, but there are somethings that doeas not match. I mean, I have to extract the follwowing information from OTRS' database :

Field ..........................................Description
Ticket ID ..................................... Ticket's number
Create Time ................................. Date and time when the ticket was opened
Solve Time ................................... Date and time when the ticket was solved
Ticket Type ................................... Type of ticket (INCIDENT etc.)
Ticket status ................................. Ticket's status (pending, open , solved, closed etc.)
Ticket Service ................................ Ticket's reason. What caused the issue ?
User ............................................ Info about ticket's owner (login and etc.)
SLA first response..............................Time util the ticket is picked up
SLA solution ...................................Time elapsed to solve the issue according to company policy
Priority ....................................... Ticket's Priority
Solved By ..................................... Who solved the issue (Operato' Name from my support team)
Group Name ................................. Team's operator name
VIP ........................................... Flag to indicate if the user is VIP or not

About the user type I've tracked some information at "ACL" table, but I do not know the meaning of this table and it did not match with any field in "Ticket" or "Queue" table.

Then I'm a little lost against this database schema.

Thanks.

Regards,

[jojo]

https://github.com/OTRS/otrs/tree/maste ... t/diagrams

[alvarocsoares]

Hi Jojo,

As I said in previous post, I've already got the database schema, but i'm stuck about how to query out all the information I need.


Thanks

Regards.

[reneeb]

untestet:

Code: Select all

SELECT t.id, t.create_time, tt.name, s.name, t.user_id, p.name
FROM ticket t
INNER JOIN ticket_type tt ON t.type_id = tt.id
INNER JOIN ticket_history th ON th.ticket_id = t.id
INNER JOIN service s ON t.service_id = s.id
INNER JOIN priority p ON t.priority_id = p.id
WHERE t.id = ....

SLA first response..............................Time util the ticket is picked up
SLA solution ...................................Time elapsed to solve the issue according to company policy

This is tricky if you want to consider office hours and weekends. Otherwise, you have to do some calculations with ticket_history entries.

Solved By ..................................... Who solved the issue (Operato' Name from my support team)

You have to get those information from the ticket_history as well

Group Name ................................. Team's operator name

An agent can be a member of several groups...