Need to add User Custom Field 1 to some reports
I am not a SQL guy at all, so I need help here. Have imported "Department" from AD into User Custom Field 1. I need this field to show up on a couple of simple reports, so I can sort the report by Department.
7 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
5 years ago
Top Answer
You can do this (setting the custom field in the ticket) to the user's department. Create a new rule that runs on ticket save. I have my custom field's default value set to "Set on Save" and my rule matches those tickets. If you want to update historical tickets, then you might have to change the rule slightly.
This update statement will then change the custom ticket field to contain the custom user field:
update HD_TICKET as T
set T.CUSTOM_FIELD_VALUE3 = (
select USER_FIELD_VALUE.FIELD_VALUE FROM USER_FIELD_VALUE
where USER_FIELD_VALUE.USER_ID = SUBMITTER_ID and USER_FIELD_VALUE.FIELD_ID = 1) where
T.ID = <TICKET_IDS>;
Here is a simple select statement you can use:
SELECT HD_TICKET.ID
FROM HD_TICKET
JOIN USER_FIELD_VALUE DEPARTMENT on DEPARTMENT.USER_ID = SUBMITTER_ID and DEPARTMENT.FIELD_ID = 1
WHERE HD_TICKET.CUSTOM_FIELD_VALUE3 != DEPARTMENT.FIELD_VALUE
This will select tickets where the custom field value is not the same as the submitter's department.
Also, to add custom user fields to a report, you need to add a join statement to the USER_FIELD_VALUE table:
JOIN USER_FIELD_VALUE DEPARTMENT as DEPARTMENT.USER_ID = SUBMITTER_ID and DEPARTMENT.FIELD_ID = 1
This will allow you to select the department as DEPARTMENT.FIELD_VALUE as DEPARTMENT. A super basic report for open tickets would then look like this:
SELECT HD_TICKET.ID, SUBMITTER.FULL_NAME, DEPARTMENT.FIELD_VALUE as DEPARTMENT
FROM HD_TICKET
JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
JOIN USER_FIELD_VALUE DEPARTMENT on DEPARTMENT.USER_ID = HD_TICKET.SUBMITTER_ID and DEPARTMENT.FIELD_ID = 1
WHERE HD_TICKET.TIME_CLOSED = "0000-00-00 00:00:00"
You can add your other custom user fields with additional join statements. For instance, we have the AD description in custom user field 3.
JOIN USER_FIELD_VALUE DESCRIPTION on DESCRIPTION.USER_ID = HD_TICKET.SUBMITTER_ID and DESCRIPTION.FIELD_ID = 3
Example your script might look something like this:-
SELECT ID AS TICK,
CREATED,
TITLE,
TIME_CLOSED
FROM HD_TICKET
With your line added it would look like this:-
SELECT ID AS TICK,
CREATED,
TITLE,
CUSTOM_FIELD_VALUE0 AS DEPT,
TIME_CLOSED
FROM HD_TICKET - Druis 5 years ago
07/30/2018 16:20:49> Starting: 07/30/2018 16:20:49 07/30/2018 16:20:49> Executing Select Query... 07/30/2018 16:20:49> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<TICKET_IDS>' at line 5] in EXECUTE("update HD_TICKET as T set T.CUSTOM_FIELD_VALUE3 = ( select USER_FIELD_VALUE.FIELD_VALUE FROM USER_FIELD_VALUE where USER_FIELD_VALUE.USER_ID = SUBMITTER_ID and USER_FIELD_VALUE.FIELD_ID = 1) where T.ID = <TICKET_IDS>;") - ForneyGonzo 5 years ago
Also, if you are using a rule to update multiple tickets and not just on ticket save you will need to change "where T.ID = <TICKET_IDS>" to "WHERE T.ID in (<TICKET_IDS>)" - chucksteel 5 years ago
SQL Statement...
update HD_TICKET as T set T.CUSTOM_FIELD_VALUE3=(select USER_FIELD_VALUE.FIELD_VALUE FROM USER_FIELD_VALUE where USER_FIELD_VALUE.USER_ID=SUBMITTER_ID and USER_FIELD_VALUE.FIELD_ID=1) where T.ID=<TICKET_IDS>;
Last Run Log...
07/31/2018 09:48:10> Starting: 07/31/2018 09:48:10 07/31/2018 09:48:10> Executing Select Query... 07/31/2018 09:48:10> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<TICKET_IDS>; and (HD_TICKET.ID = 673)' at line 1] in EXECUTE("update HD_TICKET as T set T.CUSTOM_FIELD_VALUE3=(select USER_FIELD_VALUE.FIELD_VALUE FROM USER_FIELD_VALUE where USER_FIELD_VALUE.USER_ID=SUBMITTER_ID and USER_FIELD_VALUE.FIELD_ID=1) where T.ID=<TICKET_IDS>; and (HD_TICKET.ID = 673) ") - ForneyGonzo 5 years ago
It's unfortunate that the Quest tech support people don't know that the <TICKET_IDS> variable is populated by the results of the select statement. Can you post your select statement? It might not be returning the ID of the ticket in the ID field. - chucksteel 5 years ago
07/31/2018 12:39:42> Starting: 07/31/2018 12:39:42 07/31/2018 12:39:42> Executing Select Query... 07/31/2018 12:39:42> selected 1 rows 07/31/2018 12:39:42> Adding ticket comments... 07/31/2018 12:39:42> updated 1 tickets 07/31/2018 12:39:42> Executing Update Query... 07/31/2018 12:39:42> updated 1 rows 07/31/2018 12:39:42> Ending: 07/31/2018 12:39:42 - ForneyGonzo 5 years ago