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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
If you're trying to run that query in the MySQL Query Browser, then it is erroring at <CHANGE_ID> because that is a variable within the KBOX. The same is true with <TICKET_IDS> - which is an array of ticket IDs resulting from the select query.
Answered 11/24/2009 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
that makes sense, can you test these ticket rules in your environment and see if you can get it to work?
Answered 11/25/2009 by: jg1000c
Orange Belt

Please log in to comment
0
I may be able to in a week or two, but we don't currently have a development environment setup. To get a speedy resolution, reply to your ticket to KACE telling them any issues you're having with the query, and I'm sure Gerald will look it over and adjust it accordingly.
Answered 11/25/2009 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
jg1000c and I figured it out. With rules that have a frequency of "on ticket save" you should not use the "run now" button -- you could wind up modifying many tickets instead of a single ticket. This is one reason I like the 5.0 syntax using <CHANGE_ID> which forces the query to only be able to pass when something changes on ticket save.

The hard part comes in testing. Sometimes you just want to take this query, run it in mysql query browser and see what you get, but since <CHANGE_ID> has no meaning in MySQL query browser it fails. So what I do in testing is:

1) make a change in my ticket that i would expect to pass
2) run this query to get the 10 most recent ticket changes in the entire database
SELECT * FROM HD_TICKET_CHANGE ORDER BY ID DESC LIMIT 10
3) examining them I can quickly tell which row corresponds to my change in step 1.
4) read the ID column and substitute it for <CHANGE_ID>
5) run the query
Answered 11/25/2009 by: GillySpy
Seventh Degree Black Belt

  • I still have not gotten it to work. My assumption here is that i missed something easy. I added the 4 required items from this example (VPN Account, Phone, Hardware, New Hire Process) to CATEGORY. I created the 2 ticket rules using the defined queries and changed the values related to the owner verification.

    Also I'm seeking some clarifications:
    1. Am I creating the ticket using the UI or via email?
    2. why am I designating a submitter and owner (the SL, OL, SProxy and OProxy fields) in my rule 1 query? Shouldn't it take it from ticket values?
    3. Why were the items added into the category field along with New Hire Process rather than a custom field?
    4. Expanding on question 3, could this be applied to a Multi-Select custom field and then dynamically create only the child tickets from the selected values?
Please log in to comment
Answer this question or Comment on this question for clarity