We have been using Active Directory authentication in KBOX for a long time but only recently more data is available. The work phone number is now appearing next to a Submitter's name. It would be nice to automatically populate custom fields on tickets. Specifically we now have automatic updating of Location and Job Title information. On a ticket I'd like to have the Submitter's Name followed by
CUSTOM_5 = Ticket: Location = User Detail: Location
CUSTOM_6 = Ticket: Job Title = User Detail: Custom 1.

I'm stuck on the query to get the data from the User to the Ticket or does this have to be a custom ticket rule on save instead?

I tried using the example but it isn't producing results:
query: select distinct(LOCATION) from USER
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
SELECT DISTINCT LOCATION FROM USER

Will give you a list of unique location values from your user table. Are you trying to link the user's location and job title automatically to a ticket? If so, you'll need to setup a custom SQL rule to query the selected user's ID row for the Location and Job Title fields (which I assume is one of the 4 custom fields).
Answered 02/17/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Yes, I want to autofill two custom ticket fields from two User data fields and one of those is a custom User field. I assume this will require another "On Ticket Save" rule to fill in those fields, which is fine. I am clueless on how to make that SQL though.

The logic is something like:
copy the Submitters Location (User Detail: Location) and enter it into the Ticket: Location field (CUSTOM_5) and
copy the Submitter's Job Title (User Detail: Custom 1) and insert it into the Ticket: Job Title field (CUSTOM_6).
Answered 02/18/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
You'd think it would be simple enough for a technician to click the little person icon next to the Submitters name to see the same data in a new window but I'd like to save a click and see two pieces of that data right on the ticket. If it's not easy to do we can settle for an extra click!
Answered 02/18/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
Rich, using a ticket rule to automatically populate the fields is much easier than KACE adding an icon to display the results in a new window (this would require KACE to modify the PHP of the console).

Try this as an "On Ticket Save" rule. This rule will update all tickets with a valid submitter, and it will only update users with valid Locations and Titles entered in their User account.

Select Query:
SELECT T.ID AS ID FROM HD_TICKET T
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
WHERE T.SUBMITTER_ID != ""


Update Query:
UPDATE HD_TICKET T
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE5 = U.LOCATION, T.CUSTOM_FIELD_VALUE6 = U.CUSTOM_1
WHERE T.ID IN (<TICKET_IDS>)
Answered 02/22/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thanks Andy for this rule that will be handy when it works.

Here's the run log:

22:26> Starting: Tue, 23 Feb 2010 15:22:26 -0700
22:26> Executing Select Query...
22:26> mysql error: [1054: Unknown column 'HD_TICKET.ID' in 'where clause'] in EXECUTE("SELECT T.ID AS ID FROM HD_TICKET T
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
WHERE T.SUBMITTER_ID != "" and (HD_TICKET.ID = 46706) ")
Answered 02/23/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
There already is a little person symbol at the end of the Submitter line. When the symbol is clicked another window pops open with User data:



Answered 02/23/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
When writing an "On Ticket Save" ticket rule you cannot alias the HD_TICKET table since it will add and HD_TICKET.ID=X to the end of any query.

You'll need to rewrite your query as:
SELECT T.ID AS ID FROM HD_TICKET
JOIN USER U ON (U.ID = HD_TICKET.SUBMITTER_ID)
WHERE HD_TICKET.SUBMITTER_ID <>0


The update query does not have this restriction because it is always run "as-is" when considering the <TICKET_IDS> as a comma-separated list of values.

reference: What are Ticket Rules? (FAQ)
Answered 02/23/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Thanks for the update Gerald but now I'm getting a different error:

09:36> Starting: Tue, 23 Feb 2010 23:09:36 -0700
09:36> Executing Select Query...
09:36> mysql error: [1054: Unknown column 'T.ID' in 'field list'] in EXECUTE("SELECT T.ID AS ID FROM HD_TICKET
JOIN USER U ON (U.ID = HD_TICKET.SUBMITTER_ID) WHERE HD_TICKET.SUBMITTER_ID <>0
and (HD_TICKET.ID = 27030) ")
Answered 02/23/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
Since you can't alias the HD_TICKET table, T.ID is no longer valid - it needs to be HD_TICKET.ID. Thanks for that info Gerald; I forgot about that alias issue with OTS queries.


SELECT HD_TICKET.ID AS ID FROM HD_TICKET
JOIN USER U ON (U.ID = HD_TICKET.SUBMITTER_ID) WHERE HD_TICKET.SUBMITTER_ID <>0
Answered 02/24/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
ooops...i would modify my post but having issues with that this morning. Thanks to airwolf
Answered 02/24/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
OK, there's no longer an error message for the Select Query, but the Submitter's Location is filling in the custom field for Position (Job Title) instead of Location. The screen shots below show the 3 fields in the Ticket Layout section and the results in a ticket.



Answered 02/24/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
In the helpdesk (HD_TICKET)...
custom field 1 in the UI is CUSTOM_FIELD_VALUE0 in the database...so
custom field 5 in the UI is CUSTOM_FIELD_VALUE4 in the database

In users (USER)...
Custom 1 in the UI is CUSTOM_1 in the database
Answered 02/24/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Woohoo! It's working now with the following queries. Thanks again Gerald and Andy:

Select:SELECT HD_TICKET.ID AS ID FROM HD_TICKET
JOIN USER U ON (U.ID = HD_TICKET.SUBMITTER_ID) WHERE HD_TICKET.SUBMITTER_ID <>0

Update:UPDATE HD_TICKET T
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE4 = U.LOCATION, T.CUSTOM_FIELD_VALUE5 = U.CUSTOM_1
WHERE T.ID IN (<TICKET_IDS>)
Answered 02/24/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
Woohoo! It's working now with the following queries. Thanks again Gerald and Andy:

Select:SELECT HD_TICKET.ID AS ID FROM HD_TICKET
JOIN USER U ON (U.ID = HD_TICKET.SUBMITTER_ID) WHERE HD_TICKET.SUBMITTER_ID <>0

Update:UPDATE HD_TICKET T
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE4 = U.LOCATION, T.CUSTOM_FIELD_VALUE5 = U.CUSTOM_1
WHERE T.ID IN (<TICKET_IDS>)
Answered 02/24/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
Answer this question or Comment on this question for clarity