When a ticket is created or updated I would like to send the adminui to the technicians and the userui to the submitter but not send comments marked owners only to them.  I have tried creating a ticket rule for this but and email never gets sent. 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

Here's the rule we use to email owners on ticket changes:

Select statement:

 select HD_TICKET.ID, 
HD_TICKET.ID as TICKNUM, 
HD_TICKET.TITLE, 
U1.USER_NAME as OWNER_NAME, 
U3.USER_NAME as LASTINPUTNAME,  
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, 
HD_STATUS.NAME AS STATUS_NAME, 
HD_STATUS.ORDINAL as STATUS_ORDINAL, 
STATE, 
U1.FULL_NAME as OWNER_FULLNAME, 
U1.EMAIL as OWNER_EMAIL, 
U2.USER_NAME as SUBMITTER_NAME, 
U2.FULL_NAME as SUBMITTER_FULLNAME, 
U2.EMAIL as SUBMITTER_EMAIL, 
U3.EMAIL as UPDATEREMAIL, 
U3.FULL_NAME as UPDATERNAME,
U4.FULL_NAME as INITIALNAME,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
HD_TICKET_CHANGE.COMMENT,
HD_TICKET_CHANGE.DESCRIPTION as CHANGE_DESCRIPTION,
INITIAL_CHANGE.COMMENT as INITIAL_COMMENT,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
U2.LOCATION AS SUBMITTER_LOCATION,
U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_QUEUE.NAME AS QUEUE_NAME
from ( HD_TICKET, 
HD_PRIORITY, 
HD_STATUS, 
HD_IMPACT, 
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID 
 and HD_TICKET_CHANGE.ID=<CHANGE_ID>
JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID
 and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID 
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID 
left join USER U4 on U4.ID = INITIAL_CHANGE.USER_ID
left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID  and 
HD_STATUS.ID = HD_STATUS_ID  and 
HD_IMPACT.ID = HD_IMPACT_ID  and 
HD_CATEGORY.ID = HD_CATEGORY_ID  and
HD_TICKET.OWNER_ID != HD_TICKET_CHANGE.USER_ID and
HD_TICKET_CHANGE.DESCRIPTION not like "%Ticket Created%" and
U1.EMAIL != 'helpdeskemailaddress' and 
HD_TICKET.HD_QUEUE_ID = 2 and
HD_STATUS.NAME != 'Closed'

Email sent for each row:

 The following work order has been updated in the $queue_name queue by $updatername:

Title: $title
Submitter: $submitter_fullname
Submitter Phone: $submitter_work_phone
Submitter Location: $submitter_location
Category: $category_name
Priority: $ticket_priority

Changes:
$change_description

Comment ($updatername): 
$comment

Initial Comment ($initialname):
$initial_comment

View the ticket here:
https://kacehostname/adminui/ticket?ID=$ticknum

You are receiving this email because you are the owner of this ticket.

Most of our queues have a similar rule in place, although some departments like them to sent on different criteria, not just on any change to the ticket.

 

Answered 10/09/2012 by: chucksteel
Red Belt

Please log in to comment
1

You have to use custom rules to change the URLs being sent to the user (as opposed to the system rules). If you post the rules you have we can help figure out why they aren't working.

 

Answered 10/08/2012 by: chucksteel
Red Belt

  • select C.ID,
    HD_TICKET.ID TICKET_NUMBER,
    NOTIFIERS.EMAIL NOTIFY_USER,
    P.NAME TICKET_PRIORITY,
    HD_TICKET.TITLE TICKET_TITLE,
    case when PV.VALUE<>'HIDE' then CONCAT('http://',KBOX.HOST,'/adminui/ticket.php?ID=',HD_TICKET.ID)
    else CONCAT('http://',KBOX.HOST,'/adminui/ticket.php?ID=',HD_TICKET.ID) end TICKET_URL,

    IFNULL(GROUP_CONCAT(DISTINCT CONCAT('----- Change by ', UPDATER.EMAIL,if(H.VIA_EMAIL=',',' (via email)'),' at ',cast(H.TIMESTAMP as char),' -----\n',
    H.DESCRIPTION,'\n',H.COMMENT)
    ORDER BY H.ID DESC SEPARATOR '\n'),'no user entries') TICKET_HISTORY,
    P.ESCALATION_MINUTES TICKET_ESCALATION_MINUTES,

    IFNULL(SUBMITTER.FULL_NAME,'unassigned') TICKET_SUBMITTER_NAME,
    IFNULL(OWNER.FULL_NAME,'unassigend') TICKET_OWNER_NAME,
    IFNULL(SUBMITTER.EMAIL,'unassigned') TICKET_SUBMITTER_EMAIL,
    IFNULL(OWNER.EMAIL,'unassigend') TICKET_OWNER_EMAIL,

    case when NOTIFIERS.ID=OWNER.ID then CONCAT(UPDATER.EMAIL,if(C.VIA_EMAIL=',',' (via email)'),' at ',cast(C.TIMESTAMP as char),'-----\n',
    C.DESCRIPTION,'\n',C.OWNERS_ONLY_DESCRIPTION,'\n',C.COMMENT)
    else CONCAT(UPDATER.EMAIL,if(C.VIA_EMAIL=',',' (via email)'),' at ',cast(C.TIMESTAMP as char),'-----\n',
    C.DESCRIPTION,'\n',C.COMMENT) end CHANGE_DESC /* future goal: modify the owners to be based on owner label */

    from HD_TICKET
    JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>
    left JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID=HD_TICKET.ID and H.OWNERS_ONLY=0
    JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
    JOIN USER NOTIFIERS ON
    /* notifiers is in the list */
    (FIND_IN_SET(NOTIFIERS.ID,C.NOTIFY_USERS)>0 and
    CONCAT(NOTIFIERS.EMAIL,',')
    RLIKE '^([[:alnum:][.period.][.hyphen.][.underscore.]]+@([[:alnum:][.hyphen.][.underscore.]]+[.period.])+([[:alnum:][.hyphen.]]){2,4}[[.comma.]]+[[:space:]]*)+$' )
    OR
    /* email is in list and it's a valid address */
    (FIND_IN_SET(NOTIFIERS.EMAIL,C.NOTIFY_USERS)>0 and
    CONCAT(NOTIFIERS.EMAIL,',')
    RLIKE '^([[:alnum:][.period.][.hyphen.][.underscore.]]+@([[:alnum:][.hyphen.][.underscore.]]+[.period.])+([[:alnum:][.hyphen.]]){2,4}[[.comma.]]+[[:space:]]*)+$' )
    JOIN USER_ROLE_PERMISSION_VALUE PV ON PV.ROLE_ID=NOTIFIERS.ROLE_ID and PV.PERMISSION_ID=43
    JOIN (select 'kbox.it.ashland.edu'
    HOST) KBOX ON 1=1
    LEFT JOIN USER OWNER ON OWNER.ID=OWNER_ID
    LEFT JOIN USER SUBMITTER ON SUBMITTER.ID=SUBMITTER_ID and C.OWNERS_ONLY=0
    LEFT JOIN USER UPDATER ON C.USER_ID=UPDATER.ID
  • I don't construct the URL as part of the SQL query and instead I just use the $id variable in the ticket email (e.g. https://kaceurl/adminui/ticket.php?ID=18241). I also have different rules to notify users vs technicians, especially since we send emails based on different triggers to those sets of people.

    Your statements for getting who to notify are more complicated than I am used to seeing, as well, so I'm not sure what you're querying for exactly to determine who should be notified.

    Have you tried having the results of the query emailed to you in order to make sure it is generating the results you need?
  • Can you post an example of one of the rules you have for going to technicians versus owners? Thanks
  • I'm not sure what you mean by technicians vs owners.
    • Sorry I meant owners (technicians) versus submitters
  • You could actually use the same select query that I posted but instead of emailing to OWNER_EMAIL as the column use SUBMITTER_EMAIL.
Please log in to comment
Answer this question or Comment on this question for clarity

Share