KACE Product Support

Trying to add all comments to a ticket rule that emails a group on ticket save.  As long as ticket status is not closed and ticket meets requirements, I want it to send the updated information to the group.  I can get initial comment but not the additional comments.  I would like it to look like the ticket history at the bottom of each ticket.  Newest to oldest - top to bottom.  Example:

Date of change, time of change, changed by

Comment…

Date of change, time of change, changed by

Comment…

I am not a mysql person, so I don’t know how to formulate the statements correctly to get only the comments in the email.  This is actually my first time working with the ticket rules.  Everything in the query appears to work fine and happens on ticket saves.  Just wondering if anyone could explain to me how to add the comments to my query or help me write that part.

Any help would be greatly appreciated.  My working query minus the comment tries:

 Select Query:

                        HD_STATUS.NAME AS STATUS_NAME,

                        HD_STATUS.ORDINAL as STATUS_ORDINAL,

                        HD_IMPACT.ORDINAL as IMPACT_ORDINAL,

                        HD_IMPACT.NAME as IMPACT_NAME,

                        HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,

                        HD_CATEGORY.NAME as CATEGORY_NAME,

                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,

                        HD_TICKET.CUSTOM_FIELD_VALUE0 as CHANGE_LOCATION,

                        HD_TICKET.CUSTOM_FIELD_VALUE1 as DEVICE_NAME,

                        HD_TICKET.CUSTOM_FIELD_VALUE2 as APP_TITLE,

                        'email@email.com' as CHANGECONTROLEMAIL,

                         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_IMPACT.NAME != '1 user') AND HD_STATUS.NAME != 'closed') AND HD_CATEGORY.NAME != 'self service') AND HD_CATEGORY.NAME not like 'network printer%') and HD_TICKET.HD_QUEUE_ID = 7 )

Send an email for each result:

Subject:   [TICK:$id] Change Control – $title

Email Column:  CHANGECONTROLEMAIL

Email Body: 

Title:  $title

http://kboxname.domain.local/userui/ticket?ID=$id

Ticket Number: $id

Ticket Submitter: $submitter_fullname

Ticket Owner: $owner_fullname

Impact: $impact_name

Status: $status_name

Location: $change_location

Category: $category_name

Device Name: $device_name

App Title: $app_title

Comment: