Help with KACE Help Desk Queue Custom Rule (Set Owner and Submitter ID based on incoming email text)
Hello ITNinjas.
I am hoping someone can help me figure out why a custom rule is not working as expected: it is supposed to set the HD_TICKET.owner_id and HD_TICKET.submitter_id based on the text of the incoming email sent to our helpdesk (the email happens to be Google Apps for Business Password Changed Alert).
- I have the parsing correct (in that username for both the submitter and owner are correctly extracted),
- SELECT QUERY correctly returns the ticket ID in question, Executing Select Query... 02/04/2015 10:28:34> selected 1 rows
- the UPDATE QUERY simply says Executing Update Query... 02/04/2015 10:28:36> updated 0 rows
The SELECT QUERY is this:
SELECT HD_TICKET.ID
FROM HD_TICKET INNER JOIN
HD_TICKET_CHANGE ON HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID
WHERE HD_TICKET_CHANGE.ID = < CHANGE_ID > AND
HD_TICKET_CHANGE.DESCRIPTION Like 'Ticket Created%' AND
HD_TICKET_CHANGE.USER_ID = 4470 AND
HD_TICKET_CHANGE.COMMENT LIKE 'Google Apps for Business%Password%has been changed%'
The UPDATE QUERY is this:
update HD_TICKET
set HD_TICKET.OWNER_ID =
(
SELECT ID FROM USER U1
WHERE U1.USER_NAME =
(
SELECT
CASE
WHEN HTC2.COMMENT LIKE '%(s@mydomain.tld)%' THEN 's'
WHEN HTC2.COMMENT LIKE '%(a@mydomain.tld)%' THEN 'a'
WHEN HTC2.COMMENT LIKE '%(b@mydomain.tld)%' THEN 'b'
WHEN HTC2.COMMENT LIKE '%(c@mydomain.tld)%' THEN 'c'
WHEN HTC2.COMMENT LIKE '%(d@mydomain.tld)%' THEN 'd'
WHEN HTC2.COMMENT LIKE '%(e@mydomain.tld)%' THEN 'e'
WHEN HTC2.COMMENT LIKE '%(f@mydomain.tld)%' THEN 'f'
WHEN HTC2.COMMENT LIKE '%(g@mydomain.tld)%' THEN 'g'
WHEN HTC2.COMMENT LIKE '%(h@mydomain.tld)%' THEN 'h'
WHEN HTC2.COMMENT LIKE '%(i@mydomain.tld)%' THEN 'i'
WHEN HTC2.COMMENT LIKE '%(j@mydomain.tld)%' THEN 'j'
WHEN HTC2.COMMENT LIKE '%(k@mydomain.tld)%' THEN 'k'
WHEN HTC2.COMMENT LIKE '%(l@mydomain.tld)%' THEN 'l'
WHEN HTC2.COMMENT LIKE '%(m@mydomain.tld)%' THEN 'm'
WHEN HTC2.COMMENT LIKE '%(n@mydomain.tld)%' THEN 'n'
WHEN HTC2.COMMENT LIKE '%(o@mydomain.tld)%' THEN 'o'
WHEN HTC2.COMMENT LIKE '%(p@mydomain.tld)%' THEN 'p'
WHEN HTC2.COMMENT LIKE '%(q@mydomain.tld)%' THEN 'q'
WHEN HTC2.COMMENT LIKE '%(r@mydomain.tld)%' THEN 'r'
ELSE ''
END AS OWNER1
FROM HD_TICKET_CHANGE HTC2
WHERE HTC2.HD_TICKET_ID = HD_TICKET.ID AND HTC2.COMMENT LIKE 'Google Apps for Business%Password%has been changed%'
)
),
HD_TICKET.SUBMITTER_ID =
(
SELECT ID FROM USER U2
WHERE U2.USER_NAME =
(
SELECT SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(HTC3.COMMENT,'@',2),'@',1),LOCATE('user',SUBSTRING_INDEX(SUBSTRING_INDEX(HTC3.COMMENT,'@',2),'@',1))+5) AS SUBMITTER1
FROM HD_TICKET_CHANGE HTC3
WHERE HTC3.HD_TICKET_ID = HD_TICKET.ID AND HTC3.COMMENT LIKE 'Google Apps for Business%Password%has been changed%'
)
)
where
HD_TICKET.ID in (< TICKET_IDS >)
I have tried it as a On Ticket Save rule (with HD_TICKET_CHANGE.ID = , and as a On Demand/Scheduled rule where I simply select all matching tickets (no ). Both ways, the SELECT query returns the correct ticket ID(s), but the UPDATE query executes but states "0 rows updated".
NOTE: < CHANGE_ID > and < TICKET_IDS > above have extra spaces between < > because the tokens seem to get stripped from the question when typed in directly; in the actual rule the two tokens are correct.
What am I missing?
2 Comments
[ + ] Show comments
Answers (0)
Please log in to answer
Be the first to answer this question
For your first U1.USER_NAME would it be better to use a left function instead of all of those case statements? - chucksteel 9 years ago
The rule started working just fine, shortly after I posted my question--I think I had a double space somewhere in the update query which was making it fail.
Thanks for the MIN() tip... it sounds obvious now that I think of it, but I sure never thought of it before :-)
The CASE statement is there because I was lazy when I first started on this rule, I could probably replace it with a combination of SUBSTRING/SUBSTRING_INDEX but it works now. I'll fix it one of these days--I should also make it work from a list of approved ticket owners so I don't have to change it every time a new hire comes in.
Regards. - merklo 9 years ago