Need help generating a report of all the tickets created in the past month.
Hey,
We are looking to create a report of ALL the tickets that were created in the past month for a specific queue.
We have tried the reports in Kace, but was not able to achieve a report that only shows the tickets created in the past month.
Using the wizard, we got a report that we wanted, but it shows all the tickets from the beginning and not the past month, maybe someone can help in editing this sql query so that it only shows the tickets created in the past month.
Here is the sql query:
SELECT HD_TICKET.APPROVAL, HD_TICKET.APPROVAL_NOTE, HD_TICKET.CREATED, HD_IMPACT.NAME AS IMPACT, O.FULL_NAME AS OWNER_NAME, HD_STATUS.NAME AS STATUS, HD_TICKET.ID, HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED, HD_TICKET.TITLE, HD_TICKET.CUSTOM_FIELD_VALUE0, HD_CATEGORY.NAME AS CATEGORY, GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED, HD_PRIORITY.NAME AS PRIORITY, S.FULL_NAME AS SUBMITTER_NAME FROM HD_TICKET JOIN HD_IMPACT ON (HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 8) GROUP BY HD_TICKET.ID ORDER BY TIME_OPENED
Any help apprieciated.
Answers (1)
If you just need the title and when it was created within 30 days. You would just need to change the FROM to the actualy helpdesk column for the queue you are thinking of.
SELECT title, created FROM HD_TICKET H
WHERE created > (DATE_SUB(CURDATE(), INTERVAL 30 DAY))
ORDER BY created;
Or you can try to add
WHERE created > (DATE_SUB(CURDATE(), INTERVAL 30 DAY))
Comments:
-
I think the first command might work! Can you please help me with the FROM sql query? I would like to see only the tickets created in the Queue ID = 8
Thanks a lot! - awaisk 11 years ago -
SELECT H.TITLE, CREATED
FROM HD_TICKET H
WHERE (H.HD_QUEUE_ID = 8) AND
CREATED > DATE_SUB(NOW(), INTERVAL 30 DAY) - dugullett 11 years ago -
Thank you all for your help - awaisk 11 years ago
