Rule to change queue no longer working
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
Answers (3)
Please log in to answer
Posted by:
chucksteel
8 years ago
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.
Comments:
-
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? - bryanpittman 8 years ago
-
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. - chucksteel 8 years ago
-
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>)) - bryanpittman 8 years ago
Posted by:
Ace58
7 years ago
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 ?
Comments:
-
No, I still have never come across a resolution for this. - bryanpittman 7 years ago
-
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. - Ace58 7 years ago
Posted by:
jct134
2 years ago
What I was able to do, for example we wanted to move certain tickets when they are closed to a different Queue, but of course if they got moved over they would be set back to "New" status instead of staying closed.. as well as have the category set to the default etc..
So (and this should work for any of these settings) if you go into the database and see what the ID is that represents the status (or whatever) that you want set in the Queue you want to move the ticket into, then set that value with for example :
Update HD_TICKET
Set HD_STATUS_ID=72, HD_CATEGORY_ID=273, HD_QUEUE_ID=9
where
(HD_TICKET.ID in (<TICKET_IDS>))
in our Tier 2 Queue this CTR sets the Status for this particular ticket to "Closed" Category to "Employee::Change" and the queue to "9" our Employee Process Queue