/build/static/layout/Breadcrumb_cap_w.png

Help with Custom SQL for ticket rule

I need to have custom ticket rule that gets the title of parent ticket and appends it to the child ticket based on the category of the ticket upon save..


Example...


If parent ticket title says "New Hire Some guys name blah blah" then if the child ticket has category of "Phone Ext" then take the title from the parent and add "-Phone" to it.. so the child title will be "New Hire Some guys name blah blah-Phone" or "Phone-New Hire Some guys name blah blah" either way would work...


I have tried some of what I found at KACE Product Support : Have K1000 use the same title for all tickets in a process (itninja.com) but even the select query 

SELECT
HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.HD_PRIORITY_ID,
HD_TICKET.HD_IMPACT_ID,
HD_TICKET.OWNER_ID,
HD_TICKET.HD_CATEGORY_ID,
HD_TICKET.CUSTOM_FIELD_VALUE0,
HD_TICKET.MACHINE_ID,
HD_TICKET.PARENT_ID
FROM
HD_TICKET PARENT, HD_TICKET
WHERE
PARENT.ID = HD_TICKET.PARENT_ID


Does not show any results, even though there is parent & child tickets in the queue


Any assistance will be greatly appreciated..


Thanks


Jason


0 Comments   [ + ] Show comments

Answers (3)

Posted by: Hobbsy 2 years ago
Red Belt
0

You could create a ticket rule that just runs a concatenate and joins the fields together, something like, if the second value is Custom field 1?


SET

HD_TICKET.TITLE = CONCAT((SELECT HD_TICKET.TITLE),

HD_TICKET.CUSTOM_FIELD_VALUE0) 


WHERE(HD_TICKET.HD_STATUS_ID = 63)

      AND (HD_TICKET.HD_QUEUE_ID = 9)


You will just need to set the WHERE statement to only run specifically on the child tickets that you want to update

Posted by: lee@spica.one 2 years ago
White Belt
0

Jason

You should get some results from your query if there are any parent/child tickets.

Try running the following to list all parent tickets in your Kace DB:

SELECT ID, TITLE

FROM HD_TICKET

WHERE IS_PARENT = 1


If you get none, there's a different issue going on.


Comments:
  • If I try the above code in a Queue that I know there is a parent there.. and I click "View Ticket search results" I get this error
    "There was an error retrieving the data for this page.

    Please refresh the page. If the error persists, try resetting the default page layout." - jct134 2 years ago
    • Jason

      The "View Ticket search results" is broken in my environment (11.0.273). All of my working scripts show the same error.

      As an alternative you could check the "email results" and have the rule send you the results in email.

      I use MySQL workbench to test my scripts against a test environment. Works for select but not update queries. - lee@spica.one 2 years ago
Posted by: jct134 2 years ago
Senior Purple Belt
0

What I ended up doing....


The reason I originally wanted to take the title form the parent ticket and append info for the child tickets.. is 


When you create a process that automatically creates the parent & child tickets you CAN NOT copy the title & Summary from the parent upon creation unless they are in the same queue... so this is why I was trying to do it this way...


However.. what I found to work better was:


1. Create the process with all 4 (1 parent & 3 child) tickets all in the same queue.. & copy the title & summary as well as set the categories for each child

2. custom rule to move the parent ticket to a separate parent queue.. (will close automatically when last child is closed)

3. 3 custom rules that check the category, and appends the title with extra text based upon the category




Comments:
  • Interesting.

    I have many rules that trigger for my new hire process, some of which do what you actual ask. Including
    * adding and updating the new hire's name to each child ticket title (helps when there are a lot of hires)
    * Updating the category when a manager submits with less than approved notice (short notice hires)
    * Setting due dates based on the hire date field

    So what you're asking is definitely possible. - lee@spica.one 2 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

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