Systems Management Question

Approver Rule K1000

05/03/2017 1062 views
I am trying to set up a rule that will send an email to the user that needs to approve the request.  I have part of it complete but I would like to include the actual request in the approval email. 

This is what I have so far.

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,

                        HD_TICKET.TITLE as ticket_title,

                        HD_TICKET_CHANGE.COMMENT as COMMENT,  /*COMMENTS*/

                        U2.FULL_NAME as submitter,


                        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,

                        U3.FULL_NAME as APPROVER_FULLNAME,

                        U3.EMAIL as APPROVER_EMAIL,

                        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  


                        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_TICKET.ID = HD_TICKET_ID  /*COMMENTS*/

                        and ((  HD_STATUS.NAME = 'Waiting for Approval') and HD_TICKET.HD_QUEUE_ID = 5)

                        GROUP BY HD_TICKET.ID

Do you Approve of the following request submitted by $submitter.
Ticket Title: $ticket_title
Ticket Comments: $comment
To APPROVE this request, please click here and then click Send an auto-generated email:
To NOT APPROVE this request, please click here and then click Send on auto-generated email:
Thank you,

1 Comment   [ + ] Show comment


  • So, what are you trying to add that isn't working? I'm not sure that the mailto tag will work that way and it will also depend on the email client that the user is using to read their email if it is rendered properly.
    • I would like the summary of the ticket to go to the Approver in the email when we change the approver. The email portion works as I have tested that part already. I just can't figure out how to get the summary in the email yet.
      • The summary should be available as $summary.

All Answers

You'll need to add this somewhere in your SELECT clause:
Then the summary field is available via the variable $summary
Answered 05/04/2017 by: JasonEgg
Red Belt

  • FYI, the wizard always includes HD_TICKET.* in the select clause, so there is no need to select the SUMMARY column on its own. The $summary variable should already be available.
    • Haha I didn't notice the first line! Also I was unaware that the wizard always includes it. Thanks Chuck!
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