I've done some searches on something similar, but none of the results I've found really do what I need.

Every time a new ticket is opened with a comment, the status goes to "Customer Responded" instead of "New", causing trouble with our site techs' queues. I'd like new tickets to remain labeled with status "New", even if a comment is added.

I'd also like help tickets to remain listed with a "New" status when anyone - other than the original submitter - posts a comment to the ticket. So when technicians use the comment field to communicate with each other, I want the status to remain new, but when the submitter leaves a comment, I want it to change to "Customer Responded".

Roles I want able to comment without changing the status are called: Admin, Admin (LDAP), Technology Learning Coordinator.

Labels I want able to comment without changing the status are called: Manager Group, Test Queue Ticket Owners

Roles and labels I want to have change the status when they comment: User, User (LDAP), End user

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2

You can duplicate the "customer responded" rule and make changes to it.  I'm not good with MySQL, but perhaps someone can help you modify the select/update query:

Select query:

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 (1)
                       and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)
                       and UPDATER.ID > 0

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 = 'Opened' and
T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and  (T.ID in (<TICKET_IDS>))
Answered 09/13/2012 by: jknox
Red Belt

  • This query code is the default code from the "CustomerResponded" default rule, would it be possible for some extra help on this as i have the same issue and can't get the code configured correctly.
    • I took this rule and within the WHERE filter I literally had to add a string of :
      and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)
      and UPDATER.ID > 0
      and UPDATER.ID <> '1384'
      and UPDATER.ID <> '1387'

      (Those ID's = owners of tickets within that queue)

      This worked when I had 1 queue and just a few tech's/owners but now that I've moved to a multi-queue environment (per department) it can be a maintenance nightmare.
      • to stop the nightmare change the first line on your response with: and (UPDATER.ID= HD_TICKET.SUBMITTER_ID) and delete the rest.
    • (UPDATER.ID= HD_TICKET.SUBMITTER_ID) would be ideal if we only expected 1 person to update 1 ticket. We work in an environment where any kind of 'problem' ticket can almost always expect 2 or 3 involved parties. on a bigger scale, when dealing with certain processes or group issues, we've had tickets with up to 6 people on it.
Please log in to comment

Answers

1
I have managed to get this working by updating the select query and update query as follows:

Select Query:

Change:
and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)
To:
and (UPDATER.ID= HD_TICKET.SUBMITTER_ID) 

Update Query:
Deleted all text and replaced with:

update HD_TICKET
    set HD_TICKET.HD_STATUS_ID = '34'
  where 
        (HD_TICKET.ID in (<TICKET_IDS>))


Answered 06/25/2014 by: chris.poston
Senior Purple Belt

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