/build/static/layout/Breadcrumb_cap_w.png

K1000 Helpdesk Rule that changes queue and owner

I am trying to create a rule that will move a ticket to another queue if it has a keyword in the ticket and then assign it to a person in that queue. I created a rule that moved it to another queue, however the other queue that has a rule to auto assign the ticket does does not find the ticket and I am not sure why as it is the same rule except for a different queue.

My goal is to have the ticket auto move the ticket to another queue (to queue 7) and assign it to an owner if it has particular keywords in it. Maybe I need a different work flow but any pointers would be greatly appreciated! Lacking in SQL right now but just started taking a course in it so hopefully I will be better in a couple months.


Queue 6 Rule
select HD_TICKET.*,
                        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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
                        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(HD_TICKET.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 = 'New') AND HD_TICKET.OWNER_ID = '0') AND ((((((((HD_TICKET.SUMMARY like '%stat%') OR HD_TICKET.TITLE like '%stat%') OR HD_TICKET.SUMMARY like '%report%') OR HD_TICKET.TITLE like '%report%') OR HD_TICKET.SUMMARY like '%fingerprint%') OR HD_TICKET.TITLE like '%fingerprint%') OR HD_TICKET.SUMMARY like '%dispo%') OR HD_TICKET.TITLE like '%dispo%') and HD_TICKET.HD_QUEUE_ID = 6 )





I can get the update portion to work as separate ones but I do not know how to make it into one, here are the two separate ones
UPDATE HD_TICKET, HD_CATEGORY
SET HD_TICKET.HD_QUEUE_ID = 7
WHERE HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
AND HD_TICKET.ID = <TICKET_IDS>

update HD_TICKET, USER as T5
    set HD_TICKET.OWNER_ID = T5.ID
  where T5.USER_NAME = 'username' and 
        (HD_TICKET.ID in (<TICKET_IDS>))


Rule is set to run on Save

Then rule on queue 7 is (only thing that changed from above is the queue number)
select HD_TICKET.*,
                        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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
                        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(HD_TICKET.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 = 'New') AND HD_TICKET.OWNER_ID = '0') AND ((((((((HD_TICKET.SUMMARY like '%stat%') OR HD_TICKET.TITLE like '%stat%') OR HD_TICKET.SUMMARY like '%report%') OR HD_TICKET.TITLE like '%report%') OR HD_TICKET.SUMMARY like '%fingerprint%') OR HD_TICKET.TITLE like '%fingerprint%') OR HD_TICKET.SUMMARY like '%dispo%') OR HD_TICKET.TITLE like '%dispo%') and HD_TICKET.HD_QUEUE_ID = 7 )




Update:
update HD_TICKET, USER as T5
    set HD_TICKET.OWNER_ID = T5.ID
  where T5.USER_NAME = 'username' and 
        (HD_TICKET.ID in (<TICKET_IDS>))

Asked 6 years ago 779 views
6 Comments   [ + ] Show comments
  • Does the rule in queue 7 indicate that it ran? There should be a log at the bottom of the rule's configuration page. - chucksteel 6 years ago
  • It does. Here is the result:
    10/13/2017 10:15:01> Starting: 10/13/2017 10:15:01 10/13/2017 10:15:01> Executing Select Query... 10/13/2017 10:15:01> selected 0 rows



    I was thinking that maybe it was not finding the ticket when it was moved from one queue to another queue with the on save portion, but it does not find it manually either. I'm not sure why moving the ticket from one queue to another queue makes the same query not work...

    Actually just realized when I click on View ticket search results I get this message:
    There was an error retrieving the data for this page.
    Please refresh the page. If the error persists, try resetting the default page layout. - cgsao 6 years ago
  • Oops, forgot to put this at the end.

    It doesn't work after I reset the default view either. I'll see if I can find anything for that in IT Ninja. - cgsao 6 years ago
  • I actually just realized what is going on now but I am not quite sure how to fix it. When the ticket gets moved from one queue to another (both queues defaults are 'Other') the ticket has a blank category and not the default. - cgsao 6 years ago
    • Well, since the ticket's category isn't relevant, you can remove HD_CATEGORY from this line:
      from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)

      (don't forget to delete the comma before it)
      and then remove this line:
      and HD_CATEGORY.ID = HD_CATEGORY_ID - chucksteel 6 years ago
      • Removed it and sent a test ticket in. It moved queues and now took down the ticket system so I will have to call support. I guess I should have made sure I could see the ticket before testing the rule. - cgsao 6 years ago
      • Wow, that's crazy. Did you get the appliance back up? - chucksteel 6 years ago
  • chucksteel,
    Would you happen to know of a way to run the update query where it will move the ticket to another queue and also change the category to 'Other' that is in the new queue? - cgsao 6 years ago
    • The easiest way to do it would be to get the ID of the category and set it in the update statement. If you don't have something like MySQL Workbench setup to query the database you can create a report with the following statement:
      SELECT * FROM HD_CATEGORY

      That will give you a list of all of your categories and their ID. Keep in mind that categories are specific to queue's, so make sure you identify the "Other" category in queue 7. Your update statement in queue 6 should then be:
      UPDATE HD_TICKET
      SET HD_TICKET.HD_QUEUE_ID = 7,
      HD_TICKET.HD_CATEGORY_ID = [number]
      AND HD_TICKET.ID = <TICKET_IDS>

      Where [number] is the new category ID. - chucksteel 6 years ago
      • Additionally, you could add a join to the category table in the update and add WHERE HD_CATEGORY.NAME = 'Other' to automatically map the ID. Like so:

        UPDATE HD_TICKET
        JOIN HD_CATEGORY
        SET HD_TICKET.HD_QUEUE_ID = 7,
        HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID
        WHERE HD_CATEGORY.NAME = 'Other' AND HD_TICKET.ID = <TICKET_IDS> - airwolf 6 years ago
  • Thanks for the tip. After all said and done, we scrapped the different queues and made it into a single queue so that we can run the rules we want. - cgsao 6 years ago

Answers (0)

Be the first to answer this question

Posted by:

cgsao Yellow Belt
Ninja since: 6 years ago

Don't be a Stranger!

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

Sign up! or login

View more:

Systems Management Questions
question

Related Questions

Link

Related Links

Post

Related Posts

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