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
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.
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

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'

Answered 04/20/2012 by: andibogard
Blue Belt

  • 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)
  • 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.
  • 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
  • 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.
Please log in to comment
Answer this question or Comment on this question for clarity

Share