/build/static/layout/Breadcrumb_cap_w.png

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?


0 Comments   [ + ] Show comments

Answers (2)

Posted by: Hobbsy 1 month ago
Red Belt
0

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 1 month 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 1 month ago
      • Is the USER_ID from the email system? Or does KACE store the USER_ID in a table somewhere? - clowninja2021 1 month ago
Posted by: barchetta 1 month ago
Senior Purple Belt
0

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

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ