KACE Product Support

I think my post may have been a little convoluted - I'm only aiming for the OTS rule. I did a manual (batch) version without the <TICKET_IDS> statement just to verify functionality.

Your last comment about needing to reference SOMETHING.ID in the select query was the key - IT IS WORKING NOW!!! So here it is in final form for anyone else who might find it useful - HUGE THANKS TO DCRHRISTIAN AND GILLYSPY!!! You guys make this forum a great resource and I truly appreciate it!

Thanks yet again to both of you!!! ^_^

John
___________________________________________________

Create this ticket rule in Queue 1 (IT Helpdesk)

Title:
Autopopulate Child Ticket Fields (Location)

Order:
12

Notes:
Copies specified data from Parent ticket (in queue 2) to Child ticket (in queue 1) on ticket save of Child ticket.

Frequency:
on Ticket Save
____________________________________________________________________

Select Query:
SELECT PARENT.CUSTOM_FIELD_VALUE0 AS 'Parent Location', HD_TICKET.CUSTOM_FIELD_VALUE0 AS 'Child Location', HD_TICKET.ID AS "ID"
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID

X Run an update query, using the results from the one above

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)

____________________________________________________________________

* the following example works when frequency set to a time, but would have to run on a schedule, want to run on ticket save and only apply to saved ticket

* keeping this example to illustrate aliasing Parent & Child queues

Select Query:
SELECT PARENT.CUSTOM_FIELD_VALUE0 AS 'Parent Location', CHILD.CUSTOM_FIELD_VALUE0 AS 'Child Location'
FROM HD_TICKET PARENT, HD_TICKET CHILD
WHERE PARENT.ID = CHILD.PARENT_ID

X Run an update query, using the results from the one above

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET CHILD
SET CHILD.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE PARENT.ID = CHILD.PARENT_ID

* dchristian's example select query:
SELECT PARENT.TITLE, CHILD.TITLE
FROM HD_TICKET PARENT, HD_TICKET CHILD
WHERE PARENT.ID = CHILD.PARENT_ID
____________________________________________________________________

Per dchristian:

The child / parent works like this.
Each ticket is assigned an ID in the database.
The PARENT_ID field points back to the ID field (just on a different row).
Hope this helps, good luck your close.
____________________________________________________________________

Per GillySpy (on :

In an OTS rule you must have a table referenced as HD_TICKET.
You aliased it to PARENT so it won't work.
If you want this to be triggered by the parent then change all PARENT back to HD_TICKET.
If you want it triggered by the CHILD then change CHANGE back to HD_TICKET.
If you want it triggered by both then have two rules.
____________________________________________________________________

Here's the other post I keep referencing, and its code so you can see another example. Aside from the extended concat and where statements in the Update Query (which were used for something very specific - see the post for more details on that), it's the same as the other example here. Also, you can see I'm referencing the Title field in the Child ticket in this rule (i.e. HD_TICKET.TITLE), in case you thought this only worked for custom fields.

Again, ultimately, you need to figure out what your fields are called and creating a report is the easiest way to do this.

If you want a quick solution *now* and don't have the want/need to play with this, I would suggest calling support and asking for assistance from Kace professional services - but this is definitely something you can do on your own. ^_^

John
_________________________

http://itninja.com/question/performance-monitoring125&mpage=1&key=𔣀

Select Query:
SELECT PARENT.CUSTOM_FIELD_VALUE2 AS 'Parent New User Name', HD_TICKET.TITLE AS 'Child Title Field', HD_TICKET.ID AS 'ID'
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID

X Run an update query, using the results from the one above

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.TITLE = CONCAT(HD_TICKET.TITLE, ' (', PARENT.CUSTOM_FIELD_VALUE2, ')')
WHERE (((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.TITLE NOT RLIKE '^.+ [[.(.]].*') AND HD_TICKET.ID = <TICKET_IDS>)

It works! Thanks GillySpy and jverbosk. For reference, here is my Select Query:

SELECT PARENT.CUSTOM_FIELD_VALUE3, HD_TICKET.CUSTOM_FIELD_VALUE3, HD_TICKET.ID
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID

and my Update Query:

UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.CUSTOM_FIELD_VALUE3 = PARENT.CUSTOM_FIELD_VALUE3
WHERE ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)

Now I have to modify them to do 4 fields (I just did the one field for testing)

John,

Here is the Select Query for multiple fields:

SELECT PARENT.CUSTOM_FIELD_VALUE1, HD_TICKET.CUSTOM_FIELD_VALUE1, PARENT.CUSTOM_FIELD_VALUE2, HD_TICKET.CUSTOM_FIELD_VALUE2, PARENT.CUSTOM_FIELD_VALUE3, HD_TICKET.CUSTOM_FIELD_VALUE3,
PARENT.CUSTOM_FIELD_VALUE0, HD_TICKET.CUSTOM_FIELD_VALUE0, HD_TICKET.ID
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID

And Update Query:

UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.CUSTOM_FIELD_VALUE1 = PARENT.CUSTOM_FIELD_VALUE1,
HD_TICKET.CUSTOM_FIELD_VALUE2 = PARENT.CUSTOM_FIELD_VALUE2,
HD_TICKET.CUSTOM_FIELD_VALUE3 = PARENT.CUSTOM_FIELD_VALUE3,
HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)


Works like a champ. I couldn't have done it without your help or GillySpy.

Rob

You could have a ticket rule that does this, but the parent is linked from the child.

I am learning SQL statements so can you help me out with this? I don't see where I can do this using the ticket rule wizard.

Child tickets that go through a process should have a purple drop-down arrow where you can see contents of parent

The wizard does not have this capability. You would have to create a custom rule. I haven't done this before but I am thinking a rule running on save could detect the initial creation of the child ticket and pull down information from its parent. If you really want it done then services could be an option for you.

We are having the same issue and it should flow from the parent to the child. Even though there are different processes created to keep the workflow streamlined the persons name, contact info, location etc. should flow to the child ticket. We have made some of these fields required which makes each person that has a child ticket fill the info in each child ticket before closing

I would also like to be able to have data from parent tickets saved into the child tickets. Running v5.3.45497 on the kbox for reference.

I looked at variables, and the only ones I saw related to this were:

HD_TICKET.PARENT_ID - lists a ticket's parent ticket by ticket number
HD_TICKET.IS_PARENT - specifies whether a ticket is a parent ticket or not (0 - no, 1 - yes)

I saw some other posts that referenced variables like CHILDREN.TITLE and CHILDREN.PRIORITY, i.e.:
http://itninja.com/question/repackaging-process51&mpage=1&key=Parent%2Cchild𐻧

but looking using a SQL browser to check variables in the MySQL database I didn't see anything tagged "child" or "children". I liked GillySpy's idea in another post to detect the children tickets as they are being created and copy the specified data down to them as that happens, but since I'm unable to identify any code related to this process based on what I'm seeing here in this forum, on the Kace KB site and in the KBOX Reports & Custom Ticket Rules SQL code, that's not an option for me at this point. Probably due to my complete ignorance of SQL, I will admit.

Based on this, my idea is to have a rule run on ticket save (a save in the child ticket after it has been created) which pulls data from specified fields in that ticket's parent and sets the corresponding specified fields to match in the child ticket being saved. It is an extra step (going into the child ticket and hitting the "Save" button), but in my mind this would beat having to hop between tickets and copy/paste data or set fields to match.

My current setup has the parent ticket in Queue 2 and the child ticket in Queue 1. Therefore, my select query (being run on from the child ticket in queue #1) is:

SELECT HD_TICKET.ID AS ID, HD_TICKET.PARENT_ID AS PARENT FROM HD_TICKET
WHERE HD_TICKET.HD_QUEUE_ID = 1

With the update query being:

UPDATE HD_TICKET T
SET HD_TICKET.CUSTOM_FIELD_VALUE0 = HD_TICKET.CUSTOM_FIELD_VALUE0
WHERE ((HD_TICKET.HD_QUEUE_ID = 2) and (T.ID=<TICKET_IDS>)

However, I haven't tested this due the second line of the update query most likely causing a loop. I would like to be able to say:

SET HD_TICKET.QUEUE1.CUSTOM_FIELD_VALUE0 = HD_TICKET.QUEUE2.CUSTOM_FIELD_VALUE0
(in other words, copy Queue 2's data field and put it in Queue 1's field)

...but I'm not quite sure how to phrase this in SQL. Any suggestions or help would be great!

Thanks!!!

John

I did something like this for a client once.

From a high level in your update query you want to join HD_TICKET to itself.

Alias one as the parent and one as the child.

You can link them together by using PARENT_ID (from the child) and the ID (from the parent).

From here it should be easy to propagate your changes down.

David,

Thanks very much for your reply. I've been researching based on your suggestions and need some assistance - please excuse my ignorance, but I'm not getting very far with the generic SQL guides out there when trying to get a handle on things and this is all pretty new to me.

1) When you say "join HD_TICKET to itself", do you mean something like this? Is this so I can query both HD_TICKET and HD_QUEUE tables?

JOIN HD_TICKET Q ON HD_TICKET.HD_QUEUE_ID=HD_QUEUE.ID

2) How do I specify a particular queue in an alias? Would this be in the SELECT statement, like:

SELECT Q1.HD_QUEUE_ID AS Q1, Q2.HD_QUEUE_ID AS Q2, .... or
SELECT HD_QUEUE.1 AS CHILD, HD_QUEUE.2 AS PARENT, .... ?

I can see the ID field listed for HD_QUEUE in the MySQL Query Browser (downloaded it just for this), but am not sure how to alias something specific.

I am familiar with specifying in WHERE statements like this: WHERE ((LOCATION = 'Pittsburgh') and QUEUE = 2)

But at this point I'm confused and don't know how to proceed. My research on SQL aliases talked about aliasing tables and columns, but in general terms, not specifically (like alias Queue1 as Child).

3) I'm hopeful that I can figure the rest out if you don't mind clarifying or giving an example on the first two points. If you don't mind sharing the code from when you did this before (or at least the relevant parts), I would really appreciate it.

Thanks very much for your help!

John

Here's an example of doing this with a select.
SELECT PARENT.TITLE,
CHILD.TITLE
FROM HD_TICKET PARENT,
HD_TICKET CHILD
WHERE PARENT.ID = CHILD.PARENT_ID

David,

Thank you very much for your help!!!

I tried running your example through my MySQL Query Browser and it brought up the three test child tickets from my first queue. I'm honestly at a loss to how/where the PARENT and CHILD variables are being pulled from, since I'm not seeing them in the query browser, but I'm absolutely thrilled to be getting further thanks to your help!

I'll keep playing and see if I can do something with this, will post when I do (or if I get stuck again...).

John

David,

I tried creating an update query but didn't have any luck getting it to update. I believe I'm stuck at the JOIN statement and am not sure how to get the output from the Parent queue to copy into the Child queue's fields.

I adjusted your select query so it would refer to my location field (custom field 1 in the GUI):

SELECT PARENT.CUSTOM_FIELD_VALUE0 AS 'Parent Location', CHILD.CUSTOM_FIELD_VALUE0 AS 'Child Location'
FROM HD_TICKET PARENT, HD_TICKET CHILD
WHERE PARENT.ID = CHILD.PARENT_ID

This works just as your example above did in the MySQL Query Browser, and I have a better understanding of how to modify it now. I'm still not positive about the PARENT and CHILD variables, but after some searching I think they may just be native to MySQL syntax and not parts of the KBOX's database.

I tried a few different variations of the update query, none of which worked, but hopefully am getting warmer:

UPDATE HD_TICKET
JOIN CHILD C ON (PARENT.HD_TICKET = CHILD.HD_TICKET)
SET HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE HD_TICKET.ID=<TICKET_IDS>

UPDATE HD_TICKET
JOIN HD_TICKET Q ON (PARENT = CHILD)
SET HD_TICKET.CUSTOM_FIELD_VALUE0 = Q.PARENT
WHERE HD_TICKET.ID=<TICKET_IDS>

After multiple variations of the above, I've resigned myself to the fact that without knowing how to do the JOIN statement and knowing how to specify the queues, I'm not going to get very far.

Where I typically get an error when trying to run the rule is on line 4:

04:36> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,,,,,,' at line 4] in EXECUTE("UPDATE HD_TICKET
JOIN CHILD C ON (PARENT.HD_TICKET = CHILD.HD_TICKET)
SET HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE HD_TICKET.ID=,,,,,,,,")

So I'm wondering, maybe I need to specify the ticket ID in the select query in order for that line to process correctly?

Sorry for rambling, hope that makes sense.

Thanks again for your help!!!

John

I know you said I need to join the HD_TICKET to itself in the update query

Of course run at your own risk....

But its gonna look like this

UPDATE HD_TICKET PARENT,
HD_TICKET CHILD
SET CHILD.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE PARENT.ID = CHILD.PARENT_ID
AND CHILD.ID = <TICKET_IDS>

The child / parent works like this.

Each ticket is assigned an ID in the database.

The PARENT_ID field points back to the ID field (just on a different row).

Hope this helps, good luck your close.

David,

I'm definitely getting closer, but there's an error that comes up when the rule runs on ticket save that I'm not having any luck with.

For the record, when the following pair of queries are run on a schedule (or manually), the child/parent tickets are identified correctly and the specified field in the child ticket is updated with data from the parent ticket.

Select Query:
SELECT PARENT.CUSTOM_FIELD_VALUE0 AS 'Parent Location', CHILD.CUSTOM_FIELD_VALUE0 AS 'Child Location'
FROM HD_TICKET PARENT, HD_TICKET CHILD
WHERE PARENT.ID = CHILD.PARENT_ID

X Run an update query, using the results from the one above

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET CHILD
SET CHILD.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE PARENT.ID = CHILD.PARENT_ID

So that works perfectly, problem solved for updating child tickets with data from parent tickets on a schedule. Thank you!!! ^_^
_______________________________________________________________________________________

However, where I'm stuck is when the frequency is changed to "on Ticket Save" and the <TICKET_IDS> statement is added (i.e. what you wrote above):

UPDATE HD_TICKET PARENT, HD_TICKET CHILD
SET CHILD.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE PARENT.ID = CHILD.PARENT_ID AND CHILD.ID = <TICKET_IDS>

When this is done, regardless of what preceeds <TICKET_IDS>, I'm getting the following error in the Ticket Rule's Run Log screen:

14:02> Starting: Tue, 25 Oct 2011 09:14:02 -0400
14:02> Executing Select Query...
14:02> mysql error: [1054: Unknown column 'HD_TICKET.ID' in 'where clause'] in EXECUTE("SELECT PARENT.CUSTOM_FIELD_VALUE0 AS 'Parent Location', CHILD.CUSTOM_FIELD_VALUE0 AS 'Child Location'
FROM HD_TICKET PARENT, HD_TICKET CHILD
WHERE PARENT.ID = CHILD.PARENT_ID and (HD_TICKET.ID = 21989) ")

It's querying the correct ticket (i.e. the one that's being saved), but throws an "unknown column" error that I'm not having any luck with.

I've tried multiple variations in queries, such as adding CHILD.ID to the select query:

SELECT PARENT.CUSTOM_FIELD_VALUE0 AS 'Parent Location', CHILD.CUSTOM_FIELD_VALUE0 AS 'Child Location', CHILD.ID
FROM HD_TICKET PARENT, HD_TICKET CHILD
WHERE PARENT.ID = CHILD.PARENT_ID

...specifying HD_TICKET.ID in the WHERE statement of the update query:

UPDATE HD_TICKET PARENT, HD_TICKET CHILD
SET CHILD.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE PARENT.ID = CHILD.PARENT_ID AND HD_TICKET.ID = <TICKET_IDS>

...as well as lots of dropping/modifying statements, but I think I may be running into an issue with the way the <TICKET_IDS> statement works.

Per GillySpy - http://itninja.com/question/create-outlook-exchange-profiles07

"A rule with a frequency of On Ticket Save (OTS) will imply that the query of the rule will act ONLY on the ticket that is being saved. That means when the ticket "save" button is pressed or when an email is sent into the ticket. It does this by adding and HD_TICKET.ID = X to the select query at run time. With clever SQL you could circumvent this restriction, but it is inadvisable -- this is a safe guard that will facilitate your rules performance and ease of development. Take advantage of it. "

Based on that and the error in the log screen (Unknown column 'HD_TICKET.ID' in 'where clause'), it appears that the CHILD.ID = <TICKET_IDS> is being converted to HD_TICKET.ID = ticket# at runtime, and the rule isn't working since HD_TICKET.ID isn't being defined anywhere (possibly can't be, due to the PARENT and CHILD calls?). Adding HD_TICKET.ID to the select query generates an error (because it doesn't know which queue to pull from, I'm assuming), so I'm pretty stuck (again) at this point. Maybe <TICKET_IDS> can't be used here?

I could just run the rule on a schedule and be done with it, but I really would like to understand this as it appears to be something unique when dealing with parent/child queues.

Thanks again for all of your help!!!

John

ORIGINAL: jverbosk
14:02> Starting: Tue, 25 Oct 2011 09:14:02 -0400
14:02> Executing Select Query...
14:02> mysql error: [1054: Unknown column 'HD_TICKET.ID' in 'where clause'] in EXECUTE("SELECT PARENT.CUSTOM_FIELD_VALUE0 AS 'Parent Location', CHILD.CUSTOM_FIELD_VALUE0 AS 'Child Location'
FROM HD_TICKET PARENT, HD_TICKET CHILD
WHERE PARENT.ID = CHILD.PARENT_ID and (HD_TICKET.ID = 21989) ")

It's querying the correct ticket (i.e. the one that's being saved), but throws an "unknown column" error that I'm not having any luck with.

In an OTS rule you must have a table referenced as HD_TICKET. You aliased it to PARENT so it won't work. If you want this to be triggered by the parent then change all PARENT back to HD_TICKET. If you want it triggered by the CHILD then change CHANGE back to HD_TICKET. If you want it triggered by both then have two rules.

GillySpy,

Thanks very much for clarifying this!

I made changes to the queries as you recommended and tested by running manually without the OTS rule to verify things worked correctly:

Select Query:
SELECT PARENT.CUSTOM_FIELD_VALUE0 AS 'Parent Location', HD_TICKET.CUSTOM_FIELD_VALUE0 AS 'Child Location'
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID

X Run an update query, using the results from the one above

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE PARENT.ID = HD_TICKET.PARENT_ID

***************************************************

Everything worked fine as before (when run manually), so I changed the frequency to OTS and added the OTS statement to the Update Query like so:

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE PARENT.ID = HD_TICKET.PARENT_ID AND HD_TICKET.ID = <TICKET_IDS>

and also like this (with quotes around the WHERE parameters):

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)

but am getting a syntax error and it appears <TICKET_IDS> isn't resolving (or being detected?):

09:37> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' at line 3] in EXECUTE("UPDATE HD_TICKET, HD_TICKET PARENT
SET HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE PARENT.ID = HD_TICKET.PARENT_ID AND HD_TICKET.ID = ")

20:27> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3] in EXECUTE("UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = )")

I also tried without aliasing the PARENT, but that didn't work either (i.e.):

Update Query:
UPDATE HD_TICKET
SET HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)

All generated approximately the same error about there being something wrong in line 3. Unlike my earlier post, I noticed that <TICKET_IDS> does not appear to be resolving correctly, so it seems like I'm missing something (yet) again. Any ideas or suggestions would be much appreciated.

Thanks for all of your help!!!

John

So you have a batch rules that runs on a schedule and an OTS rule.

In the batch rule -- your update still needs to reference <TICKET_IDS>. <TICKET_IDS> (as described in the bubble help of the update) is a
sql UPDATE statement, replacing the string <TICKET_IDS> with a comma separated list of IDs extracted from the Select Query

So you need to have an ID colum and results of that become a list of IDs. In a batch rule this could contain 0, 1 or more values so you write your update query like this:
UPDATE ....
SET ..
WHERE ...
and HD_TICKET.ID IN (<TICKET_IDS>)
In an OTS rule it should only have one value so you write it like this:

UPDATE ....
SET ..
WHERE ...
and HD_TICKET.ID =<TICKET_IDS>

But since your update has no column called ID then <TICKET_IDS> is blank.

The select query needs to have one of PARENT.ID or CHILD.ID for HD_TICKET.ID in the list of it's columns -- whichever is appropriate. I honestly haven't been following close enough to recommend one.

I know you are only aiming for the OTS rule but I recommend to re-write your batch as well so the update has <TICKET_IDS> for both. Glad you have it working though.

OK, I re-read your comment here and it makes sense now:

http://itninja.com/question/create-outlook-exchange-profiles07
WHERE T.ID=<TICKET_IDS>
That last part will make sure that the ID (ticket number) from the select query is passed to the update

I was under the mistaken impression that <TICKET_IDS> was just used for OTS rules, but now I understand that it is used to pick up the ticket number from the select query.

Based on that, the revised batch should just need the HD_TICKET.ID added to the SELECT statement (like I did above) - in other words, the only difference from the OTS rule being the frequency.

Thanks again for the info (and help), GillySpy!!!

John

You're welcome. FYI for the batch your <TICKET_IDS> could contain many records( e..g 1,2,3,4,5) so you want the update in the batch to be HD_TICKET.ID IN (<TICKET_IDS>)

Definitely good to know, I'll add that to the toolkit. Thanks again for always posting extra info like that, it's definitely appreciated!

Keep up the great work!!!

John

jverbosk,

Is there a way I can get your current SQL statement for this rule? Why Kace doesn't have this as an option is beyond me but it would be helpful. I am a SQL newbie and don't quite understand some of the SQL statements in the previous posts. I am just using one queue, if that makes a difference.

Thanks,
Rob

robnpat,

It's in my 10/25/2011 3:28:42 PM posting on this thread, along with my notes. If all you want is the SQL statements I'm currently using, here they are - just be aware that you'll need to specify which field(s) in the parent ticket will be copied into the child ticket's fields. In my example below, I'm copying data from HD_TICKET.CUSTOM_FIELD_VALUE0 field in the Parent ticket into the HD_TICKET.CUSTOM_FIELD_VALUE0 field in the Child ticket, but this can definitely be changed and expanded to as many fields as you would want. Also, be aware that the child tickets won't reflect the parent ticket data until they are saved. If you have any questions, just let me know.

John
________________________________

Select Query:
SELECT PARENT.CUSTOM_FIELD_VALUE0 AS 'Parent Location', HD_TICKET.CUSTOM_FIELD_VALUE0 AS 'Child Location', HD_TICKET.ID AS "ID"
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID

X Run an update query, using the results from the one above

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)

OK, thanks. I really appreciate it. Going to try this now.

Rob

What exactly is CUSTOM_FIELD_VALUE0? Is that CUSTOM_0 in Custom Field? My Custom_1 is labeled as 'Building'. Also, 'Building' is a required field. Does that make a difference?

SELECT PARENT.CUSTOM_FIELD_VALUE1 AS 'Parent Location', HD_TICKET.CUSTOM_FIELD_VALUE1 AS 'Child Location', HD_TICKET.ID AS "ID"
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID

Rob,

I'm writing from my phone while I'm in traction, so please bear with me.... Also, I'm definitely not a SQL guru, I just tinker and ask the guys here for help when I get stuck. I'm not a programmer and my first exposure to SQL code was only several months ago when I was trying to figure out how to get patching reports the way I needed them to be.

The custom fields that are listed in the GUI (queue setup screen) correspond to the SQL custom fields, but the SQL fields are one lower since they start from 0. So custom field 1 in the GUI would be custom field 0 in the SQL code.

Next point is how I figure out the field names to use in SQL statements. It's pretty straightforward:

1) make a new report using the report wizard, including the field (or fields) you want to target (along with something that will be obvious, like the computer's IP address, etc)

2) save the report

3) click on a HTML, csv, etc link to run the report

4) verify the correct info is being pulled (some field names/choices aren't obvious, so do this - better to be 100% certain)

5) close the report and redo until you are satisfied with results

6) click on the name of the report to "edit" it and you should see the SQL code for the report

7) look at the first line (or two) for the "SQL names" of the fields you targeted

8) use these names in your custom ticket rules

If you look at my post on LDAP, Patching, etc and go towards the bottom of the post, I put my own non-SQL guru explanation of things there. Maybe helpful, maybe not - but free to read! (^_^)

For the purposes of this custom ticket rule, though, you should just need to specify the parent and child fields in the Select and Update statements.

Also, I thought about on the way home and although you can probably use multiple fields in one statement if you are guru enough (or just want to play around), you can by all means create multiple parent-child ticket rules (one for each pair of fields). I currently have two separate rules, mainly because in one of the rules I'm grabbing data from one field in the parent ticket and appending it to existing data in the child ticket field (not just a plain "make the child field the same as the parent field" rule. I believe that post is in the K1000 Ticket Rules forum, so just look there or click on my username to see my last 100 posts and it should be there.

Oh, and the mandatory fields should be fine, as I'm using them too without any mods necessary to the ticket rules.

I'm going to start working on a guide/post for setting up helpdesk queues from scratch (including planning and ticket rules), but it will probably take longer than my LDAP, patching, etc post since there's a bit more involved with my processes. I'll include this stuff as well, so hopefully all of it will help someone get things setup (or tweaked). I guess I'm saying this so you can keep an eye out.

John

Thanks for the help. I'll run the report as you mentioned to try and get the names correct.

Rob

OK, I think I may know where I am stuck. From my understanding in one of your previous posts, you are using 2 queues (parent queue, child queue). I am using just one queue. I did a query report, and 'edited' the SQL statement to see the custom fields. This is what I saw for the only queue I am using:

HD_TICKET.HD_QUEUE_ID = 1

So would my SQL statement go along these lines:

SELECT PARENT.CUSTOM_FIELD_VALUE0 AS HD_TICKET.HD_QUEUE_ID = 1, HD_TICKET.CUSTOM_FIELD_VALUE0 AS HD_TICKET.HD_QUEUE_ID = 1, HD_TICKET.ID AS "ID"
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID

Still stuck on this. Anyone have a quick solution?

Sorry for the late reply, been busy here.

First, I do have two queues, but they are not being referenced in the SQL code (that I'm aware of) - I just have them referenced in the notes for my own reference.

When the ticket rule runs against a child ticket (when that ticket is saved), the SQL code in the Select Query checks to confirm that a parent-child relationship exists and if so, passes the referenced fields (in my example, CUSTOM_FIELD_VALUE0 from the Parent ticket and CUSTOM_FIELD_VALUE0 from the Child ticket) to the Update query, which is what actually copies the data from the specified Parent ticket field into the specified Child ticket field.

You'll also need a corresponding Update Query, which is very similar to the Select Query.

What you need to figure out is the name of the field in your Parent ticket that you want to copy from, and the name of the field in your Child ticket that you want to copy into. This is why I included the tip about running the report. The name of the queue is unimportant, it's the name of the fields (in the report's SQL code) that you need to determine.

For my example, I wanted to grab the name from the Location field I created using a custom field. In the Parent ticket it was HD_TICKET.CUSTOM_FIELD_VALUE0, and in the Child ticket it was HD_TICKET.CUSTOM_FIELD_VALUE0 (I purposely made these the same, but they don't have to be, as my other post illustrates). The value for the Parent ticket was changed in this rule to PARENT.CUSTOM_FIELD_VALUE0 in order for things to work (this is the part that dchristian helped me with, as I had no idea where to begin).

Hope that helps!

John

The field names are only important if you are using the select query from the ticket rule in a report or using the "email query results" section. I do like using "email query results" when testing but it is not necessary for the rule to run. All you need for the rule to work is to return the ID in the query. e.g.
select HD_TICKET.ID
from ....

Simple version: to put CUSTOM_1 from the parent into CUSTOM_1 on the child when the child is saved (note here: the ID from the query is the child ticket #)
update HD_TICKET child JOIN HD_TICKET parent on parent.ID=child.PARENT_ID
set child.CUSTOM_FIELD_VALUE0=parent.CUSTOM_FIELD_VALUE0 /*note that with ticket fields numbering starts at 0 inside the database*/
WHERE child.ID IN (<TICKET_IDS>)


Note that for these custom fields the web ui refers to CUSTOM_1 but in the db it is CUSTOM_FIELD_VALUE0. This difference is not consistent for all fields.

Thanks guys. Looks like I'm getting closer. I got my 'Select Query' to run without errors (yaaa!) and now working on the 'Update Query'.

Here is what I have for the 'Update Query':

update HD_TICKET child JOIN HD_TICKET parent on parent.ID=child.PARENT_ID
set child.CUSTOM_FIELD_VALUE3=parent.CUSTOM_FIELD_VALUE3
WHERE HD_TICKET.ID IN (<TICKET_IDS>)

Here is the error I'm getting:

50:33> Starting: Mon, 12 Dec 2011 08:50:33 -0600
50:33> Executing Select Query...
50:33> selected 2 rows
50:33> Executing Update Query...
50:33> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3] in EXECUTE("update HD_TICKET child JOIN HD_TICKET parent on parent.ID=child.PARENT_ID
set child.CUSTOM_FIELD_VALUE3=parent.CUSTOM_FIELD_VALUE3
WHERE HD_TICKET.ID IN (,)")

So, I'm halfway there.

What is your select query? There is something wrong the select query since it is returning two rows with "blank" values for ID as indicated by the comma WHERE HD_TICKET.ID IN ( , )

Also what is the schedule of your rule? An "on ticket save" rule should only return one row

Here is an updated 'Update Query'. I ran it manually and, in this instance, the field updated in child tickets. Now I just have to get it to work on OTS.

UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.CUSTOM_FIELD_VALUE3 = PARENT.CUSTOM_FIELD_VALUE3
WHERE PARENT.ID = HD_TICKET.PARENT_ID

30:36> Starting: Mon, 12 Dec 2011 09:30:36 -0600
30:36> Executing Select Query...
30:36> selected 2 rows
30:36> Executing Update Query...
30:36> updated 2 rows
30:36> Ending: Mon, 12 Dec 2011 09:30:36 -0600

I only have 2 child tickets for this parent ticket.

okay, this is working because it is not dependent upon <TICKET_IDS> . This update is updating CUSTOM_4 for ALL children in all queues even if the field already has a value.

An OTS rule should use <TICKET_IDS> so that you are only updating what changed. What is the select query?

If you do not want to update a child that already has the value set to non-empty then add

WHERE ...
and HD_TICKET.CUSTOM_FIELD_VALUE3<>'

If you do not want to update a child that already has the value equal to the parent then add:

WHERE ...
and HD_TICKET.CUSTOM_FIELD_VALUE3 <> PARENT.CUSTOM_FIELD_VALUE3

My 'Select Query':

SELECT PARENT.CUSTOM_FIELD_VALUE3, CHILD.CUSTOM_FIELD_VALUE3
FROM HD_TICKET PARENT, HD_TICKET CHILD
WHERE PARENT.ID = CHILD.PARENT_ID

I'm going through this thread and looking at jverbosk's resolutions and trying them. Are you thinking my 'Select Query' may be wrong?

In jverbosk's example for 'Select Query', he has HD_TICKET.ID AS 'ID'

I put that in my 'Select Query' but it coughs up a lung:

11:42> Starting: Mon, 12 Dec 2011 10:11:42 -0600
11:42> Executing Select Query...
11:42> mysql error: [1054: Unknown column 'HD_TICKET.ID' in 'field list'] in EXECUTE("SELECT PARENT.CUSTOM_FIELD_VALUE3, CHILD.CUSTOM_FIELD_VALUE3, HD_TICKET.ID AS 'ID'
FROM HD_TICKET PARENT, HD_TICKET CHILD
WHERE PARENT.ID = CHILD.PARENT_ID ")

My guess is the 'ID' is some variable??

Putting HD_TICKET.ID in your query requires that HD_TICKET exist as a referenced object and it have a column called ID. HD_TICKET certainly does have a column called ID and your query uses HD_TICKET but it has renamed HD_TICKET to something else (ie. CHILD). I changed it back so syntactically the following would work. I'm not sure if it does what you want though.

SELECT HD_TICKET.ID
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID

GillySpy - Thanks for clarifying things as usual!

robnpat - Glad you got it working. Now just create additional ticket rules, change the field names in the queries and you should be all set. On the flip side, if you do get adventurous and try to get everything working in a single ticket rule, please share your results! ^_^

John

Rob,

Thanks for sharing, glad it's working!

John