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

Comments

Please log in to comment

Answers

0
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
Answered 08/08/2014 by: grayematter
Fourth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity