Valid for K1 Appliances Versions 5.3.*

Hello Boxers,

If you use Processes in your Help Desk module of the k1000 then you probably experience a situation where you would have liked manually entered information from the Parent Ticket to be passed down the Child Tickets. 

An Example of this could be a Process for a New Employee. Since each employee as unique information (Names, Badge Numbers, Employee IDs and example) you would have to fill this in into each child ticket manually. However there is a way to automate this using a Custom Ticket Rule and a little bit of SQL.

We will go ahead and use the above scenario as the basis for this post so we will pull data from a parent ticket down to a child ticket in a process that is all about setting up an Employee Account. Our goal will be to pull the data from 3 custom fields down to the child ticket.

Keep in mind that when using SQL it is always best to test them out before applying them to your production environment. KACE Technical Support nor IT Ninja can be held responsible if any damage occurs from using the following SQL. 

Now let's get down to business. Here is a Screen Cap of my Custom Ticket Rule. You can Create CTRs by going to Help Desk -> Configuration -> Queue -> QUEUE_NAME_HERE -> Scroll down to buttom and click "Custom Ticket Rules:[Customize]" 

You will need to Create a CTR using the Drop Down menus but it does not matter what you use as we will be overwriting it.

Note: It is important to have a Frequency of "On Ticket Save"

Here are the queries so you can copy and paste them:

Select Query:

SELECT
PARENT.CUSTOM_FIELD_VALUE0,
HD_TICKET.TITLE,
HD_TICKET.ID
FROM
HD_TICKET PARENT, HD_TICKET
WHERE
PARENT.ID = HD_TICKET.PARENT_ID
AND HD_TICKET.RESOLUTION not like '%Fields Pulled From Parent Ticket%'

Update Query:

UPDATE
HD_TICKET PARENT,
HD_TICKET
SET
HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0,
HD_TICKET.CUSTOM_FIELD_VALUE1 = PARENT.CUSTOM_FIELD_VALUE1,
HD_TICKET.CUSTOM_FIELD_VALUE2 = PARENT.CUSTOM_FIELD_VALUE2,
HD_TICKET.RESOLUTION = CONCAT(HD_TICKET.RESOLUTION,' Fields Pulled From Parent Ticket.')
WHERE
((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)

First thing you should notice is that the Update Query is using the fields CUSTOM_FIELD_VALUE followed by a number. These are the names for the custom fields in the SQL Database the K1 appliance uses. These fields will not be on by default. You can enable these fields through the specific Queue's configuration page. 

I went ahead and made these fields to be used for Name, ID, and Password for the new employee. Here is a screenshot of a blank ticket for an example.

We are going to use these 3 fields as the data we will want to pull from the Parent ticket down to the Child ticket; However, you can transfer the data any field possible (title, owner, priority, even a comment) if you wanted.

In my above screen shot 'Name' has the Database field name of CUSTOM_FIELD_VALUE0.

'ID' has the name of CUSTOM_FIELD_VALUE1.

'Password' has the name of CUSTOM_FIELD_VALUE2.

Here is the logic behind the Ticket Rule:

1. Look for a ticket that has as Parent Ticket and that has not yet been ran through this Ticket Rule:

WHERE

PARENT.ID = HD_TICKET.PARENT_ID     <---------- Has Parent Ticket 
AND HD_TICKET.RESOLUTION not like '%Fields Pulled From Parent Ticket%' <----------  Checks Resolution Field to see if the CTR ran for this ticket already.

2. Grab data from the fields of the parent ticket and pass them to the child

SET
HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0, <---------- Passes the 'Name'
HD_TICKET.CUSTOM_FIELD_VALUE1 = PARENT.CUSTOM_FIELD_VALUE1, <---------- Passes the 'ID'
HD_TICKET.CUSTOM_FIELD_VALUE2 = PARENT.CUSTOM_FIELD_VALUE2, <---------- Passes the 'Password'
HD_TICKET.RESOLUTION = CONCAT(HD_TICKET.RESOLUTION,' Fields Pulled From Parent Ticket.') <---------- Marks the ticket letting us know that it was updated by this CTR.

So any time a Parent Ticket of a process is saved it will automatically pass these fields down to the child tickets without the need to manually add them. This is extremely helpful if each child ticket is meant to be handled by different individuals or even seperate queues altogether!

Adding Data from Parent Ticket to the Child Ticket's Title.

Here is another example of a CTR to pass information from the Parent ticket down to the child ticket. The following CTR will grab the same "Name" field we used above ( CUSTOM_FIELD_VALUE0 ) and then appends it to the Title of the child ticket. So not only are we pulling data from the Parent Ticket, but we are now adding it to a completely different field of HD_TICKET.TITLE which is where the Title of the ticket is stored in the Database.

SELECT
PARENT.CUSTOM_FIELD_VALUE0,
HD_TICKET.TITLE,
HD_TICKET.ID
FROM
HD_TICKET PARENT, HD_TICKET
WHERE
PARENT.ID = HD_TICKET.PARENT_ID
AND HD_TICKET.RESOLUTION not like '% Name added to title.%'

UPDATE
HD_TICKET PARENT,
HD_TICKET
SET
HD_TICKET.TITLE = CONCAT(HD_TICKET.TITLE," ",PARENT.CUSTOM_FIELD_VALUE0),
HD_TICKET.RESOLUTION = CONCAT(HD_TICKET.RESOLUTION,' Name added to title.')
WHERE
((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)

So if we had a Parent ticket with the custome "Name" field filled with "Steven" it will then add "Steven" the Title of the Child ticket! Keep in mind this will not replace the title altogether but simply add it to the end.