Custom Field for Ticket Close Time

[adrianb]

It amazes me that there is no field in the Ticket table that stores the date/time a ticket is closed. Is there any way to use a dynamic field or freetext to do this? I have no technical background, so I'm very in the dark.

In short, I want a field to store the date and time a ticket is closed, as it happens. This field should only update anytime the ticket is closed (in other words, if the ticket is closed, reopened, and then closed again, the field should be update with the new date). Is there any way to achieve this?

I am using a Business Intelligence tool to run reports from OTRS, including reporting on turnaround time (the difference between created time and closed time). With no Close Time field, I've been using Change Time. However, if a note is added to (or any other action carried out on) a closed ticket, the Change Time is updated (which I don't want).

[CSL]

This data is stored within OTRS: just not in the ticket table. It's stored in the ticket_history table, because closing a ticket is merely changing its state to 'closed', and records of all changes made to tickets are logged in the ticket_history table.

You can find this data by looking for the latest 'state change' entry in the ticket_history table, for a ticket that is currently in a closed state. I've done it this way because a ticket can be closed an re-opened multiple times, and this will only give the most recent time. Here's some SQL from a report I created:

SELECT MAX(th.create_time) FROM `otrs`.`ticket` st, `otrs`.`ticket_history` th
WHERE st.id = $ticketid
AND st.id = th.ticket_id
AND st.ticket_state_id NOT IN ( 1, 4, 6 )
AND th.history_type_id = 27

You'll have to replace $ticketid with the ticket ID (not ticket number) of the ticket you want the close time for. A ticket that isn't closed won't return anything.

[adrianb]

Thanks for your response CSL. I left out some important info. I know of the history table, and wrote the following code (very similar to what you did) to calculate the Close Time

SELECT max(ticket_history.change_time)
FROM ... etc...
WHERE ticket_history.history_type_id = 27 and ticket_state.type_id = 3

It works... but the query takes forever and actually hangs up my OTRS server. That's why I was avoiding using SQL on the backend to get my result.

Have you had any issues with your query?

[CSL]

No problem; I use a separate query to first generate the list of ticket ID's that I want the close times for, and then run the query I gave above for each separate ticket ID to get their close time. It runs in about a second for several thousand tickets, so I don't have any issues with performance.

If your query is slow, you may be trying to do too much at once, or draw / compare information from too many tickets (possibly the full set instead of a filtered set). Joins on non-primary key columns can easily cause this. I always find it best to do as OTRS does, and never use any join statements. Most of the time you can just say:
AND table1.id = table2.id

To link up data (ticket history table entries with their ticket, for instance) it's much more efficient to use the id's rather than attempting a join. Not sure if this helps any, but it's what I've found when making up my own statements.

[adrianb]

This is the actual statement:

SELECT
max(ticket_history.change_time)
FROM
ticket_state INNER JOIN ticket ON (ticket.ticket_state_id=ticket_state.id)
INNER JOIN ticket_history ON (ticket.id=ticket_history.ticket_id)

WHERE
( ticket_history.history_type_id = 27 and ticket_state.type_id = 3 )

While I understand what you are saying about the joins, I'm unsure of how to create my reports in the way I need them without using the joins. If you have any further tips/suggestions I'd appreciate it. In the meantime I'll try to use what you have suggested so far to see how far I can get. Really appreciate the help.

[crythias]

why are you select ticket_history.change_time from ticket_state? Why not ticket_history.change_time from ticket_history?

explicitly (I know all the state_ids that are closed)

Code: Select all

SELECT ticket.tn, ticket.id, max(ticket_history.change_time) AS closed FROM ticket_history LEFT JOIN ticket ON (ticket.id = ticket_history.ticket_id) WHERE ticket_history.history_type_id = 27 AND ticket_history.state_id IN (2, 3) GROUP BY ticket.tn; 

or (I know the state_type_id number for closed is 3, but many unknown states could apply)

Code: Select all

SELECT ticket.tn, ticket.id, max(ticket_history.change_time)  AS closed FROM ticket_history LEFT JOIN ticket ON (ticket.id = ticket_history.ticket_id) LEFT JOIN ticket_state ON (ticket_state.id = ticket_history.state_id) WHERE ticket_history.history_type_id = 27 AND ticket_state.type_id=3 GROUP BY ticket.tn;

or (I have no idea what the state_type_id is for closed. Find it for me and show me the result)

Code: Select all

SELECT ticket.tn, ticket.id, max(ticket_history.change_time) AS closed FROM ticket_history LEFT JOIN ticket ON (ticket.id = ticket_history.ticket_id) LEFT JOIN ticket_state ON (ticket_state.id = ticket_history.state_id) LEFT JOIN ticket_state_type ON (ticket_state_type.id = ticket_state.type_id) WHERE ticket_history.history_type_id = 27 AND ticket_state_type.name="closed" GROUP BY ticket.tn;

or (I don't trust 27 to be the right number)

Code: Select all

SELECT ticket.tn, ticket.id, max(ticket_history.change_time) AS closed FROM ticket_history LEFT JOIN ticket_history_type ON (ticket_history_type.id = ticket_history.history_type_id) LEFT JOIN ticket ON (ticket.id = ticket_history.ticket_id) LEFT JOIN ticket_state ON (ticket_state.id = ticket_history.state_id) LEFT JOIN ticket_state_type ON (ticket_state_type.id = ticket_state.type_id) WHERE ticket_history_type.name = "StateUpdate" AND ticket_state_type.name="closed" GROUP BY ticket.tn;

[vgheidesheim]

Thanks, that helped me a lot, too.

I customized the sql code a bit to show a bit more:
viewtopic.php?f=17&t=21616&p=86297#p86297

I simply used this database schema to get the names.
http://ftp.otrs.org/pub/otrs/misc/otrs-3.2-database.png