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
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)
Please log in to answer
Posted by:
airwolf
14 years ago
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).
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
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).
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
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
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:
Update Query:
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
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) ")
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
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
When writing an "On Ticket Save" ticket rule you cannot alias the HD_TICKET table since it will add
You'll need to rewrite your query as:
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)
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
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) ")
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
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
ooops...i would modify my post but having issues with that this morning. Thanks to airwolf
Posted by:
RichB
14 years ago
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
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
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
Woohoo! It's working now with the following queries. Thanks again Gerald and Andy:
Select:
Update:
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
Woohoo! It's working now with the following queries. Thanks again Gerald and Andy:
Select:
Update:
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.