KACE Product Support Question
Need to add User Custom Field 1 to some reports
07/23/2018 776 views
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.
Answer Chosen by the Author
Please log in to 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:
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
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
Answered 07/30/2018 by: chucksteel