Possible to modify "sent from" address via ticket rule on outgoing messages
I'm trying to use the helpdesk of our kbox exclusively for change management, mainly requests for additional access, new logins to applications etc. I have everything setup more or less the way I'd like, but i've hit a roadblock. Right now, I'm using a ticket rule to email our main helpdesk the contents of the ticket once the request has been approved. The only problem with this, is that when our other helpdesk (track-it!) creates a ticket from the email the requestor is the kace, not the user. This is the expected behaviour, but the only problem with this, is that users won't get completion notifications when the ticket is closed in track-it unless a techician manually changes the requestor or logs in to the kace and completes the ticket there. My question is this; is it possible to manipulate the sender email address to match that of the submitter? That way, when the email is sent to track-it, it's appears to be sent from Jon.firstname.lastname@example.org instead of HR@company.com. This isn't a dealbreaker, i'm just trying to avoid technicians needing to login to two helpdesks. Here's my ticket rule, I set this all up using the extremely helpful blog post
select APPROVER_ID as APPROVER, 'email@example.com' as SUBMITTER, HD_TICKET.CUSTOM_FIELD_VALUE0 as FULLNAME, HD_TICKET.CUSTOM_FIELD_VALUE1 as JOBTITLE, HD_TICKET.CUSTOM_FIELD_VALUE2 as DEPARTMENT, HD_TICKET.CUSTOM_FIELD_VALUE3 as DATEOFHIRE, HD_TICKET.CUSTOM_FIELD_VALUE4 as EOB, HD_TICKET.TITLE as Issue, HD_TICKET.CUSTOM_FIELD_VALUE5 as Vision, HD_TICKET.CUSTOM_FIELD_VALUE6 as RFAX, HD_TICKET.CUSTOM_FIELD_VALUE7 as MEDVIEW, HD_TICKET.CUSTOM_FIELD_VALUE8 as EMR, HD_TICKET.CUSTOM_FIELD_VALUE9 as FILEMAKER, HD_TICKET.CUSTOM_FIELD_VALUE10 as MAILGROUP, HD_TICKET.CUSTOM_FIELD_VALUE11 as DRIVES, HD_CATEGORY.NAME as CATEGORY, 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, HD_IMPACT.ORDINAL as IMPACT_ORDINAL, HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL, HD_PRIORITY.ORDINAL as PRIORITY_NUMBER, STATE, if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS, if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED, if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED, if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED, if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED, if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED, if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED, if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE, case upper(STATE) when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED) when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE, if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME, U1.FULL_NAME as OWNER_FULLNAME, U1.EMAIL as OWNER_EMAIL, if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME, if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME, U2.FULL_NAME as SUBMITTER_FULLNAME, U2.EMAIL as SUBMITTER_EMAIL, if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME, if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME, if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS, Q.NAME as QUEUE_NAME 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 LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_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 (( HD_STATUS.NAME = 'Approved') and HD_TICKET.HD_QUEUE_ID = 2 )
Send an email for each result row
Subject: $issue: $status_name
Email Column: SUBMITTER
$category User: $fullname Job Title: $jobtitle Department: $department Effective Date: $dateofhire Status: $status_name Access requested ---------------------- EOB Express: $eob Vision: $vision RightFax Number: $rfax Medview: $medview NexTech EMR: $emr FileMaker: $filemaker Email Groups: $mailgroup Mapped Drives: $drives Ticket created at $created
Answer from chucksteel You need to pull from the USERS table to get the email address of the submitter. The update query would be something like this: UPDATE HD_QUEUE SET ALT_EMAIL_ADDR = (select EMAIL from HD_TICKET JOIN USER on HD_TICKET.SUBMITTER_ID = USER.ID where HD_TICKET.ID = ) WHERE HD_QUEUE.ID = 2 The is a variable that will get passed to the update query as long as your select statement grabs the ticket ids. If you created the rule with the wizard then it should be included in the select query. I didn't test this, but it should work. Have you given thought to how changing the alt email address will affect other parts of the helpdesk? If you don't have a corresponding rule to change the alt email address back to something else, then it will always be set as the last user that had a ticket submitted. You might want to have multiple rules that will: 1. Change the alt email address to the submitter 2. Perform any other ticket actions 3. Change the alt email address back to the the appropriate value This is an interesting idea, so please let us know if it works.