/build/static/layout/Breadcrumb_cap_w.png
09/15/2016 2666 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?