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.b@company.com 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
http://www.itninja.com/blog/view/k1000-service-desk-setup-tips-things-i-have-learned
select APPROVER_ID as APPROVER, 'test.submitter@mycompany.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
Email Body:
$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
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
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.
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.
Please log in to answer
Posted by:
andibogard
12 years ago
Ok, well I figured out I can change ALT_EMAIL_ADDR with an update query, but I'm having issues making it all work. For starters, I only want it to updated the alt_email_addr for the queue with an ID of 2. Also, i'm having problems setting the alt_email_addr to match the ticket submitter's email.
update HD_QUEUE
set ALT_EMAIL_ADDR = 'submitter@domain.com'
Comments:
-
A little more progress to the update query. Though i'm looking for the email address of the submitter, not the ID#.
UPDATE HD_QUEUE
SET ALT_EMAIL_ADDR =
(SELECT HD_TICKET.SUBMITTER_ID
FROM HD_TICKET) - andibogard 12 years ago -
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. - chucksteel 12 years ago -
I think what I'm going to do is create a new rule to handle the email address change and point it at any ticket in approved status, before the "request approved" rule is run. Then, as part of an update query in the "request approved" rule, i'll change the ticket status to something like closed or completed and set ALT_EMAIL_ADDR back to the correct address. I'll test it all out and post the results. Thanks so much for your help - andibogard 12 years ago
-
Everything seems to be working. Here's a top level view of the ticket rules I'm using.
Rejected Status - Changes ticket status to 'rejected' for any ticket with matching approval status
Approved Status - Changes ticket status to 'approved' for any ticket with matching approval status
Manager as approver - Sets manager as approver for existing employee access forms(user custom field 1 is mapped to their manager when we import their account into kace)
Ticket Owner as Approver - Sets HR as approver for new employee access forms
Ticket Title Update - Existing Employee - Changes ticket title of any ticket with 'Existing Employee .....' as ticket category to Existing Employee ..... + employee name and effective date (custom fields 1 and 2)
ex Existing Employee Network Access Change Form - John Smith (7/21/2012)
Ticket Title Update - New Employee - Changes ticket title of any ticket with 'New Employee .....' as ticket category to New Employee ..... + employee name and effective date (custom fields 1 and 2)
ex New Employee Network Access Change Form - John Smith (7/21/2012)
Waiting On Approval - Emails approver and changes status to 'waiting on approval - notification sent'
Change email - Changes alt_email_addr for the queue to that of the submitter
Request Approved - Sends an email summarizing the requested changes to our main helpdesk so technicians can complete
Ticket Delay - Not sure if this is actually doing anything, but seems to be. Initially, it seemed like the email address was being reset before the email went out.
Reset Email - Sets alt_emal_addr back to default
I played with some of the select queries to exclude tickets based on category, status etc., but this is more or less how I set my rules up. I tested yesterday and everything seems to be working. I don't foresee any problems as we won't be using this queue for anything else and won't be setting approvals manually, only via email. There should be a long enough delay between approvals to keep the alt_email_addr from being set based off the wrong ticket submitter. - andibogard 12 years ago