Custom report
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.
2 Comments
[ + ] Show comments
-
Have you given the wizard a try. It has limitation but what you describe, the wizard should be able to do. - nshah 8 years ago
-
I don't know the SQL code to put in - glenhooper 8 years ago
Answers (3)
Answer Summary:
Please log in to answer
Posted by:
Hobbsy
8 years ago
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
Comments:
-
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? - glenhooper 8 years ago
-
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. - glenhooper 8 years ago
Posted by:
Hobbsy
8 years ago
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
Comments:
-
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 - glenhooper 8 years ago
Posted by:
Hobbsy
8 years ago
Top Answer
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
Comments:
-
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 - glenhooper 8 years ago
