comments post closure -- need report and notification
I am needing help to create a report that will show all tickets that have a comment AFTER they have been closed. It would also be nice to be notified when a comment is added to a ticket with status CLOSE.
Thanks,
Jason
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
grayematter
10 years ago
Here is the basis for a report. I have arbitrarily limited the results to comments posted within the last 31 days. You should be able to easily convert this to a ticket rule as well.
select
HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET_CHANGE.COMMENT,
HD_TICKET_CHANGE.DESCRIPTION,
HD_TICKET_CHANGE.TIMESTAMP,
HD_TICKET.TIME_CLOSED,
ifnull((select
FULL_NAME
from
USER
where
HD_TICKET.OWNER_ID = USER.ID),
' Unassigned') as OWNER_NAME,
(select
FULL_NAME
from
USER
where
HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from
HD_TICKET_CHANGE
left join
HD_TICKET ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
left join
HD_CATEGORY ON HD_CATEGORY_ID = HD_CATEGORY.ID
left join
HD_STATUS ON HD_STATUS_ID = HD_STATUS.ID
left join
HD_PRIORITY ON HD_PRIORITY_ID = HD_PRIORITY.ID
left join
HD_IMPACT ON HD_IMPACT_ID = HD_IMPACT.ID
left join
MACHINE ON HD_TICKET.MACHINE_ID = MACHINE.ID
where
HD_STATUS.STATE = 'closed'
and HD_TICKET_CHANGE.TIMESTAMP > HD_TICKET.TIME_CLOSED
and (HD_TICKET_CHANGE.COMMENT IS NOT NULL and HD_TICKET_CHANGE.COMMENT <> '')
and HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 31 DAY)
ORDER BY HD_TICKET.ID, HD_TICKET_CHANGE.ID
