KACE SMA SQL Attributes
Good morning! I hope that everyone is having a wonderful week. Is there a secret KACE MySQL attribute guide out there ha ha? I have been trying to pre-populate a tickets submitters location, Custom_1, and Custom_2 data into a ticket to speed up the submission process for the end user. I have tried creating reports and reviewing the SQL queries but there is clearly something that I am missing. I reviewed the KACE manual and I am not seeing a list. Any guidance would be much appreciated! I am running the latest KBOX. Appreciate you!
Answers (1)
Top Answer
There isn't a database diagram but the tables are laid out well. I recommend enabling the external reporting setting and connecting to the database to view the tables. I currently use DBeaver for my database tool.
To answer your specific question, here is the SQL select statement we use for setting the user's location. Note that we put the information in Custom Field 11, which in the database is CUSTOM_FIELD_VALUE10 and that field has a default value of 'Set on Save', this insures that the rule only runs once for the ticket.
FROM HD_TICKET
WHERE HD_TICKET.CUSTOM_FIELD_VALUE10 = 'Set on Save'
and HD_TICKET.HD_QUEUE_ID = 2
Here is the corresponding update statement:
update HD_TICKET as Tset T.CUSTOM_FIELD_VALUE10 = (
select ASSET.NAME FROM USER
JOIN ASSET on ASSET.ID = USER.LOCATION_ID
where USER.ID = SUBMITTER_ID ) where
T.ID = <TICKET_IDS>;
Note that the select statement returns a list of comma separated ticket IDs, since our select statement will only return one value the TICKET_IDS variable will be just that value.
Here is an update statement that uses a custom user field:
update HD_TICKET as Tset T.CUSTOM_FIELD_VALUE0 = (select FIELD_VALUE FROM USER_FIELD_VALUE where USER_ID = SUBMITTER_ID and FIELD_ID = 4) where
T.ID = <TICKET_IDS>;
