Hi all,

We would like to create a Report for Helpdesk Ticket that has been created for more than an hour without anyone assigned or Ticket status as 'NEW' for more than 60 minutes in past 30 days

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • Thanks and it works exactly we want.
Please log in to comment

Answers

1

This should get you started. The first one lists tickets where the status has changed from New to something else:

 SELECT HD_TICKET.ID, HD_TICKET.CREATED,HD_TICKET_CHANGE.TIMESTAMPFROM ORG1.HD_TICKETJOIN HD_TICKET_CHANGE on HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID     and HD_TICKET_CHANGE.DESCRIPTION like '%Changed ticket status from "New"%'WHERE TIME_TO_SEC(TIMEDIFF(HD_TICKET_CHANGE.TIMESTAMP, HD_TICKET.CREATED)) > 3600and HD_TICKET.CREATED > NOW() - INTERVAL 30 DAY

This one looks for change in owner:

 SELECT HD_TICKET.ID, HD_TICKET.CREATED,HD_TICKET_CHANGE.TIMESTAMPFROM ORG1.HD_TICKETJOIN HD_TICKET_CHANGE on HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID     and HD_TICKET_CHANGE.DESCRIPTION like '%Changed ticket owner from Unassigned%'WHERE TIME_TO_SEC(TIMEDIFF(HD_TICKET_CHANGE.TIMESTAMP, HD_TICKET.CREATED)) > 3600and HD_TICKET.CREATED > NOW() - INTERVAL 30 DAY

 

Answered 02/14/2014 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity