I have found a couple of questions for the same thing but none that give a full answer only ones that show an attempt at creating a script but they all ahave errors.

One says they have it working but do not say how.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

Service Desk > Configuration tab > Queues > Queue name > Email on Events > Click the box(es) for Owner Change

Answered 06/26/2013 by: mpace
Red Belt

  • This would need to be a ticket rule not a script. Here's the one I have to send an email on owner assignment:

    SELECT

    -- ticket fields
    HD_TICKET.ID, -- $id
    HD_TICKET.ID AS TICKNUM, -- $ticknum
    HD_TICKET.TITLE, -- $title
    DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
    DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified

    -- change fields
    C.COMMENT, -- $comment
    C.DESCRIPTION, -- $description
    GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
    H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox.wacoisd.org/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
    ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history


    -- about the updater
    UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
    UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
    UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
    IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional

    -- about the owner
    OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
    OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
    OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
    IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user

    -- about the submitter
    SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
    SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
    SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
    -- about priority
    P.NAME AS PRIORITY, -- $priority

    -- about status
    S.NAME AS STATUS, -- $status

    -- about impact
    I.NAME AS IMPACT, -- $impact

    -- about category
    CAT.NAME AS CATEGORY, -- $category

    -- other fields
    HD_TICKET.CUSTOM_FIELD_VALUE0 AS LOCATION, -- $location
    HD_TICKET.CUSTOM_FIELD_VALUE1 AS ROOM, -- $room
    HD_TICKET.CUSTOM_FIELD_VALUE2 AS WISD_NUM, -- $wisdnum
    HD_TICKET.CUSTOM_FIELD_VALUE7 AS EQUIPMENT, -- $equipment
    HD_TICKET.CUSTOM_FIELD_VALUE5 AS DESCRIPTION, -- $description
    HD_TICKET.CUSTOM_FIELD_VALUE6 AS USER_EMAIL, -- $user_email


    FROM HD_TICKET
    /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
    /*AND C.ID=<CHANGE_ID>*/
    /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
    /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
    /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
    /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
    /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
    /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
    /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
    /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
    WHERE
    C.DESCRIPTION LIKE 'Changed ticket Owner%'

    /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1

    I have some custom fields in there that we have set for our purposes. It should illustrate how to set your own custom fields.

    Then I check the box next to send an email for each row and use the variables set in the select query in the email:

    A Ticket has been assigned to you.

    Ticket information:
    Ticket: $ticknum
    From: $submitter_fname ($submitter_email)

    Location: $location
    Room: $room
    Equipment: $equipment
    Service Tag/WISD#: $wisd_num
    Description: $description

    Opening Comment: $comment
  • Upon further reflection and working on something unrelated I realized that this will send you an email every time the work order is saved, because after the first time it is assigned to someone it the field C.DESCRIPTION will always match the criteria. Working on a solution. I will add it when i figure it out.
  • The rule could only email the first time a ticket is assign to someone by changing

    C.DESCRIPTION LIKE 'Changed ticket Owner%'

    to

    C.DESCRIPTION LIKE 'Changed ticket Owner from"Unassigned" to%'

    Still working on one that will catch any owner change.
  • Email on events for THIS particular event seems most reasonable, (and trust me, I use ticket rules for almost every other email). You can click on CUSTOMIZE EMAILS to customize the physical email sent out.


    I have rules for emails for about every event but that one + ticket creation.
Please log in to comment
Answer this question or Comment on this question for clarity