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

0 Comments   [ + ] Show Comments

Comments

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

Answers

This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

0

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

Answered 01/13/2014 by: nshah
Red Belt

  • 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.
Please log in to comment
0

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

Answered 01/14/2014 by: nshah
Red Belt

  • 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.
    • 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. :)
      • 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.
      • 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 :)
Please log in to comment
Answer this question or Comment on this question for clarity