I think it is supposed to work something like this. Ticket Owner makes a comment on an open ticket and the status should change to “waiting on customer”. If a customer has not responded to an owners comment within 3 days, they will get an email that says they need to reply within the next 4 days,(it is now in “Waiting Overdue”). If they did not respond after that (7 days), the ticket is closed and status will change to “overdue closed”. If the customer responds even weeks later their response automatically reopens the ticket, the status should change to “reopened”.

If the customer replies to the owner before the ticket is closed, it should change to “customer responded”.

When the submitter adds a comment, it should not change to “waiting on customer” since the submitter is the customer.  But it does.

If this is how the process should work, the rules are not working correctly. Help is appreciated. 

 

Waiting on customer rule:

select C.ID

FROM HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>

JOIN HD_STATUS S on HD_STATUS_ID=S.ID

JOIN HD_TICKET_CHANGE_FIELD FCOM ON FCOM.HD_TICKET_CHANGE_ID=C.ID and FCOM.FIELD_CHANGED='COMMENT'

LEFT JOIN HD_TICKET_CHANGE_FIELD FSTAT ON FSTAT.HD_TICKET_CHANGE_ID=C.ID AND FSTAT.FIELD_CHANGED='STATUS_NAME'

WHERE

FSTAT.ID IS NULL

and OWNERS_ONLY=0

and S.NAME<>'Waiting On Customer'

AND DESCRIPTION NOT LIKE 'TICKET CREATED%'

 

Update Query:

update HD_TICKET as T JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=T.ID

JOIN HD_STATUS S ON T.HD_QUEUE_ID=S.HD_QUEUE_ID

JOIN HD_STATUS S_OLD ON HD_STATUS_ID=S_OLD.ID

    set DESCRIPTION=CONCAT(DESCRIPTION,'\nOwner Reply Triggered a Status Change. [Rule]\nChanged ticket Status from "',S_OLD.NAME,'" to "',S.NAME,'"'),

HD_STATUS_ID=S.ID

  where

S.NAME='Waiting On Customer' and

        (C.ID in (<TICKET_IDS>))

Waiting Overdue

Moves ticket into an overdue status when it has been waiting on a customer action for a period of time

Select Query:

select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,

                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.NAME = 'Waiting on Customer'))

                       and DATE_SUB(NOW(), INTERVAL 3 DAY) > DATE(HD_TICKET.MODIFIED)

                       and HD_TICKET.HD_QUEUE_ID = 1

Comments: If no update is received in the next 4 days, the ticket will be closed automatically.

In general all support requests should include the following information:

Contact information,

Site/Location

Computer affected,

Detailed description of the problem,

and best time to contact.

If this information is not provided you may experience considerable delay in the resolution of your support request.

Regards,

NMUSD Help Desk

Update Query:

update HD_TICKET as T, HD_STATUS as T5

set T.HD_STATUS_ID = T5.ID,

T.TIME_OPENED  = IF(T5.STATE = 'opened', NOW(), T.TIME_OPENED),

T.TIME_CLOSED  = IF(T5.STATE = 'closed', NOW(), T.TIME_CLOSED),

T.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), T.TIME_STALLED),

T.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_RATING),

T.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_COMMENT)

where T5.NAME = 'Waiting Overdue' and

T.HD_QUEUE_ID = T5.HD_QUEUE_ID and  (T.ID in (<TICKET_IDS>))

 

CustomerResponded Rule

select distinct HD_TICKET.ID,

                           HD_TICKET.OWNER_ID as OWNER_ID,

                           HD_TICKET.ID as TICKNUM,

                           HD_TICKET.TITLE,

                           HD_STATUS.NAME AS STATUS_NAME,

                           HD_STATUS.STATE as STATE,

                           OWNER.USER_NAME as OWNER_NAME,

                           OWNER.FULL_NAME as OWNER_FULLNAME,

                           OWNER.EMAIL as OWNER_EMAIL,

                           UPDATER.USER_NAME as UPDATERNAME,

                           UPDATER.EMAIL as UPDATEREMAIL

                      from (HD_TICKET, HD_STATUS)

                 left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = <CHANGE_ID>

                 left join HD_TICKET_CHANGE_FIELD on HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID

                 left join USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID

                 left join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID

                     where HD_STATUS.ID = HD_TICKET.HD_STATUS_ID

                       and HD_TICKET_CHANGE.HD_TICKET_ID= HD_TICKET.ID

                       and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_RATING'

                       and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_COMMENT'

                       and HD_STATUS.ID in (25,9)

                       and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)

                       and UPDATER.ID > 0

 X Send an email for each result row

Subject:  Ticket $ticknum - Customer Responded

Email Column:  OWNER_EMAIL

Ticket $ticknum owned by "$owner_name" has been reopened because the customer or other non-owner made a ticket update.

               

You may review the ticket here:

http://kbox1200.<nmusd>.us/adminui/ticket?ID=$ticknum

The status was "$status_name" and is now marked as "Reopened".

Update Query:

update HD_TICKET as T, HD_STATUS as STATUS

set T.HD_STATUS_ID = STATUS.ID,

T.RESOLUTION = CONCAT(T.RESOLUTION,'

Reopened'),

T.TIME_OPENED  = IF(STATUS.STATE = 'opened', NOW(), T.TIME_OPENED),

T.TIME_CLOSED  = IF(STATUS.STATE = 'closed', NOW(), T.TIME_CLOSED),

T.TIME_STALLED = IF(STATUS.STATE = 'stalled', NOW(), T.TIME_STALLED),

T.SATISFACTION_RATING = IF(STATUS.STATE = 'closed', NULL, T.SATISFACTION_RATING),

T.SATISFACTION_COMMENT = IF(STATUS.STATE = 'closed', NULL, T.SATISFACTION_COMMENT)

where STATUS.NAME = 'Reopened' and

T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and  (T.ID in (<TICKET_IDS>))

ReopenTicket rule

Notes: Reopens a closed ticket if someone other than the owner updates it, and notifies the owner by email

select distinct HD_TICKET.ID,

               HD_TICKET.OWNER_ID as OWNER_ID,

               HD_TICKET.ID as TICKNUM,

               HD_TICKET.TITLE,

               HD_STATUS.NAME AS STATUS_NAME,

               HD_STATUS.STATE as STATE,

               OWNER.USER_NAME as OWNER_NAME,

               OWNER.FULL_NAME as OWNER_FULLNAME,

               OWNER.EMAIL as OWNER_EMAIL,

               UPDATER.USER_NAME as UPDATERNAME,

               UPDATER.EMAIL as UPDATEREMAIL

          from (HD_TICKET, HD_STATUS)

     left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = <CHANGE_ID>

     left join HD_TICKET_CHANGE_FIELD on HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID

     left join USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID

     left join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID

         where HD_STATUS.ID = HD_TICKET.HD_STATUS_ID

           and HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID

           and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_RATING'

           and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_COMMENT'

           and HD_STATUS.STATE = 'closed'

           and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)

           and UPDATER.ID > 0

Comment: This closed ticket was updated by a non-owner.  Changing status to "Reopened."

Note: If you were trying to close this case: You MUST take ownership to close a case.

Subject: Ticket $ticknum Reopened

Email Column: Closed ticket $ticknum owned by "$owner_name" has been reopened.

                 

You may review the ticket here:

http://kbox1200.<nmusd>.us/adminui/ticket?ID=$ticknum

The status was "$status_name" and is now marked as "Reopened".

 

Update Query:

update HD_TICKET as T, HD_STATUS as STATUS

set T.HD_STATUS_ID = STATUS.ID,

T.RESOLUTION = CONCAT(T.RESOLUTION,'

Reopened'),

T.TIME_OPENED  = IF(STATUS.STATE = 'opened', NOW(), T.TIME_OPENED),

T.TIME_CLOSED  = IF(STATUS.STATE = 'closed', NOW(), T.TIME_CLOSED),

T.TIME_STALLED = IF(STATUS.STATE = 'stalled', NOW(), T.TIME_STALLED),

T.SATISFACTION_RATING = IF(STATUS.STATE = 'closed', NULL, T.SATISFACTION_RATING),

T.SATISFACTION_COMMENT = IF(STATUS.STATE = 'closed', NULL, T.SATISFACTION_COMMENT)

where STATUS.NAME = 'Reopened' and

T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and  (T.ID in (<TICKET_IDS>))

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

This is a lot to look at. For the first rule that changes the status to waiting on customer you are only selecting C.ID which selects comment IDs. The update rule only acts on Ticket IDs, however, so I don't think that will work. You need to select ticket IDs and then just change the ticket's status. I have never tried modifying the comment to add a description like you are attempting. Instead I use the function in the rule to add a comment that the rule ran. This also helps separate out the actions and makes it more clear that the rule ran, in my opinion. 

Answered 08/10/2012 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share