K1000 Report showing tickets over X number of days
Dear Experts,
I'd like to create a report that shows any tickets that have been open for a certain number of days.
For example, as part of our KPIs we report on tickets that have been open over 15 and then 25 days.
Does anybody have any custom SQL reports that would do this?
Thanks
Answers (2)
You could probably use this and just change the internval dates.
http://www.itninja.com/question/report-showing-number-of-tickets-that-a-user-has-that-are-over-3-days-old-taking-into-account-a-5-day-work-week
Comments:
-
Thanks for the quick response nshah, I'd actually seen this before, but rather than showing the number of tickets overdue I want an actual list of the tickets overdue. - GarethE 10 years ago
Something like this?
SELECT
HD_TICKET.TITLE,
HD_TICKET.CREATED,
HD_STATUS.`NAME`,
`USER`.FULL_NAME
FROM
HD_TICKET
INNER JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
INNER JOIN `USER` ON HD_TICKET.OWNER_ID = `USER`.ID
WHERE
HD_TICKET.CREATED > DATE_SUB(NOW(),INTERVAL 15 DAY)
ORDER BY FULL_NAME ASC
Comments:
-
Thank you again and apologies for such a long delay. I think I might be doing something wrong because I can't get that to return any results at all and I know I've got tickets there over 15 days. - GarethE 10 years ago
-
are there Tickets in your archive for the other days? Then you'd need to get all tickets from both your archive and your current queue. Change Every HD_Ticket to HD_ARCHIVE_TICKET in his/her script and get them togehter. I'm not good at SQL but somehow you can "union" them. :) - aazimm 10 years ago
-
Hi aazimm, my apologies if I'm being a little slow on the uptake. By archive do you mean tickets that have been closed? I only want to report on active tickets within my service desk queue that have been open for X number of days. - GarethE 10 years ago
-
Sorry, didn't realize that. forget my answer :) But if you'd ever need to get the closed tickets, you can always remember my answer :) - aazimm 10 years ago
