/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Dynamic Location Field

04/17/2019 638 views

I'm trying to create a custom ticket field for the submitter's location (mapped to physicalDeliveryOfficeName). I would like this field to be dynamic so when the submitter is selected this custom field automatically populates with the correct location information. I can do the following, but this is not dynamic:
query: SELECT NAME FROM USER JOIN ASSET on USER.LOCATION_ID = ASSET.ID where USER.USER_NAME = 'first.last'
This also works:
query: SELECT NAME FROM USER JOIN ASSET on USER.LOCATION_ID = ASSET.ID where USER.ID = '120'

We are already importing the user's location properly and I can pull all of the locations, but I only want a single location based off of the selected user's Active Directory information (physicalDeliveryOfficeName).

Thanks for any help that's provided!

KACE SMA v9.0.270

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

The SMA doesn't support dynamic fields like that, unfortunately. However, it sounds like you want to set a field based on the user, and that can be done using a custom ticket rule. Here is how I accomplished this on my appliance.

Create your custom field in the service desk and set the default value to "Set on Save"

Create a new custom ticket rule, put anything you want in the wizard, we're just going to erase it.

For the SQL select statement, enter the following:

select HD_TICKET.ID     
from HD_TICKET
WHERE
HD_TICKET.CUSTOM_FIELD_VALUE10 = 'Set on Save'

Check the box for Run Update Query

Enter this for the SQL statement:

update HD_TICKET as T
    set 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>;

Set the rule to run on ticket save.

Note that my custom field in the service desk is field 11, which maps to CUSTOM_FIELD_VALUE10 in the database (the database columns start at 0). You may have to adjust that based on your custom field.


Answered 04/19/2019 by: chucksteel
Red Belt

  • Thanks for the info ChuckSteel. I'll put this in my bag of tricks for next time. For now, we are going to bypass the user-by-location field. We decided to create Distribution Groups by location and will assign these members to the Owner field.
    Thanks again!
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ