/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Dell K1000 - Service Desk - Ticketrule; send mail on ticket close

09/19/2016 1418 views
Hello,

I want to send an email to inform the ticketopener that the ticket is close
I know it exist 'ticket on event' but I don't want the line '-+-+-  Please reply above this line to add a comment  -+-+-' in the mail notification.

It's maybe an easy one, but I'm not good in mySQL so I hope for help.

Thanks!
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1
Sorry, I've found it...

SQL-select:
 select HD_TICKET.*,
                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,
                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 (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
                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_STATUS.STATE = 'closed')
                and HD_TICKET.RESOLUTION not like '%Closed Email Sent')
                and HD_TICKET.HD_QUEUE_ID = 10

send an email to..

subject:                                                                  column with mail adress:
[TICKET-ID:$id] Ticket closed.SUBMITTER_EMAIL                             

mail:
Your email text.

SQL-update:
 update HD_TICKET as T
set T.RESOLUTION = CONCAT(T.RESOLUTION,'
Closed Email Sent')
where (T.ID in (<TICKET_IDS>))


Hope it's helpful for someone who have the same question.




Answered 09/19/2016 by: svmay
Red 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