/build/static/layout/Breadcrumb_cap_w.png

Report for all non-closed tickets in a single queue

I am using the following code but I need some help making revisions to it.

CONCAT('TICK:',HD_TICKET.ID) as 'Number' ,HD_PRIORITY.NAME as 'Priority' ,Q.NAME as 'Queue' ,HD_TICKET.TITLE as 'Title' ,if((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as 'Owner' ,DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as 'Created' ,case upper(STATE)
                               when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) -
                                                    unix_timestamp(HD_TICKET.TIME_OPENED)
                               when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
                               else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end
                                as 'Time Open' ,HD_CATEGORY.NAME as 'Category' ,HD_STATUS.NAME as 'Status' ,if((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as 'Submitter' ,HD_TICKET.DUE_DATE as 'Due'  from (HD_TICKET, HD_STATUS, HD_IMPACT, HD_CATEGORY)
                        LEFT JOIN HD_PRIORITY on (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
                        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
                        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
                        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
                        where (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID  and
                        HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID and HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) and ((( (HD_TICKET.HD_QUEUE_ID in (5)))  or (OWNER_ID = 314) or (APPROVER_ID = 314) or (SUBMITTER_ID = 314 and (SERVICE_TICKET_ID is NULL or SERVICE_TICKET_ID = 0 or IS_PARENT = true)))
                   and STATE in ('opened', 'stalled'))  

1) How can I get rid of the following columns:

  • Time Open
  • Category
  • Status 
2) Relocate the "Due Date" column to the right of the "Created" column.

3) This report is including tickets from another ticket queue.  Can you please help me set it to queue 5 which is the "Marketing" queue?

Thanks in advance!

0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: aragorn.2003 9 years ago
Red Belt
0
To get rid of these 3 columns, just remove
case upper(STATE) when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED) when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)             else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as 'Time Open'
HD_CATEGORY.NAME as 'Category'
HD_STATUS.NAME as 'Status'

To relocate the Due Date columt right of the created column, move

HD_TICKET.DUE_DATE as 'Due'

right after

DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as 'Created',

To ensure that you only see tickets from queue 5, it´s correct to have the where clause

HD_TICKET.HD_QUEUE_ID in (5)

but you have several or conditions. please change that one.


Comments:
  • Hi Aragorn,

    The report shows up fine but it still contains tickets from other queues. For example, the Network report includes 2 tickets for the Help Desk (Queue:1) and 3 tickets for Accounting (Queue:4). - TXgroup 9 years ago
    • Hi TXgroup. As I said, you have so many or conditions. What you get is a list with tickets in queue_id = 5 or where the owner is 314 (also in other queues) or where the approver is 314 (also in other queues) or where the submitter is 314 (also in other queues). I think you need to walk through the whole where condition. - aragorn.2003 9 years ago
      • Hi Aragorn, I was able modify the owner, approver, and submitter to the same queue number and that resolved the issue! Thanks!! - TXgroup 9 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ