/build/static/layout/Breadcrumb_cap_w.png

Submitter data to ticket fields

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

Answers (14)

Posted by: airwolf 14 years ago
Red Belt
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).
Posted by: RichB 14 years ago
Second Degree Brown Belt
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).
Posted by: RichB 14 years ago
Second Degree Brown Belt
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!
Posted by: airwolf 14 years ago
Red Belt
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>)
Posted by: RichB 14 years ago
Second Degree Brown Belt
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) ")
Posted by: RichB 14 years ago
Second Degree Brown Belt
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:



Posted by: GillySpy 14 years ago
7th Degree Black Belt
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)
Posted by: RichB 14 years ago
Second Degree Brown Belt
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) ")
Posted by: airwolf 14 years ago
Red Belt
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
Posted by: GillySpy 14 years ago
7th Degree Black Belt
0
ooops...i would modify my post but having issues with that this morning. Thanks to airwolf
Posted by: RichB 14 years ago
Second Degree Brown Belt
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.



Posted by: GillySpy 14 years ago
7th Degree Black Belt
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
Posted by: RichB 14 years ago
Second Degree Brown Belt
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>)
Posted by: RichB 14 years ago
Second Degree Brown Belt
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>)
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ