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
Answers (3)
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
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 3 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 3 years ago
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 3 years ago