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.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

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))

Answered 04/02/2013 by: nshah
Red Belt

  • 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!
  • SELECT H.TITLE, CREATED
    FROM HD_TICKET H
    WHERE (H.HD_QUEUE_ID = 8) AND
    CREATED > DATE_SUB(NOW(), INTERVAL 30 DAY)
  • Thank you all for your help
Please log in to comment
Answer this question or Comment on this question for clarity