/build/static/layout/Breadcrumb_cap_w.png
03/06/2019 317 views

Hi guys,

Can someone help me with a CTR for sending email notifications to 3 people when a ticket owner changes? Or is there a way to do this? 

I am unable to figure this out and after research wasn't able to come up with anything.

Thanks,

Khan

1 Comment   [ + ] Show comment

Comments

  • I have this query which does not seem to work ----

    Select SQL:

    SELECT
    -- ticket fields
    HD_TICKET.ID, -- $id
    HD_TICKET.ID AS TICKNUM, -- $ticknum
    HD_TICKET.TITLE, -- $title
    HD_TICKET.SUMMARY as ticket_summary, -- $ticket_summary
    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)
    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
    -- -- example of static distribution list
    'EMAIL DISTRIBUTION LIST' AS NEWTICKETEMAIL -- $newticketemail
    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%')
    and OWNER.USER_NAME is not null
    /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1

    ----------------------------------------

    Email each recipient in query results:
    Subject: [TICK:$ticknum] Owner has been changed to $owner_fname

    Column Containing email addresses: NEWTICKETEMAL

    Message:
    Owner has been changed to $owner_fname.

    Ticket can be accessed below:
    http://ksma.wavelifesci.com/adminui/ticket?ID=$ticknum

    The original submission was:
    Ticket: $ticknum
    Submitter: $submitter_fname ($submitter_email)
    Category: $category
    Priority: $priority
    Status: $status
    Severity: $impact
    Created: $created
    Last Modified: $modified

    Title: $title
    Description: $ticket_summary



    This is not working - clearly my SQL is broken --- any help on this is appreciated!

All Answers

0

The above code mentioned in my comment is working -- I forgot to enable the code. 


Answered 03/07/2019 by: khanooo
White Belt