KACE Product Support

Context:  I want people to be able to submit a process, but don't want them to be submitters in the queue for the process ticket to be able to see this parent ticket after it is submitted.  I need this ticket queue to be the parent ticket for the information that is needed, but any update can be seen on a parent ticket.  This process creates a 6th ticket in the main queue that they WOULD be able to see.  I want this sixth ticket to become the parent ticket for the other 5 (for the sake of SEE ALL CHILD HISTORY at the bottom of the ticket).

I've got the select query which finds the tickets once they are created, but it needs further modification to only find tickets that are just made in that process (I can restrict it to JUST that original queue but then the update still needs to see queue id = 1:

 select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.PARENT_ID,
HD_TICKET.TITLE,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME, 
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
'@dorfman-pacific.com' as EMAIL, 
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
COMMENT
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
  and HD_TICKET_CHANGE.ID=<CHANGE_ID>
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_CHANGE.USER_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_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%'

Then comes the trouble part; the update query.  The LAST ticket made in the process will always be the one I want to be the parent, and unless a new ticket is submitted at the exact instance the process is, the last ticket number is the one I want. 

Update query i need help making:

 
update HD_TICKET
join (Select MAX(HD_TICKET.ID) as MAX_ID from HD_TICKET)
set HD_TICKET.PARENT_ID = MAX_ID

  where 
        (HD_TICKET.ID in (<TICKET_IDS>))

Basically all the tickets just made should be set to the HIGHEST ticket ID available, which would be the Service Desk ticket, making it the parent_id.

NOTE:  I've already got a rule that AUTOMATICALLY sets IS_PARENT = '1' for all tickets (as I think there should be a DEFAULT option for this anyways and don't want to have to see a ticket, go allow another ticket to be a parent, then go back to the original ticket to assign it's parent, derr).

That's the general gist of it, but let me know if you have had any luck with this or experience or can throw your expertise at it (or if there is just an easier way)!