Count all tickets, not count tasks

[ndhvu275]

Dear,

I'm creating a ticket report as a customization. In the my OTRS 4.x system can have ticket and task (creates via split functionality), But I'd like to count all tickets had been created in a period time, not count tasks. So my query would be

Code: Select all

SELECT Count(DISTINCT ticket.id) AS COUNT
FROM ticket
 LEFT JOIN link_relation_copy
 ON ticket.id = link_relation_copy.target_key
WHERE link_relation_copy.target_key IS NULL
 AND ticket.create_time >= '2012-01-01'
 AND ticket.create_time < '2014-01-01';

My issue is slow performance, it took about in more than 1 min. My database is quite big with almost 2 milion tickets. Expecting, it can run less than 1 second.

Solution 1: Check direct in database: tartget_key is varchar (link_relation table), but id is bigint (ticket table). A big question: why did OTRS design a different like that? So it will take a lot of time in query ticket table join to link_relation table, it has to convert bigint into varchar. Therefore I changed the target_key is bigint that improved a lot, but I don't know what impact to OTRS core?

Solution 2: Look in API of TicketSearch of OTRS core. Is it possible to filter the paramter can be adapt the report requirement? How can I set parameters

Thanks for your time can help or give me some idea
Vu Nguyen

[ndhvu275]

Hi,

This is my blocking issue. Please somebody, experts, guru help me

Thanks
Vu Nguyen

[seberget2]

I'm creating a ticket report as a customization. In the my OTRS 4.x system can have ticket and task (creates via split functionality), But I'd like to count all tickets had been created in a period time, not count tasks.

So how do you know the difference between a task and a ticket? Typically that would be done with ticket type, so you could do a WHERE clause on ticket_id one.

[ndhvu275]

I'm creating a ticket report as a customization. In the my OTRS 4.x system can have ticket and task (creates via split functionality), But I'd like to count all tickets had been created in a period time, not count tasks.

So how do you know the difference between a task and a ticket? Typically that would be done with ticket type, so you could do a WHERE clause on ticket_id one.

link_relation table, there are 2 fields: source_key and target_key.
source_key = 123
target_key = 124
123 and 124 are ticket_id in ticket table, so in this case 123 is ticket (parent) and 124 is task (child)

[dtosun]

Hi,

I want to separate my parent and child tickets. When i use your way like ticket_id is not equal to target_key filter, somethings is wrong. The result is not correct i think. Did you try it? Is there another way to separate them?