I saved this [Template] in my custom ticket rules and I duplicate it when I'm going to create a new rule.  Then I just add the criteria at the end and voila, you can create a very informative email.


select HD_TICKET.*, TC.COMMENT AS TCOMM, TC.TIMESTAMP AS TCOMMTIME,

                        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_CATEGORY.NAME as CATEGORY,

                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,

                        U1.EMAIL AS OWNER_EMAIL,

                        U1.FULL_NAME AS OWNER_FNAME,

                        U2.EMAIL AS SUBMITTER_EMAIL,

                        U2.FULL_NAME AS SUBMITTER_FNAME,

                        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_TICKET_CHANGE TC ON TC.HD_TICKET_ID = HD_TICKET.ID

                        LEFT JOIN HD_QUEUE Q on Q.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.HD_QUEUE_ID = 4

"optional" Order by tcommtime ASC limit 1

 

In this example "HD_QUEUE_ID = 4" is my main queue, but you can change the value to accommodate

Also the optional "Order by tcommtime ASC limit 1” gives you only the 1st comment on that ticket

                                " Order by tcommtime DESC limit 1" gives you only the last comment on that ticket

 

Here is what it will query and feed into the email body for this ticket rule:

 

Ticket Number:                 $id

Prob Desc:                          $title

Summary:                           $summary

Custom Field 1:                 $custom_field_value0

Status:                                 $status_name

Category:                            $category

Submitter:                          $submitter_fname

Submitter Email:               $submitter_email

Owner:                                 $owner_fname

Owner Email:                     $owner_email

Queue:                                 $queue_name

Comment:                           $tcomm