/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: JasonEgg 6 years ago
Red Belt
0
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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ