Return
-
Title
Service Desk: Using SQL queries in Custom Ticket Fields -
Description
Custom fields in the help desk are great ways to add pertinent information to a ticket using a field that is not, by default, provided in the help desk -
Cause
Normally the selectable information is added to these fields manually. If you have information that resides elsewhere in the K1000 that you would like to have in a custom field we can populate that information with a MySQL query. -
Resolution
NOTE: The querySQL code provided as a courtesy by KACE to its customers. We make no promises as to workability or functionality under certain circumstances. In default situations under normal setup these work as designed. KACE does not support modification nor usage. We highly recommend you test all code no matter the source before using in a production environment. If you are cut and pasting the code, paste in to a text editor then copy back out to remove any hidden characters or markup.
---
To create a custom field:
- Click Service Desk > Configuration > Queues.
- The Service Desk Queues page appears.
- Click the desired help desk Queue.
- Click Customize Fields and Layout.
- The Service Desk Customization page appears.
- In the Custom Fields area, click the page edit icon to edit or create a field.
- The editable fields appear.
- Select the field type from the Field Type list.
- What kind of field type (text entry, or single or multiple select) to use in each field?
- Fill in the Select Values field with the query of your choice.
- query: select distinct(LOCATION) from USER order by LOCATION asc
.For example: If you have the location for your users filled from Active Directory imports. The following query will list the locations in alphabetical order..Step-1: mapping the Active Directory attributes, importing and update the users valueStep-2: querySQL to show the all User Custom Field.SELECTUSER.ID,USER.USER_NAME,USER.FULL_NAME,USER.EMAIL,V1.FIELD_VALUE AS CUSTOM_1,V2.FIELD_VALUE AS CUSTOM_2,V3.FIELD_VALUE AS CUSTOM_3,V4.FIELD_VALUE AS CUSTOM_4FROM USERLEFT JOIN USER_FIELD_VALUE V1 ON V1.USER_ID = USER.ID AND V1.FIELD_ID = 1LEFT JOIN USER_FIELD_VALUE V2 ON V2.USER_ID = USER.ID AND V2.FIELD_ID = 2LEFT JOIN USER_FIELD_VALUE V3 ON V3.USER_ID = USER.ID AND V3.FIELD_ID = 3LEFT JOIN USER_FIELD_VALUE V4 ON V4.USER_ID = USER.ID AND V4.FIELD_ID = 4.NOTE: In my example, I mapping the Active Directory Department attribute on User Field 2.Step-3: querySQL to show the User Custom Field 2select V2.FIELD_VALUE as CUSTOM_2 from USER left join ASSET UL on UL.ID = USER.LOCATION_ID and UL.ASSET_TYPE_ID = 1 left join USER_FIELD_VALUE V2 on V2.USER_ID = USER.ID and V2.FIELD_ID = 2 where V2.FIELD_VALUE is not NULL group by V2.FIELD_VALUE order by V2.FIELD_VALUE asc.Now, you can informing the query of your choice on Service Desk Fields and Layout data..Another example: Create a departament data from ASSET- querySQL to show the ASSET Info
- select ASSET.NAME from ASSET where ASSET.ASSET_TYPE_ID = 2 order by ASSET.NAME asc
- Click Service Desk > Configuration > Queues.
-
Additional Information
Using SQL queries in Custom Ticket Fields