Has anyone come across an issue if a ticket status is set to Waiting on customer and end user goes back and adds an attachment hits submit and save the status doesnt change to Customer responded. Which will never trigger an email to the ticket owner notifying ticket has been updated.

We have only to come to find out after few users complained they have been waiting update on tickets and ticket owners never getting a notification the ticket was updated. When we went back to check all those ticket were still sitting on waiting on customer though the customer went in and added an attachment the status never changed.

Per Quest this has been registered as new a bug. 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
EDIT (4/18/17): fixed my queries

A workaround would be to setup a Custom Ticket Rule.
SELECT:
select HD_TICKET_ID AS ID from HD_TICKET_CHANGE 
join HD_TICKET on HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
where DESCRIPTION like '%Added attachment%'

UPDATE:
UPDATE HD_TICKET 
SET STATUS_ID = [your status id]
WHERE ID in (<TICKET_IDS>)
Answered 03/21/2017 by: JasonEgg
Red Belt

  • Jason - I am going to test this and if this works, that would be awesome.
    Support couldnt come up with this I even asked them if we could do a custom ticket to resolve this.

    I can see this working on ticket that is already in the queue and the submitter goes and attaches a file. But what would happen if this is a new ticket that is being submitted and the submitter attaches the a file, if we set the status to ticket updated that ticket will not be a new ticket. Correct?

    I want to make sure a new ticket is not set to "User Responded"
    • Good thought. According to my tests, if an attachment happens when the ticket is created then the change description includes both actions in one row (E.G. "Ticket Created
      Added attachment "image.png""). So one change to the query should do it: add a wildcard before 'added' as well, so it's '%Added attachment%'
      • Jason - I finally got around to testing this and somehow its not working for me. When an attachment is added the status does not update unless the end user adds a comment. I also keep getting an error on the sql: 04/16/2017 21:45:49> Starting: 04/16/2017 21:45:49 04/16/2017 21:45:49> Executing Select Query... 04/16/2017 21:45:49> mysqli error: [1054: Unknown column 'HD_TICKET.ID' in 'where clause'] in EXECUTE("select HD_TICKET_ID from HD_TICKET_CHANGE where DESCRIPTION like '%Added attachment%' and (HD_TICKET.ID = 245864) ").

        please let me know if you have any other suggestions.
      • I forgot to alias "HD_TICKET_ID" as "ID" since the UPDATE query looks for a column named "ID" when it substitutes <TICKET_IDS>. I edited my original answer to reflect the two changes we've talked about.
      • Still getting the error. 04/17/2017 13:35:13> Starting: 04/17/2017 13:35:13 04/17/2017 13:35:13> Executing Select Query... 04/17/2017 13:35:13> mysqli error: [1054: Unknown column 'HD_TICKET.ID' in 'where clause'] in EXECUTE("select HD_TICKET_ID AS ID from HD_TICKET_CHANGE where DESCRIPTION like '%Added attachment%' and (HD_TICKET.ID = 245864) ")
      • make the last line of update "WHERE ID in (<TICKET_IDS>)"
      • Jason - could this be something in the select query? Cause even if I take the update query an just set it to email me, it still fails with the same sql error.
      • You are correct: the problem was in the select statement. I neglected to join the HD_TICKET table so that "HD_TICKET.ID" could be added by the CTR (KACE adds logic to the where clause so it doesn't search through every single ticket).
      • It was still failing with errors but you gave me some ideas and kind of combined yours with another rule I had and now it semi works but not fully. It atleaset send the email but its not updating the status.

        Here is what I put together.

        select HD_TICKET_CHANGE.ID from
        HD_TICKET JOIN HD_TICKET_CHANGE ON HD_TICKET.ID=HD_TICKET_ID and HD_TICKET_CHANGE.ID=<CHANGE_ID>
        where DESCRIPTION like '%Added attachment%'


        update HD_TICKET_CHANGE
        SET STATUS_ID = [User Responded]
        WHERE HD_TICKET_CHANGE.ID in (<TICKET_IDS>)
      • The only thing I cannot get to work now is to change the status but I can settle for notifying the owner. If you can help me adding the ticket owner email to this rule please.
        select HD_TICKET_CHANGE.ID from
        HD_TICKET JOIN HD_TICKET_CHANGE ON HD_TICKET.ID=HD_TICKET_ID and HD_TICKET_CHANGE.ID=<CHANGE_ID>
        where DESCRIPTION like '%Added attachment%'

        Thank you.
      • This will make the variable $EMAIL the owner's email address:
        select HD_TICKET_ID as ID,
        USER.EMAIL as EMAIL
        from HD_TICKET_CHANGE
        join HD_TICKET on HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
        join USER on USER.ID = HD_TICKET.OWNER_ID
        where DESCRIPTION like '%Added attachment%'
        and HD_TICKET_CHANGE.ID=<CHANGE_ID>
      • Jason thank you for all your help. With few modifications with your code I was able to get it at least to send the owner an email notification. I wish I could change the status to User Responded.

        select HD_TICKET_ID as TICKNUM,
        HD_TICKET.TITLE,
        OWNER.EMAIL OWNER_EMAIL
        from HD_TICKET_CHANGE
        join HD_TICKET on HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
        join USER OWNER ON OWNER_ID=OWNER.ID
        where DESCRIPTION like '%Added attachment%'
        and HD_TICKET_CHANGE.ID=<CHANGE_ID>
      • You'll need to find the actual STATUS_ID value of "User Responded", you can't set it by the field name. Find out the STATUS_ID by running: SELECT * FROM HD_STATUS
Please log in to comment

Answers

1
I've received a few complaints from customers about this bug as well. 
Answered 03/18/2017 by: tramiksim
Third Degree Blue Belt

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