-
Title
Updating SQL which uses the USER table after upgrade to version 7.0.x -
Description
Database schema changes in 7.0 may change the way custom rules execute. This article is meant to provide an example of the type of changes to a SQL query that may be necessary with database changes in 7.0. Please note that KACE Support does not assist with directly creating / repairing custom ticket rules. For more information on obtaining assistance with custom ticket rules, please see How To Contact KACE Professional Services (176023).
All K1000 7.0 Database Schema Changes are documented in the K1000 7.0 Database Schema Changes article.
-
Resolution
CODE WRITTEN BY KACE SUPPORT FOR DEMONSTRATION PURPOSES ONLY AND NOT SUPPORTED BY KACE TECHNICAL SUPPORT. CLIENT CONFIRMS THE NEED FOR TESTING AND ACCEPTS ALL RESPONSABILITY FOR USE AND MISUSE OF CODE. KACE SHALL NOT BE HELD LIABLE FOR DAMAGE OR DOWN TIME RESULTING FROM USE OF THIS OR ANY CODE PRESENTED FOR PURPOSES OF TROUBLESHOOTING OR DEMONSTRATION
USER.LOCATION
The User “Location” data is no longer stored in the USER table as USER.LOCATION. This data is now stored by specifying a “Location” type asset ID referred to by USER.LOCATION_ID to point to a “Location” type asset’s ID in the ASSET table.
Example
Previously (before K1000 v7), a user’s Full Name and Location would be selected using SQL similar to the following:
select
USER.FULL_NAME,
USER.LOCATION
from
USERIn K1000 v7, the following SQL will get the same information:
select
USER.FULL_NAME,
ASSET.NAME
from
USER
join ASSET on USER.LOCATION_ID = ASSET.IDUSER.CUSTOM_#
The User “Custom #” data (Custom 1, Custom 2, etc…) is no longer stored in the USER table as USER.CUSTOM_# (USER.CUSTOM_1, USER.CUSTOM_2, etc…). This data is now stored in a table called “USER_FIELD_VALUE” which is new to v7.
Example
Previously (before K1000 v7), a user’s Full Name and Custom 1 would be selected using SQL similar to the following:
select
USER.FULL_NAME,
USER.CUSTOM_1
from
USERIn K1000 v7, the following SQL will get the same information:
select
USER.FULL_NAME,
USER_FIELD_VALUE.FIELD_VALUE
from
USER
join USER_FIELD_VALUE on USER.ID = USER_FIELD_VALUE.USER_ID
and USER_FIELD_VALUE.FIELD_ID = 1Note: All K1000 7.0 Database Schema Changes are documented here.