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
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
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
Posted by:
GillySpy
14 years ago
Posted by:
RichB
14 years ago
Posted by:
GillySpy
14 years ago
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.
so that the conversation will remain readable.