Okay I've been given the task of creating a custom report that will display all tickets that are 14 days or older.  Sorted by Ticket#, Submitter Name, Technician/Owner, Date created, Location, Due date and Status.  I have no idea how to do this.
Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

Comments

  • Have you given the wizard a try. It has limitation but what you describe, the wizard should be able to do.
  • I don't know the SQL code to put in
Please log in to comment

Answer Chosen by the Author

0
Are you reporting from the correct Queue? The code above is looking at Queue_ID = 3, this may not be right for you. Go to Servicedesk in the console, Configuration and then Queues. Hover over the queue you want to report on and the Queue ID will pop up in the bottom left.

The edit the code above with the correct ID
Answered 10/08/2015 by: Hobbsy
Red Belt

  • Well dang, I didn't even think to look at that. Yeah my queue ID is 1 not 3. I changed that now it works great!! Thanks so much Hobbsy
Please log in to comment

Answers

0
Try this SQL, it was built using the wizard, the key part being the created date not being within the last 14 days

SELECT HD_TICKET.ID, HD_TICKET.CREATED, S.FULL_NAME AS SUBMITTER_NAME, O.FULL_NAME AS OWNER_NAME, HD_TICKET.DUE_DATE, HD_STATUS.NAME AS STATUS_NAME  FROM HD_TICKET  LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND (((TIMESTAMP(HD_TICKET.CREATED) > NOW() OR TIMESTAMP(HD_TICKET.CREATED) <= DATE_SUB(NOW(),INTERVAL 14 DAY))))  ORDER BY ID
Answered 10/07/2015 by: Hobbsy
Red Belt

  • Thank you Hobbsy. This code seems to work, but brings in tickets that are closed. After further investigation, the closed tickets do not exist. This leads me to believe that the code is bringing in tickets that were deleted. Is there a fix for that?
  • After even further investigation, the code is creating a report of all tickets that have been deleted. I think it's searching for ticket ID, not ticket number.
Please log in to comment
0
Ticket ID is the same as the ticket number, however we can add in additional elements in the where statement

SELECT HD_TICKET.ID, HD_TICKET.CREATED, S.FULL_NAME AS SUBMITTER_NAME, O.FULL_NAME AS OWNER_NAME, HD_TICKET.DUE_DATE, HD_STATUS.NAME AS STATUS_NAME  FROM HD_TICKET  LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND ((HD_STATUS.NAME != 'Closed') AND ((TIMESTAMP(HD_TICKET.CREATED) > NOW() OR TIMESTAMP(HD_TICKET.CREATED) <= DATE_SUB(NOW(),INTERVAL 14 DAY))))  ORDER BY ID

Now should only include tickets that are not closed, give that a go
Answered 10/08/2015 by: Hobbsy
Red Belt

  • No go. It's generating a report of tickets that do not exist. I'm thinking the non-existent tickets are ones that have been deleted
Please log in to comment
This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity

Share