Kace Reporting Scheduled Ticket Report
I have a scheduled report to pull Modified,Category, Owner, Title, Location, COmments,FirstReportedBy . Is there anyway to pull the last comment, and not all of them.
Here is the SQL.
SELECT HD_TICKET.MODIFIED, HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.CUSTOM_FIELD_VALUE12, O.FULL_NAME AS OWNER_NAME, HD_TICKET.TITLE, HD_TICKET.CUSTOM_FIELD_VALUE1, GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED, HD_TICKET.CUSTOM_FIELD_VALUE3 FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) LEFT JOIN HD_SERVICE_STATUS ON HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.HD_SERVICE_STATUS_ID and HD_SERVICE_STATUS.ID = HD_TICKET.HD_SERVICE_STATUS_ID JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 10) AND ((IF(HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.IS_PARENT, HD_SERVICE_STATUS.NAME, HD_STATUS.NAME) != 'Closed')) GROUP BY HD_TICKET.ID ORDER BY MODIFIED
1 Comment
[ + ] Show comment
-
can you post your current query? - JasonEgg 6 years ago
-
I have posted the SQL view. - rosenbaaron 6 years ago
Answers (1)
Please log in to answer
Posted by:
JasonEgg
6 years ago
This query will give you information on the latest change to a ticket, not necessarily the latest comment:
SELECT
HD_TICKET.ID,
HD_TICKET.MODIFIED,
HD_CATEGORY.NAME AS CATEGORY,
HD_TICKET.CUSTOM_FIELD_VALUE12,
O.FULL_NAME AS OWNER_NAME,
HD_TICKET.TITLE,
HD_TICKET.CUSTOM_FIELD_VALUE1,
HD_TICKET.CUSTOM_FIELD_VALUE3,
HD_TICKET_CHANGE.DESCRIPTION AS LAST_CHANGE,
HD_TICKET_CHANGE.COMMENT AS LAST_CHANGE_COMMENT
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
LEFT JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
LEFT JOIN HD_SERVICE_STATUS ON HD_TICKET.HD_USE_PROCESS_STATUS
AND HD_TICKET.HD_SERVICE_STATUS_ID
AND HD_SERVICE_STATUS.ID = HD_TICKET.HD_SERVICE_STATUS_ID
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE
IF (
HD_TICKET.HD_USE_PROCESS_STATUS
AND HD_TICKET.IS_PARENT
,HD_SERVICE_STATUS.NAME
,HD_STATUS.NAME
) != 'Closed'
AND HD_TICKET_CHANGE.ID in
(select MAX(HD_TICKET_CHANGE.ID)
from HD_TICKET_CHANGE
group by HD_TICKET_CHANGE.HD_TICKET_ID
)
GROUP BY HD_TICKET.ID ORDER BY MODIFIED