Helpdesk Report Showing Ticket History

Example of a report showing ticket history.

Notes:

• that this only shows user-viewable entries -- the owners only entries are hidden
• Description information is shown where applicable
• Sorted by newest ticket at the top, newest comment at the top
• Shows tickets with any submitter, owner and from any queue

SELECT HD_TICKET.ID, O.FULL_NAME AS OWNER_NAME, S.FULL_NAME AS SUBMITTER_NAME, 
GROUP_CONCAT( CONCAT('\n-- Change by ',U.USER_NAME,' on ', 
CAST(DATE(HD_TICKET_CHANGE.TIMESTAMP)  AS CHAR),' ---\n', 
IF(DESCRIPTION='' OR ISNULL(DESCRIPTION),'', 
CONCAT('-->',TRIM(TRAILING '-->' FROM REPLACE(DESCRIPTION,'\n','\n-->')),'')),
if(HD_TICKET_CHANGE.COMMENT='','',CONCAT(HD_TICKET_CHANGE.COMMENT,'\n')) 
) 
ORDER BY CAST( HD_TICKET_CHANGE.ID AS CHAR) DESC SEPARATOR '\n' ) AS
HD_TICKET_CHANGE_COMMENT

FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID =
HD_TICKET.HD_CATEGORY_ID)
JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
LEFT JOIN USER U ON USER_ID=U.ID
LEFT JOIN USER S ON S.ID=HD_TICKET.SUBMITTER_ID
WHERE  HD_TICKET_CHANGE.OWNERS_ONLY=0
GROUP BY HD_TICKET_ID
ORDER BY HD_TICKET.ID DESC