Custom report to display note

[ciesel]

Is it possible to create a report that will display the text field in all closed tickets from the close ticket window in a specified time frame? Or a report that will show notes from all closed tickets?

My end goal is to have a report that shows how all the tickets were resolved in the last month.

[crythias]

This isn't necessarily as easy as it may seem (?)
By *default*, OTRS's ticket article type of a closed ticket is simply "note-internal", which makes it a bit difficult to filter out if, for instance, there are additional note-internals attached to a ticket and you were to search, for instance, on "Note-internal" for a given ticket.

OK, but the timestamp for the state change to closed should be close, if not identical, to the article timestamp .

To start figuring this out, go to your SQL box in admin and clear the limit.
DESCRIBE article
...
DESCRIBE ticket
...
DESCRIBE ticket_history
probably you'll want article.a_body and article.create_time and ticket.tn, ticket.title, ticket.user_id, ticket.customer_id, ticket.customer_user_id

You'll want a ticket_history.history_type of StateUpdate to close ...
**put the limit back in when running the following **

Code: Select all

SELECT ticket.title, ticket.tn, article.a_body
FROM ticket
LEFT JOIN article ON article.ticket_id = ticket.id
LEFT JOIN ticket_history ON ticket_history.ticket_id = ticket.id
LEFT JOIN ticket_history_type ON ticket_history_type.id = ticket_history.history_type_id
WHERE article.create_time = ticket_history.create_time
AND ticket_history_type.name =  "StateUpdate"
AND ticket_history.name LIKE  '%closed successful\%\%'