/build/static/layout/Breadcrumb_cap_w.png
10/18/2016 963 views
I have an on-save ticket rule setup to move tickets to the appropriate queue, based off which number (1-3) a ticket owner picks in a drop down field (Custom Field 6 - on the ticket layout).

I've ensure the custom field is appropriately selected and while it does change this queue field, it doesn't actually move the ticket to another queue. Can someone tell me what is wrong with this update statement? I'm not a SQL expert and found these queries online some time ago. 

I'm getting the below error:

10/18/2016 13:36:50> Starting: 10/18/2016 13:36:50 10/18/2016 13:36:50> Executing Select Query... 10/18/2016 13:36:50> selected 1 rows 10/18/2016 13:36:50> Adding ticket comments... 10/18/2016 13:36:50> updated 1 tickets 10/18/2016 13:36:50> Executing Update Query... 10/18/2016 13:36:50> mysqli error: [1054: Unknown column 'HD_QUEUE.HD_STATUS_ID' in 'field list'] in EXECUTE("update HD_TICKET, HD_STATUS, HD_CATEGORY, HD_IMPACT, HD_PRIORITY, HD_QUEUE SET HD_TICKET.HD_QUEUE_ID = CUSTOM_FIELD_VALUE6, HD_TICKET.HD_STATUS_ID = HD_QUEUE.HD_STATUS_ID, HD_TICKET.HD_CATEGORY_ID = HD_QUEUE.HD_CATEGORY_ID, HD_TICKET.HD_IMPACT_ID = HD_QUEUE.HD_IMPACT_ID, HD_TICKET.HD_PRIORITY_ID = HD_QUEUE.HD_PRIORITY_ID, CUSTOM_FIELD_VALUE6='' where HD_TICKET.HD_STATUS_ID = HD_STATUS.ID and HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID and HD_TICKET.HD_IMPACT_ID = HD_IMPACT.ID and HD_TICKET.HD_PRIORITY_ID = HD_PRIORITY.ID and HD_QUEUE.ID=CUSTOM_FIELD_VALUE6 and (HD_TICKET.ID in (12345))") 10/18/2016 13:36:50> Ending: 10/18/2016 13:36:50

Select SQL (Which works):

select HD_TICKET.*,
            DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
            DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
            HD_STATUS.NAME AS STATUS_NAME,
            HD_STATUS.ORDINAL as STATUS_ORDINAL,
            STATE,
            U1.USER_NAME as OWNER_NAME,
            U1.FULL_NAME as OWNER_FULLNAME,
            U1.EMAIL as OWNER_EMAIL,
            U2.USER_NAME as SUBMITTER_NAME,
            U2.FULL_NAME as SUBMITTER_FULLNAME,
            U2.EMAIL as SUBMITTER_EMAIL
            from ( HD_TICKET,
            HD_PRIORITY,
            HD_STATUS,
            HD_IMPACT,
            HD_CATEGORY ) left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
            left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
            where HD_PRIORITY.ID = HD_PRIORITY_ID and
            HD_STATUS.ID = HD_STATUS_ID and
            HD_IMPACT.ID = HD_IMPACT_ID and
            HD_CATEGORY.ID = HD_CATEGORY_ID and
            CUSTOM_FIELD_VALUE6 <> '' and
            CUSTOM_FIELD_VALUE6 <> HD_TICKET.HD_QUEUE_ID /* ticket does not match the current queue */ and
            <CHANGE_ID> > 0

Update SQL (Where it breaks):

update HD_TICKET,
            HD_STATUS,
            HD_CATEGORY,
            HD_IMPACT,
            HD_PRIORITY,
            HD_QUEUE
            SET HD_TICKET.HD_QUEUE_ID = CUSTOM_FIELD_VALUE6,
            HD_TICKET.HD_STATUS_ID = HD_QUEUE.HD_STATUS_ID,
            HD_TICKET.HD_CATEGORY_ID = HD_QUEUE.HD_CATEGORY_ID,
            HD_TICKET.HD_IMPACT_ID = HD_QUEUE.HD_IMPACT_ID,
            HD_TICKET.HD_PRIORITY_ID = HD_QUEUE.HD_PRIORITY_ID,
            CUSTOM_FIELD_VALUE6=''
            where HD_TICKET.HD_STATUS_ID = HD_STATUS.ID and
            HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID and
            HD_TICKET.HD_IMPACT_ID = HD_IMPACT.ID and
            HD_TICKET.HD_PRIORITY_ID = HD_PRIORITY.ID and
            HD_QUEUE.ID=CUSTOM_FIELD_VALUE6 and
            (HD_TICKET.ID in (<TICKET_IDS>))
0 Comments   [ + ] Show comments

Comments



Community Chosen Answer

1
Your problem starts with this line in the update statement:

HD_TICKET.HD_STATUS_ID = HD_QUEUE.HD_STATUS_ID


There isn't a column with the name HD_STATUS_ID. If you want to set to the default then use HD_QUEUE.DEFAULT_STATUS_ID. The same follows for category, impact and priority.

Answered 10/19/2016 by: chucksteel
Red Belt

  • What if I want to keep the same status, category, impact and priority (and not set the destination queue defaults) but just change the queue?
    • Status IDs are queue specific, so even if they have the same name they will have separate IDs in each queue. You could use a sub select statement to get the ID for the status in the target queue. Have you tried not changing the status ID? I'm not sure what KACE will do if you don't change it as part of the rule.
      • I took out all the update lines except the queue change and while it does work, it changes the priority, status, category and impact to the first option available in the destination queue for each of these. I presume this is the default behavior for Kace when these aren't specified?

        New Update Statement:

        update HD_TICKET,
        HD_QUEUE
        SET HD_TICKET.HD_QUEUE_ID = CUSTOM_FIELD_VALUE6,
        CUSTOM_FIELD_VALUE6=''
        where HD_QUEUE.ID=CUSTOM_FIELD_VALUE6 and
        (HD_TICKET.ID in (<TICKET_IDS>))

All Answers

0
I am struggling with same issue.
No problem in changing the queue, but move the status, dont work.

Have you been able to solve this ?


Answered 04/12/2017 by: Ace58
Senior Yellow Belt

  • No, I still have never come across a resolution for this.
    • I have managed to make a query, to change queue on a specific category, and keep the values, But ofcause the queue stuckture have to be exacly the same.

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