Ticket Rule that replaces the Ticket Submitter
Hello!
I am trying to create a Ticket Rule which will read the subject line of a newly submitted ticket, pull an email address from the subject line, and then replace the Ticket Submitter field with the new email address.
There are keywords in the subject line that border the email address I want to pull. Example: |Submitted by:exampleemail@emailaddress.com
I have the following SQL:
update HD_TICKET, USER as T5
set HD_TICKET.SUBMITTER_ID = SUBSTRING(HD_TICKET.TITLE, CHARINDEX('|Submitted by:', HD_TICKET.TITLE)+LEN('|Submitted by:'), LEN(HD_TICKET.TITLE))
where
HD_TICKET.ID in (<TICKET_IDS>)
However, I'm receiving the following error: mysqli error: [1370: execute command denied to user 'USER1' for routine 'ORG1.CHARINDEX'] in EXECUTE("update HD_TICKET, USER as T5 set HD_TICKET.SUBMITTER_ID = SUBSTRING(HD_TICKET.TITLE, CHARINDEX('|Submitted by:', HD_TICKET.TITLE)+LEN('|Submitted by:'), LEN(HD_TICKET.TITLE)) where HD_TICKET.ID in (16255)")
I'm not sure what I'm doing wrong. Any tips?
Answers (3)
Where you able to get this to work? I want to take a ticket summited by vendor@vendor.com and change the requester field to HR@mycompany.com. I am struggling
It look as if you are trying to set the SUBMITTER_ID with something other than a numerical value?? SO that is probably why KACE is complaining. The HD_TICKET.SUBMITTER_ID file needs to have the ID of the submitter which is linked from the ID in the USER table. So if you have the email address you need to go to the USER table to retrieve the correct USER.ID for that account and insert that numerical value i.e. '63' into the HD_TICKET.SUBMITTER_ID field.
Comments:
-
Oh I was not aware of that. Is there another field in a KACE ticket that I can replace with a String to change the Submitter's email address in the KACE ticket?
Or is the only way to go is to find out the USER ID of the submitter and then replace the KACE ticket Submitter ID? - clowninja2021 3 years ago-
The only way to go is to lookup the USER_ID from the Email and then write that ID into the HD_TICKET.SUBMITTER_ID field for that ticket - Hobbsy 3 years ago
-
Is the USER_ID from the email system? Or does KACE store the USER_ID in a table somewhere? - clowninja2021 3 years ago
Im curious as to why you want to do this. I may steal this idea... we have a business case for executive admins placing tickets for vip's that this might work for. Only problem is, the EA wants copies of the emails going forward.. and we dont have CC on because it was a nightmare. Maybe can store the original submitter email in a custom field and then use it to cc. hmmmm
