There seems to have been many people unable to get a close ticket rule to work since the 5.4 upgrade based on old rules they were using or modifying ones they found online. Below is a confirmed working ticket rule for 5.4 that will email on closing a ticket.

 

**Warning** As a precaution first run this rule WITHOUT the send an email for each result by clicking run now at the bottom of the ticket rule screen. This ensures all prior tickets are marked as sent without generating emails for all past tickets.


**************NOTIFY ON TICKET CLOSE****************************

RULE TO NOTIFY ON TICKET CLOSE AND MARK THE TICKET AS EMAIL SENT

*************************SELECT SQL****************************

select

HD_TICKET . *,

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,

HD_IMPACT.ORDINAL as IMPACT_ORDINAL,

HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,

HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,

HTC.COMMENT_LOC as CRES,

STATE,

if((datediff(DUE_DATE, now()) = 0),2,

if((datediff(DUE_DATE, now()) < 0),1,3)) as SORT_OVERDUE_STATUS,

if(unix_timestamp(TIME_OPENED) > 0,TIME_OPENED,1 << 62) as SORT_TIME_OPENED,

if(unix_timestamp(TIME_STALLED) > 0,TIME_STALLED,1 << 62) as SORT_TIME_STALLED,

if(unix_timestamp(TIME_CLOSED) > 0,TIME_CLOSED,1 << 62) as SORT_TIME_CLOSED,

if(unix_timestamp(ESCALATED) > 0,ESCALATED,1 << 62) as SORT_ESCALATED,

if(unix_timestamp(HD_TICKET.CREATED) > 0,HD_TICKET.CREATED,1 << 62) as SORT_TIME_CREATED,

if(unix_timestamp(HD_TICKET.MODIFIED) > 0,HD_TICKET.MODIFIED,1 << 62) as SORT_MODIFIED,

if(unix_timestamp(HD_TICKET.DUE_DATE) > 0,HD_TICKET.DUE_DATE,1 << 62) as SORT_DUE_DATE,

case upper(STATE)

when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)

when 'OPENED' then unix_timestamp(now()) - unix_timestamp(HD_TICKET.TIME_OPENED)

else unix_timestamp(now()) - unix_timestamp(HD_TICKET.CREATED)

end as AGE,

if((length(U1.FULL_NAME) = 0),U1.USER_NAME,U1.FULL_NAME) as OWNER_NAME,U1.FULL_NAME as OWNER_FULLNAME,U1.EMAIL as OWNER_EMAIL,

if((length(U1.FULL_NAME) = 0),U1.USER_NAME,U1.FULL_NAME) as OWNER_NAME,U1.FULL_NAME as OWNER_FULLNAME,U1.EMAIL as OWNER_EMAIL,

if(U1.ID is null,'z',concat('a',

if((length(U1.FULL_NAME) = 0),U1.USER_NAME,U1.FULL_NAME))) as SORT_OWNER_NAME,

if((length(U2.FULL_NAME) = 0),U2.USER_NAME,U2.FULL_NAME) as SUBMITTER_NAME,U2.FULL_NAME as SUBMITTER_FULLNAME,U2.EMAIL as SUBMITTER_EMAIL,

if(U2.ID is null,'z',concat('a',

if((length(U2.FULL_NAME) = 0),U2.USER_NAME,U2.FULL_NAME))) as SORT_SUBMITTER_NAME,

if(U3.ID is null,'z',concat('a',

if((length(U3.FULL_NAME) = 0),U3.USER_NAME,U3.FULL_NAME))) as SORT_APPROVER_NAME,

if(APPROVAL = 'rejected','Rejected',

if(APPROVAL = 'info','More Info Needed',

if(APPROVAL = 'approved','Approved',

if(APPROVER_ID > 0, 'Pending', '')))) as APPROVAL_STATUS,

Q.NAME as QUEUE_NAME

from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)

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.APPROVER_ID

left join HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID

left join MACHINE M1 ON M1.ID = HD_TICKET.MACHINE_ID

left join HD_TICKET_CHANGE HTC on HTC.HD_TICKET_ID = HD_TICKET.ID

where HD_PRIORITY.ID = HD_PRIORITY_ID

and HTC.DESCRIPTION like '%Closed%'

and HD_STATUS.ID = HD_STATUS_ID

and HD_IMPACT.ID = HD_IMPACT_ID

and HD_CATEGORY.ID = HD_CATEGORY_ID

and ((HD_STATUS.STATE = 'closed')

and HD_TICKET.RESOLUTION not like '%Closed Email Sent')

and HD_TICKET.HD_QUEUE_ID = 1

**********************END SELECT*****************************

 

*********************EMAIL TEMPLATE***************************
 

SUBJECT:            Closure Notice for TICKET [$id]
 

EMAIL COLUMN:  SUBMITTER_EMAIL
 

EMAIL BODY:       $submitter_name,

                           Your ticket  '$title'

                           Was closed on $time_closed

                           See below

 

                           RESOLUTION DETAILS

                            ********************

                           Closed By:     $owner_name

                           Resolution:    $cres

                           Thanks for your business,

                           $owner_name

                           The Help desk Team
 

******************END EMAIL TEMPLATE**********************

 

********************UPDATE QUERY*************************
 

update HD_TICKET as T

set T.RESOLUTION = CONCAT(T.RESOLUTION,'

Closed Email Sent')

where (T.ID in (<TICKET_IDS>))
 

**********************END QUERY**************************