KACE Product Support

Hi,

I have been working with Kace tech support on a help desk rule that auto generates child tickets when a specific catagory (New Hire) is specified on ticket-save, and then another ticket that changes the auto generated tickets to child tickets. Gerald created a FAQ. I was hoping on getting some insight on how the rules work. Could someone with sql experience help me break it down. I haven't been able to get it to work yet. I'm getting a syntax error on the line that has "and C.ID= <CHANGE_ID> " , if I take that part out of the line, the query will run without error.

Thanks,
JG

Overview
Rule 1: Create the Tickets
Rule 2: Link the tickets as children to the parent
Notes
WARNING: Always review "Precautions when creating ticket rules" before writing any rules.
NOTE: You may need to also read "Importing a Custom Ticket Rule"

Overview
In the KBOX it is possible to designate a ticket as a parent and then from the parent to create a child ticket. Alternatively you can designate the parent of any child from within a child ticket. The problem comes when you have a common process that you want to perform over and over again and want to create multiple children -- the problem is that the creation of these children can be tedious.
With ticket rules we can automate the creation of Child tickets if we know all of the following:

The designated submitter of the child tickets (must be in a submitter label for the queue)
There has to be a queue owner with an email address that matches the email address of the queue (e.g. helpdesk@kbox.company.com)
You need to know the title of each child ticket. Optionally you can set status, category, etc, but title is required
The combination of a specific category and the designation of a ticket to become a parent will fire the rule

Rule 1: Create the Tickets
The KBOX will allow the creation of ticket by email. Basically this rule will get all the required data and create the chlid tickets by emailing itself once for each child ticket. To prevent an email loop the query is constructed in a special way to enforce the pre-requisites.
In this example, the child tickets have a designated title and category. Looking at the query you will see a portion that reads:

select 'Create New VPN Account' TITLE, 'VPN Account' CATEGORY UNION ALL
select 'Assign Phone Extension' TITLE, 'Phone' CATEGORY UNION ALL
select 'Image and deliver Laptop' TITLE, 'Hardware' CATEGORY This will be the title and category for each ticket, respectively. The category values must exist in your queue or they will end up with a default category.

Also in this example, the category must be set to "New Hire Process". The category can be set at the same time or prior to designating the parent ticket as a parent. If you designate it as a parent and then change the category later the children will not be created.


Frequency: On Ticket Save
Select Query: Select HD_TICKET.ID,
OPROXY.EMAIL QUEUEMAIL,
CHILDREN.TITLE CHILDTITLE,
CHILDREN.CATEGORY,
SPROXY.USER_NAME SUBMITTER
FROM HD_TICKET
JOIN HD_QUEUE Q ON Q.ID=HD_TICKET.HD_QUEUE_ID
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID= <CHANGE_ID>

JOIN HD_CATEGORY CAT ON CAT.ID=HD_TICKET.HD_CATEGORY_ID
JOIN HD_STATUS S ON S.ID=HD_TICKET.HD_STATUS_ID
JOIN HD_PRIORITY P ON P.ID=HD_TICKET.HD_PRIORITY_ID
JOIN HD_IMPACT I ON I.ID=HD_TICKET.HD_IMPACT_ID
JOIN (
/* set desired child ticket values */
select 'Create New VPN Account' TITLE, 'VPN Account' CATEGORY UNION ALL
select 'Assign Phone Extension' TITLE, 'Phone' CATEGORY UNION ALL
select 'Image and deliver Laptop' TITLE, 'Hardware' CATEGORY
) CHILDREN
LEFT JOIN HD_TICKET_CHANGE_FIELD F ON
F.HD_TICKET_CHANGE_ID=C.ID and FIELD_CHANGED ='IS_PARENT'
LEFT JOIN USER UO ON UO.ID=HD_TICKET.OWNER_ID
LEFT JOIN USER US ON US.ID=HD_TICKET.SUBMITTER_ID
LEFT JOIN USER UA ON UA.ID=HD_TICKET.APPROVER_ID
LEFT JOIN USER UU ON UU.ID=C.USER_ID
LEFT JOIN MACHINE M ON M.ID=HD_TICKET.MACHINE_ID
/* verify that submitter is a valid submitter*/
LEFT JOIN HD_QUEUE_SUBMITTER_LABEL_JT SLJ ON Q.ID=SLJ.HD_QUEUE_ID
LEFT JOIN LABEL SL ON SL.ID=SLJ.LABEL_ID
LEFT JOIN USER_LABEL_JT UL ON UL.LABEL_ID=SL.ID
LEFT JOIN USER SPROXY ON SPROXY.ID=UL.USER_ID
/* verify that owner is a valid owner */
LEFT JOIN HD_QUEUE_OWNER_LABEL_JT OLJ ON Q.ID=OLJ.HD_QUEUE_ID
LEFT JOIN LABEL OL ON OL.ID=OLJ.LABEL_ID
LEFT JOIN USER_LABEL_JT UL2 ON UL2.LABEL_ID=OL.ID
LEFT JOIN USER OPROXY ON OPROXY.ID=UL2.USER_ID
WHERE 1=1 and F.AFTER_VALUE=1
and LOCATE(Q.EMAIL_USER,SPROXY.USER_NAME)>0
and Q.ALT_EMAIL_ADDR <> SPROXY.EMAIL
and SPROXY.USER_NAME <> OPROXY.USER_NAME
and SPROXY.USER_NAME='user8' /* <<< designate usernameof submitter */
and SL.NAME='submitters8' /* <<< designate label that submitter is in */
and OPROXY.USER_NAME='helpdesk_8@IL-122.corp.kace.com'
/* ^^^ designate username of proxy owner */
and OL.NAME='owners8' /* <<< designate label that proxy owner is in */
and CAT.NAME='New Hire Process' /*<<< designate category name */
Query result by email: Optional (recommended for testing)
Comments In Ticket: Not recommend since multiple children will cause mutliple comments -- one for each child
Send Email for Each Result Row: EMAIL COLUMN:QUEUEMAIL
SUBJECT:$childtitle (Parent:$id)
BODY:@Submitter=$submitter
@Category=$category
----------------------
@Parent_Ticket=$id
remainder of body text
Update Query: not used


Rule 2: Link the tickets as children to the parent
Rule 1 takes advantage of the fact that email will create tickets and email tokens will set values. However, there is no email token to represent the designation of the parent ticket number (yet) so this rule takes over that short-coming

Frequency: On Ticket Save
Select Query: Select C.ID FROM HD_TICKET
JOIN HD_QUEUE Q ON Q.ID=HD_TICKET.HD_QUEUE_ID
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>

JOIN HD_CATEGORY CAT ON CAT.ID=HD_TICKET.HD_CATEGORY_ID
JOIN HD_STATUS S ON S.ID=HD_TICKET.HD_STATUS_ID
JOIN HD_PRIORITY P ON P.ID=HD_TICKET.HD_PRIORITY_ID
JOIN HD_IMPACT I ON I.ID=HD_TICKET.HD_IMPACT_ID
LEFT JOIN HD_TICKET_CHANGE_FIELD F ON
F.HD_TICKET_CHANGE_ID=C.ID and FIELD_CHANGED ='COMMENT'
LEFT JOIN USER UO ON UO.ID=HD_TICKET.OWNER_ID
LEFT JOIN USER US ON US.ID=HD_TICKET.SUBMITTER_ID
LEFT JOIN USER UA ON UA.ID=HD_TICKET.APPROVER_ID
LEFT JOIN USER UU ON UU.ID=C.USER_ID
LEFT JOIN MACHINE M ON M.ID=HD_TICKET.MACHINE_ID
WHERE 1=1
and COMMENT LIKE '%@Parent_Ticket=%'
and VIA_EMAIL LIKE '%@%'
Query result by email: Recommended for testing
Comments In Ticket: not possible
Send Email for Each Result Row: not possible
Update Query: update HD_TICKET as T JOIN
HD_TICKET_CHANGE C ON C.HD_TICKET_ID=T.ID and C.ID=<TICKET_IDS>
set
T.PARENT_ID=CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(COMMENT,'@Parent_Ticket=',-1),'\n',1) AS UNSIGNED),
DESCRIPTION=TRIM( LEADING '\n' FROM
REPLACE(
CONCAT(DESCRIPTION, '\nParent Ticket Changed to "', SUBSTRING_INDEX(SUBSTRING_INDEX(COMMENT,'@Parent_Ticket=',-1), '\n',1),'".\n'),'\n\n','\n')
),
COMMENT=REPLACE(COMMENT,CONCAT('@Parent_Ticket=', SUBSTRING_INDEX( SUBSTRING_INDEX(COMMENT,'@Parent_Ticket=',-1),'\n',1),'\n'),')


Notes
If you don't see the children right away then reopen the ticket. This can happen because the ticket might get refreshed faster than all the child tickets get saved.
.


Last update: 2009-11-19 13:44
Author: Gerald Gillespie