/build/static/layout/Breadcrumb_cap_w.png

Help with KACE Help Desk Queue Custom Rule (Set Owner and Submitter ID based on incoming email text)

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
  • 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? - chucksteel 9 years ago
  • 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. - merklo 9 years ago

Answers (0)

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ