Closed Date

[annafoxcroft]

We are creating reports using SQL selecting the change_time field where state is 2 or 3 for to find closed jobs. Our issue is that we are not able to validate the results against equivalent queries run through either stats or searches. Are the stats or searches using another definition of closed? Please could someone confirm whether a 'closed date or time' is held by the system and if so where. If the Closed date and time are calculated, please could you advise the location of the fields required.
Many thanks

[Daniel Obee]

You'll get the most correct information of close times by searching the ticket_history for status changes (ticket_history.history_type_id = 27) with the result of a closed state e.g. ticket_history.state_id in (2, 3).

Greets
Daniel

[seanbatt]

You'll get the most correct information of close times ...

Thanks for the suggestion, but we're not after the "most correct" we're after the way OTRS does it (which may be the most correct, of course).

We are trying to get our stats generated by SQL to match those from the reports we create via the Stats section of OTRS. So far, we have not been able to; the numbers just don't seem to match.

by searching the ticket_history for status changes (ticket_history.history_type_id = 27) with the result of a closed state e.g. ticket_history.state_id in (2, 3).

Is that what OTRS Stats module does? Is there any way of switching on a debug flag (or something) and watching the SQL queries that actually get run against the MySQL db so we can get as close as possible with our own SQL?

Thanks in advance.

[Daniel Obee]

AFAIK it was the very SQL that OTRS uses. You will find the SQL statement in Kernel/System/Ticket.pm.

Logging the SQL statements can be done via database log. OTRS does not provide full SQL logging.

Greets
Dan