/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Dell K1000 - Service Desk - Ticketrule; send mail to new owner

09/15/2016 2887 views
Hello,

I search for a ticketrule to inform the servicedesk supporter, that he is the new owner of a ticket.
(for example: one coworker assign the tickets to the right supporter and he should be inform by an email)

Note: Servicedesk support is an label with 8 co-workers

Hope for help, thanks!
Answer Summary:
2 Comments   [ + ] Show comments

Comments

  • You may want to look at the following thread: http://www.itninja.com/question/kace-sending-new-owner-an-email-when-ticket-is-re-assigned
  • Thanks for you response. I think thats what I'm looking for!

Answer Chosen by the Author

1
Thanks Ericenri for the link, its exactly what I'm looking for!

> http://www.itninja.com/question/kace-sending-new-owner-an-email-when-ticket-is-re-assigned



SELECT 
    HD_TICKET.ID,
    HD_TICKET.ID AS TICKNUM,
    HD_TICKET.TITLE,
    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,
    C.COMMENT,
    C.DESCRIPTION,
    GROUP_CONCAT(CONCAT('----- Change by ',
                UPDATER.EMAIL,
                ' at ',
                H.TIMESTAMP,
                ' -----
                ',
                H.DESCRIPTION,
                '
                ',
                H.COMMENT,
                '
                
                Please see your ticket at http://kbox/userui/ticket.php?ID=',
                H.HD_TICKET_ID,
                '
                ')
        ORDER BY H.ID DESC
        SEPARATOR '
        ') HISTORY,
    UPDATER.USER_NAME AS UPDATER_UNAME,
    UPDATER.FULL_NAME AS UPDATER_FNAME,
    UPDATER.EMAIL AS UPDATER_EMAIL,
    IF(UPDATER.FULL_NAME = '',
        UPDATER.USER_NAME,
        UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL,
    OWNER.USER_NAME AS OWNER_UNAME,
    OWNER.FULL_NAME AS OWNER_FNAME,
    OWNER.EMAIL AS OWNER_EMAIL,
    IFNULL(OWNER.USER_NAME, 'Unassigned') OWNER_USER,
    SUBMITTER.USER_NAME AS SUBMITTER_UNAME,
    SUBMITTER.FULL_NAME AS SUBMITTER_FNAME,
    SUBMITTER.EMAIL AS SUBMITTER_EMAIL,
    P.NAME AS PRIORITY,
    S.NAME AS STATUS,
    I.NAME AS IMPACT,
    CAT.NAME AS CATEGORY,
    HD_QUEUE.NAME AS QUEUENAME
FROM
    HD_TICKET
        JOIN
    HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
        AND C.ID = <CHANGE_ID>
        JOIN
    HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
        JOIN
    HD_PRIORITY P ON P.ID = HD_PRIORITY_ID
        JOIN
    HD_STATUS S ON S.ID = HD_STATUS_ID
        JOIN
    HD_IMPACT I ON I.ID = HD_IMPACT_ID
        JOIN
    HD_CATEGORY CAT ON CAT.ID = HD_CATEGORY_ID
        LEFT JOIN
    USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
        LEFT JOIN
    USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
        LEFT JOIN
    USER UPDATER ON UPDATER.ID = C.USER_ID
        JOIN
    HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
WHERE
    (C.DESCRIPTION LIKE '%Changed ticket Owner%')
        and OWNER.USER_NAME is not null

HAVING 1 = 1

.



Answered 09/16/2016 by: svmay
Red Belt

  • How did you add this rule... can you upload a screenshot from Kace?
    • > Service Desk > Configuration > Rules
      > "Your Queue" > Select Action > New (SQL)

      Give the rule a name and write a description

      Insert at "SQL-Select" the select-query I had posted. Confirm the checkbox "Send e-mail to each recipient in the query results". Add an mailsubject and insert in "column with e-mail addresses" - "OWNER_EMAIL". At least write an mailnotification text.

      Hope this helps.
      • Thank you for the response. I think I am missing something. MySQL skills are.. weak :-) Email isn't triggering though here is the run log after a saved a ticket.

        2016/12/13 17:21:02> Starting: 2016/12/13 17:21:02 2016/12/13 17:21:02> Executing Select Query... 2016/12/13 17:21:02> mysqli error: [1054: Unknown column 'OLIST.EMAIL' in 'group statement'] in EXECUTE("SELECT HD_TICKET.ID, HD_TICKET.ID AS TICKNUM, HD_TICKET.TITLE, 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, C.COMMENT, C.DESCRIPTION, GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL, ' at ', H.TIMESTAMP, ' ----- ', H.DESCRIPTION, ' ', H.COMMENT, ' Please see your ticket at http://kbox/userui/ticket.php?ID=', H.HD_TICKET_ID, ' ') ORDER BY H.ID DESC SEPARATOR ' ') HISTORY, UPDATER.USER_NAME AS UPDATER_UNAME, UPDATER.FULL_NAME AS UPDATER_FNAME, UPDATER.EMAIL AS UPDATER_EMAIL, IF(UPDATER.FULL_NAME = '', UPDATER.USER_NAME, UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, OWNER.USER_NAME AS OWNER_UNAME, OWNER.FULL_NAME AS OWNER_FNAME, OWNER.EMAIL AS OWNER_EMAIL, IFNULL(OWNER.USER_NAME, 'Unassigned') OWNER_USER, SUBMITTER.USER_NAME AS SUBMITTER_UNAME, SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, SUBMITTER.EMAIL AS SUBMITTER_EMAIL, P.NAME AS PRIORITY, S.NAME AS STATUS, I.NAME AS IMPACT, CAT.NAME AS CATEGORY, HD_QUEUE.NAME AS QUEUENAME FROM HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID = 384611 JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID JOIN HD_PRIORITY P ON P.ID = HD_PRIORITY_ID JOIN HD_STATUS S ON S.ID = HD_STATUS_ID JOIN HD_IMPACT I ON I.ID = HD_IMPACT_ID JOIN HD_CATEGORY CAT ON CAT.ID = HD_CATEGORY_ID LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID WHERE (C.DESCRIPTION LIKE '%Changed ticket Owner%') and OWNER.USER_NAME is not null GROUP BY OLIST.EMAIL HAVING 1 = 1 and (HD_TICKET.ID = 40394) ")
  • Screenshot -- http://imgur.com/a/xEVtO
    • Try this:

      delete in the sql-select the row at the end
      "GROUP BY OLIST.EMAIL"

      and try again - hope this helps
    • Did it work?
 
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