User Custom field changes in 7.0 / 8.0 Want to add view to database
We make extensive use of the custom fields in the User table in ticket rules and external reporting and inquiry interfaces. With the changes in data structure with 7.0 accessing the 4 original custom fields is completely different and will require extensive updates.
If a view could be added to the database structure (like the SAM_View s that are visible) that flattened out the custom fields, it would be dramatically easier to update the ticket rules and interface, and the modified code would be much simpler than if we updated each instance directly.
This is currently preventing us from upgrading from 6.4 . I have been in contact with KACE support, professional services and have posted it as a "feature request".
Does anyone have either an alternative solution, or a suggestion on the best channel to have a view added to KACE that I can reference in ticket rules etc?
The view query would be along the lines of the following:
SELECT
USER.*,
USER_FIELD_VALUE.FIELD_VALUE AS CUSTOM_1,
USER_FIELD_VALUE1.FIELD_VALUE AS CUSTOM_2,
USER_FIELD_VALUE2.FIELD_VALUE AS CUSTOM_3,
USER_FIELD_VALUE3.FIELD_VALUE AS CUSTOM_4
FROM
USER
INNER JOIN USER_FIELD_VALUE ON USER_FIELD_VALUE.USER_ID = USER.ID
INNER JOIN USER_FIELD_VALUE USER_FIELD_VALUE1 ON USER_FIELD_VALUE1.USER_ID = USER.ID
INNER JOIN USER_FIELD_VALUE USER_FIELD_VALUE2 ON USER_FIELD_VALUE2.USER_ID = USER.ID
INNER JOIN USER_FIELD_VALUE USER_FIELD_VALUE3 ON USER_FIELD_VALUE3.USER_ID = USER.ID
WHERE
USER_FIELD_VALUE.FIELD_ID = 1 AND
USER_FIELD_VALUE1.FIELD_ID = 2 AND
USER_FIELD_VALUE2.FIELD_ID = 3 AND
USER_FIELD_VALUE3.FIELD_ID = 4
View_User
SELECT
USER.*,
USER_FIELD_VALUE.FIELD_VALUE AS CUSTOM_1,
USER_FIELD_VALUE1.FIELD_VALUE AS CUSTOM_2,
USER_FIELD_VALUE2.FIELD_VALUE AS CUSTOM_3,
USER_FIELD_VALUE3.FIELD_VALUE AS CUSTOM_4
FROM
USER
INNER JOIN USER_FIELD_VALUE ON USER_FIELD_VALUE.USER_ID = USER.ID
INNER JOIN USER_FIELD_VALUE USER_FIELD_VALUE1 ON USER_FIELD_VALUE1.USER_ID = USER.ID
INNER JOIN USER_FIELD_VALUE USER_FIELD_VALUE2 ON USER_FIELD_VALUE2.USER_ID = USER.ID
INNER JOIN USER_FIELD_VALUE USER_FIELD_VALUE3 ON USER_FIELD_VALUE3.USER_ID = USER.ID
WHERE
USER_FIELD_VALUE.FIELD_ID = 1 AND
USER_FIELD_VALUE1.FIELD_ID = 2 AND
USER_FIELD_VALUE2.FIELD_ID = 3 AND
USER_FIELD_VALUE3.FIELD_ID = 4
-
Sorry - pasted in the query SQL twice... - VSwift 6 years ago
-
Can you describe a bit more what the issue is, i am guessing you are using the old custom fields into which ldap data could be imported, but as the user table has changed this has messed things up? What were you using the data for, that you are no longer able to? - Hobbsy 6 years ago
-
The issue is referencing the fields CUSTOM_1, CUSTOM_2, etc in ticket rules and reports. We LDAP value into those fields for Department (for example) and then have logic based on that value. We might reference the department field for different users (i.e. submitter.... ticket owner) and the code update required is complex and cumbersome. We ended up having to purchase professional services to deal with the situation... - VSwift 6 years ago
Answers (0)
Be the first to answer this question
