Hello ITNinjas.

I am hoping someone can help me figure out why a custom rule is not working as expected: it is supposed to set the HD_TICKET.owner_id and HD_TICKET.submitter_id based on the text of the incoming email sent to our helpdesk (the email happens to be Google Apps for Business Password Changed Alert). 

  • I have the parsing correct (in that username for both the submitter and owner are correctly extracted), 
  • SELECT QUERY correctly returns the ticket ID in question, Executing Select Query... 02/04/2015 10:28:34> selected 1 rows
  • the UPDATE QUERY simply says Executing Update Query... 02/04/2015 10:28:36> updated 0 rows

The SELECT QUERY is this: 

    SELECT  HD_TICKET.ID
    FROM  HD_TICKET INNER JOIN 
                HD_TICKET_CHANGE ON HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID
    WHERE HD_TICKET_CHANGE.ID = < CHANGE_ID > AND 
                HD_TICKET_CHANGE.DESCRIPTION Like 'Ticket Created%' AND 
                HD_TICKET_CHANGE.USER_ID = 4470 AND 
                HD_TICKET_CHANGE.COMMENT LIKE 'Google Apps for Business%Password%has been changed%'

The UPDATE QUERY is this:

update HD_TICKET
    set HD_TICKET.OWNER_ID = 
    (
        SELECT ID FROM USER U1 
        WHERE U1.USER_NAME = 
        (
          SELECT
            CASE 
              WHEN HTC2.COMMENT LIKE '%(s@mydomain.tld)%' THEN 's'
              WHEN HTC2.COMMENT LIKE '%(a@mydomain.tld)%' THEN 'a'
              WHEN HTC2.COMMENT LIKE '%(b@mydomain.tld)%' THEN 'b'
              WHEN HTC2.COMMENT LIKE '%(c@mydomain.tld)%' THEN 'c'
              WHEN HTC2.COMMENT LIKE '%(d@mydomain.tld)%' THEN 'd'
              WHEN HTC2.COMMENT LIKE '%(e@mydomain.tld)%' THEN 'e'
              WHEN HTC2.COMMENT LIKE '%(f@mydomain.tld)%' THEN 'f'
              WHEN HTC2.COMMENT LIKE '%(g@mydomain.tld)%' THEN 'g'
              WHEN HTC2.COMMENT LIKE '%(h@mydomain.tld)%' THEN 'h'
              WHEN HTC2.COMMENT LIKE '%(i@mydomain.tld)%' THEN 'i'
              WHEN HTC2.COMMENT LIKE '%(j@mydomain.tld)%' THEN 'j'
              WHEN HTC2.COMMENT LIKE '%(k@mydomain.tld)%' THEN 'k'
              WHEN HTC2.COMMENT LIKE '%(l@mydomain.tld)%' THEN 'l'
              WHEN HTC2.COMMENT LIKE '%(m@mydomain.tld)%' THEN 'm'
              WHEN HTC2.COMMENT LIKE '%(n@mydomain.tld)%' THEN 'n'
              WHEN HTC2.COMMENT LIKE '%(o@mydomain.tld)%' THEN 'o'
              WHEN HTC2.COMMENT LIKE '%(p@mydomain.tld)%' THEN 'p'
              WHEN HTC2.COMMENT LIKE '%(q@mydomain.tld)%' THEN 'q'
              WHEN HTC2.COMMENT LIKE '%(r@mydomain.tld)%' THEN 'r'
              ELSE ''
            END AS OWNER1
          FROM HD_TICKET_CHANGE HTC2
          WHERE HTC2.HD_TICKET_ID = HD_TICKET.ID AND HTC2.COMMENT LIKE 'Google Apps for Business%Password%has been changed%'
        )
    ),
    HD_TICKET.SUBMITTER_ID = 
    (
        SELECT ID FROM USER U2 
        WHERE U2.USER_NAME = 
        (
          SELECT                 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(HTC3.COMMENT,'@',2),'@',1),LOCATE('user',SUBSTRING_INDEX(SUBSTRING_INDEX(HTC3.COMMENT,'@',2),'@',1))+5) AS SUBMITTER1
          FROM HD_TICKET_CHANGE HTC3 
          WHERE HTC3.HD_TICKET_ID = HD_TICKET.ID AND HTC3.COMMENT LIKE 'Google Apps for Business%Password%has been changed%'
        )
    )
  where 
      HD_TICKET.ID in (< TICKET_IDS >)


I have tried it as a On Ticket Save rule (with HD_TICKET_CHANGE.ID = , and as a On Demand/Scheduled rule where I simply select all matching tickets (no ). Both ways, the SELECT query returns the correct ticket ID(s), but the UPDATE query executes but states "0 rows updated". 

NOTE: < CHANGE_ID > and < TICKET_IDS > above have extra spaces between < > because the tokens seem to get stripped from the question when typed in directly; in the actual rule the two tokens are correct. 


What am I missing?

2 Comments   [ + ] Show Comments

Comments

  • Since it is possible there may be more than one entry in HD_TICKET_CHANGE for each ticket you may not be able to just query that table for matching HD_TICKET_ID. If you also look for the first change to the ticket using MIN(HD_TICKET_CHANGE.ID) you will get the first ticket change on the ticket which should include the text you are looking to find.

    For your first U1.USER_NAME would it be better to use a left function instead of all of those case statements?
  • Thank you Chuck. I thought that CHANGE_ID gives you the latest change, and I assumed from that the first ON SAVE event would have to give me the change I am looking for. I will keep that in mind in the future.

    The rule started working just fine, shortly after I posted my question--I think I had a double space somewhere in the update query which was making it fail.

    Thanks for the MIN() tip... it sounds obvious now that I think of it, but I sure never thought of it before :-)

    The CASE statement is there because I was lazy when I first started on this rule, I could probably replace it with a combination of SUBSTRING/SUBSTRING_INDEX but it works now. I'll fix it one of these days--I should also make it work from a list of approved ticket owners so I don't have to change it every time a new hire comes in.

    Regards.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity

Share