/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Custom Ticket Rule to send email notification to a group of people on owner change within a ticket

03/06/2019 368 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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ