/build/static/layout/Breadcrumb_cap_w.png

Dell K1000 - Service Desk - Ticketrule; send mail on duedate

Hello,

is it possible to send an email to the ticketowner as a kind of reminder?
For example - a ticket is create today (21.09.16) and the ticket must closed on 23.09.16.
So on 23.09.16, the kace send automatically an email to the ticketowner with - "ehi you must do that ticket today!"

Hope for help! Thanks!
Asked 8 years ago 1632 views
0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: svmay 8 years ago
Red Belt
2

Top Answer

Solution:

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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
                        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 ((( DATE(HD_TICKET.DUE_DATE) = CURDATE()) and HD_TICKET.OWNER_ID not like 0) and HD_TICKET.HD_QUEUE_ID = 10 )
send an email to..

subject:                                                                  column with mail adress:
[TICKET-ID:$id] reach duedate.OWNER_EMAIL                                    
mail:
Your email text.





Comments:
  • Hi @svmay
    Got redirected here by you in another question I posted...
    (Anyone know how to get a "new ticket" to send an email to a supervisor,if it hasn't been opened by its "owner" for a specified amount of time ?)
    So in my case its not to the owner, but to the head of IT. And he is also a user in kace with an email like everyone else.
    Where do I modify in your script to reflect his "email", the "time interval" for it to be sent, and the "email content"?
    Thanks. - akmagnum 5 years ago
Posted by: Ericenri 8 years ago
Senior Purple Belt
1
Yes, it is possible, by creating a custom ticket rule. However, I just went thru the Wizard and it seems that the specific option is not showing at the drop down menu so you might need to create one directly using SQL if you are familiar with it.
Comments:
  • Hello Ericenri, thanks for your response.
    I'm not so familiar with SQL..

    I created a custom rule, with
    .. HD_TICKET.DUE_DATE != CURDATE() ..

    In this case, tickets with today due_date and tomorrow due_date are selected too..

    Hope you can help me, thanks!

    EDIT: found a way, post is comming - svmay 8 years ago
 
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