KACE Product Support : Service Desk report with last x number of comment only

Service Desk report with last x number of comment only

Hello,

I have created a service desk report that shows various details including the comments but in some cases it there is a long history of comments. My question is can you set it to only show the last X number of comments.

Thanks

2 Comments [ + ] Show comments

Can you post the SQL query for your report? - chucksteel 9 years ago
SELECT HD_PRIORITY.NAME AS PRIORITY, HD_TICKET.ID, HD_TICKET.TITLE, HD_STATUS.NAME AS STATUS_NAME,
HD_TICKET.CUSTOM_FIELD_VALUE0, HD_TICKET.CUSTOM_FIELD_VALUE4, HD_TICKET.CUSTOM_FIELD_VALUE8, HD_TICKET.MODIFIED,
HDTC.COMMENT AS HD_TICKET_CHANGE_COMMENT_GROUPED FROM HD_TICKET
JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
JOIN (SELECT ID,HD_TICKET_ID,COMMENT,MAX(TIMESTAMP) FROM HD_TICKET_CHANGE WHERE (COMMENT != '' AND COMMENT NOT LIKE 'Machine %' AND COMMENT NOT LIKE '% was deleted.' ) GROUP BY ID ORDER BY ID DESC) HDTC ON (HDTC.HD_TICKET_ID = HD_TICKET.ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 5) AND ((HD_STATUS.NAME != 'Resolved') AND (HD_STATUS.NAME != 'Withdrawn')) GROUP BY HD_TICKET.ID ORDER BY STATUS_NAME, HD_PRIORITY.ORDINAL, MODIFIED

I have made some progress and can now see the last comment part but it no longer hyperlinks the call ref to the ticket. Appreciate any assistance

Thanks - garyafarmer 9 years ago

Answers (1)

Posted by: Chris.Burgess 9 years ago

I have mine set up so that when the customer responds to a ticket it sends an email to the OWNER of the ticket and includes only the most recent comment. I had to modify the SELECT statement to include

C.COMMENT

Then I had to add another JOIN statement:

JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID

AND C.ID=<CHANGE_ID>

Maybe that will work for you in whatever you have going already?

Answer this question

Posted by:

View more:

KACE Product Support Questions

question