Custom Ticket rule to move ticket to queue based on submitter_ID default queue
I'm really new to SQL queries and I'm trying to write a custom ticket rule to move tickets from the default queue to another queue based on the default queue of the submitter. I think my select statement is ok and my update query makes sense to me but I'm getting a duplicate primary key error. Here is what I have.
The error I'm getting is this:
05/08/2015 09:17:19> Starting: 05/08/2015 09:17:19 05/08/2015 09:17:19> Executing Select Query... 05/08/2015 09:17:19> selected 6 rows 05/08/2015 09:17:19> Executing Update Query... 05/08/2015 09:17:19> mysql error: [1062: Duplicate entry '14' for key 'PRIMARY'] in EXECUTE("UPDATE ORG1.HD_TICKET JOIN ORG1.USER ON ORG1.USER.ID = ORG1.HD_TICKET.SUBMITTER_ID SET ORG1.HD_TICKET.id = ORG1.USER.HD_DEFAULT_QUEUE_ID ") 05/08/2015 09:17:19> Recalculating SLA Due Dates... 05/08/2015 09:17:19> updated 6 tickets 05/08/2015 09:17:19> Ending: 05/08/2015 09:17:19
Does this look like I'm in the ballpark? I'm not sure where to go from here.
Thanks,
Jonathan
Select SQL:
SELECT ORG1.HD_TICKET.id
FROM ORG1.USER INNER JOIN ORG1.HD_TICKET
ON ORG1.USER.ID = ORG1.HD_TICKET.SUBMITTER_ID
WHERE ORG1.HD_TICKET.id = '10'
Run update query
Update SQL:
UPDATE ORG1.HD_TICKET JOIN ORG1.USER
ON ORG1.USER.ID = ORG1.HD_TICKET.SUBMITTER_ID
SET ORG1.HD_TICKET.id
= ORG1.USER.HD_DEFAULT_QUEUE_ID Does this look like I'm in the ballpark? I'm not sure where to go from here.
The error I'm getting is this:
05/08/2015 09:17:19> Starting: 05/08/2015 09:17:19 05/08/2015 09:17:19> Executing Select Query... 05/08/2015 09:17:19> selected 6 rows 05/08/2015 09:17:19> Executing Update Query... 05/08/2015 09:17:19> mysql error: [1062: Duplicate entry '14' for key 'PRIMARY'] in EXECUTE("UPDATE ORG1.HD_TICKET JOIN ORG1.USER ON ORG1.USER.ID = ORG1.HD_TICKET.SUBMITTER_ID SET ORG1.HD_TICKET.id = ORG1.USER.HD_DEFAULT_QUEUE_ID ") 05/08/2015 09:17:19> Recalculating SLA Due Dates... 05/08/2015 09:17:19> updated 6 tickets 05/08/2015 09:17:19> Ending: 05/08/2015 09:17:19
Does this look like I'm in the ballpark? I'm not sure where to go from here.
Thanks,
Jonathan
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
8 years ago
Your update query is trying to change the ticket ID (HD_TICKET.ID). You want to change the queue ID which is HD_TICKET.HD_QUEUE_ID.
I would write the rule like this:
UPDATE HD_TICKET
SET HD_TICKET.HD_QUEUE_ID = (SELECT HD_DEFAULT_QUEUE_ID FROM USER WHERE USER.ID = HD_TICKET.SUBMITTER_ID)
WHERE HD_TICKET.ID in <TICKET_IDS>
When you create a rule KACE populates a variable named <TICKET_IDS>. This gets populated with a list of ID values returned from the select statement.
Comments:
-
pls check my statement if its correct.
what im doing is if CUSTOM_FIELD_VALUE8 like '%America%' then move the tickets to queue_ID =20.
SELECT HD_TICKET.ID, HD_TICKET.TITLE,
HD_TICKET.HD_CATEGORY_ID, HD_TICKET.HD_QUEUE_ID,
HD_CATEGORY.ID, HD_CATEGORY.NAME
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE HD_TICKET.CUSTOM_FIELD_VALUE8 like '%America%'
AND HD_TICKET.HD_QUEUE_ID = 16
ORDER BY HD_TICKET.ID
UPDATE HD_TICKET
SET HD_TICKET.HD_QUEUE_ID = 20
WHERE HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
AND HD_TICKET.ID = <TICKET_IDS>;
05/11/2016 03:01:34> Starting: 05/11/2016 03:01:34 05/11/2016 03:01:34> Executing Select Query... 05/11/2016 03:01:34> selected 2 rows 05/11/2016 03:01:34> Executing Update Query... 05/11/2016 03:01:34> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '122' at line 4] in EXECUTE("UPDATE HD_TICKET SET HD_TICKET.HD_QUEUE_ID = 20 WHERE HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID AND HD_TICKET.ID = 122,122;") 05/11/2016 03:01:34> Ending: 05/11/2016 03:01:34 - rahimpal 7 years ago-
Your select statement is returning multiple ticket IDs so your update statement ends with HD_TICKET.ID = 122,122. You should use HD_TICKET.ID in (<TICKET_IDS>) instead. - chucksteel 7 years ago
-
executes without any error but doesnt move the tickets to other queue
SELECT HD_TICKET.ID, HD_TICKET.TITLE,
HD_TICKET.HD_CATEGORY_ID, HD_TICKET.HD_QUEUE_ID,
HD_CATEGORY.ID, HD_CATEGORY.NAME
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE HD_TICKET.CUSTOM_FIELD_VALUE8 like '%Amer%'
AND HD_TICKET.HD_QUEUE_ID = 16
ORDER BY HD_TICKET.ID
UPDATE HD_TICKET, HD_CATEGORY
SET HD_TICKET.HD_QUEUE_ID = 22
WHERE HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
AND HD_TICKET.ID in (<TICKET_IDS>);
05/12/2016 01:18:57> Starting: 05/12/2016 01:18:57 05/12/2016 01:18:57> Executing Select Query... 05/12/2016 01:18:57> selected 2 rows 05/12/2016 01:18:57> Executing Update Query... 05/12/2016 01:18:57> updated 0 rows 05/12/2016 01:18:57> Ending: 05/12/2016 01:18:57 - rahimpal 7 years ago -
Have you tried just changing the queue and not including the category? So just
UPDATE HD_TICKET
SET HD_QUEUE_ID = 22
WHERE ID in (<TICKET_IDS>)
Keep in mind that category ID values are unique per queue, so you can't use a category ID from queue 16 for a ticket in queue 22. - chucksteel 7 years ago
