/bundles/itninjaweb/img/Breadcrumb_cap_w.png

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

1 Comment   [ - ] Hide Comment

Comments

  • Sorry - pasted in the query SQL twice...
    • 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?
      • 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...
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

There are no answers at this time
Answer this question or Comment on this question for clarity

Answers

Share