/build/static/layout/Breadcrumb_cap_w.png

Getting Locations in a ServiceDesk Dropdown

AB9BVSUHWWS0AAAAAElFTkSuQmCC

A common question we get asked is how to get a dropdown in the ServiceDesk that contains the locations of users.

In the Queue setup area, if you scroll down to the CUSTOM FIELDS area and click on the little Question mark a grey help panel is revealed.

Whilst this is really helpful as it shows you how to embed an SQL Query into a single select field, the data in the grey box as an example

"For example, to display a list of employee locations based on data stored in user records, use "query: select distinct(LOCATION) from USER"

Is so out of date and unhelpful it is laughable!!!

Here is the query we use occasionally, which basically looks at the imported user records and compiles a list of locations found, from the main Asset Location table

query: SELECT DISTINCT(ASSET.NAME) FROM USER USER INNER JOIN ASSET ASSET ON (USER.LOCATION_ID = ASSET.ID) WHERE ASSET.ID = USER.LOCATION_ID

We hope you find this useful.

Other Indigo Mountain Products and Services


BarKode for KACE - Enabling effective Asset Management Best Practice

For more information about BarKode click here or download a demo appliance here


Comments

  • It is half helpful, because adding that custom field generates a drop down list of all possible locations. Is it possible to select from that list just the location belonging to the submitter? I imagine there should a variable holding the current ticket id? - Empousa 2 months ago
    • That would be a different situation and would need to be a ticket rule that runs to populate the field rather than providing a lookup. The challenge you will also have with that is what happens if the location is wrong or different to AD? Do you set the rule to run constantly as it will overwrite the corrected data or do you just set it to run the one time, which is a trick we do regularly? - Hobbsy 1 month ago
      • You are correct that some tickets do not match the submitter's location (few of them), but the new column would still serve its purpose to act as a hint for helpdesk. And yes, it should run once when the ticket is created so if we see that the location is wrong we should have the option to change it, ideally. - Empousa 1 month ago
  • I have created this rule (CUSTOM_FIELD_VALUE15 is the one where Location will be stored) but i'm not sure it's the right one, because i'm a beginner in Kace customization and not that proficient in SQL either
    select HD_TICKET.*,
    HD_STATUS.NAME AS STATUS_NAME,
    HD_STATUS.ORDINAL as STATUS_ORDINAL,
    HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
    HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
    HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
    STATE,
    if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
    if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
    if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
    if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
    if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
    if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
    if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
    if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
    if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
    case upper(STATE)
    when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
    when 'OPENED' then unix_timestamp() - unix_timestamp(HD_TICKET.TIME_OPENED)
    else unix_timestamp() - unix_timestamp(HD_TICKET.CREATED) end as AGE,
    if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
    U1.FULL_NAME as OWNER_FULLNAME,
    U1.EMAIL as OWNER_EMAIL,
    if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
    if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
    U2.FULL_NAME as SUBMITTER_FULLNAME,
    U2.EMAIL as SUBMITTER_EMAIL,
    if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
    if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
    if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(HD_TICKET.APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
    Q.NAME as QUEUE_NAME
    from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
    LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
    LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
    LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
    LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
    LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
    where HD_PRIORITY.ID = HD_PRIORITY_ID
    and HD_STATUS.ID = HD_STATUS_ID
    and HD_IMPACT.ID = HD_IMPACT_ID
    and HD_CATEGORY.ID = HD_CATEGORY_ID
    and HD_TICKET.HD_QUEUE_ID =1
    and ( HD_TICKET.CUSTOM_FIELD_VALUE15 IS NULL OR HD_TICKET.CUSTOM_FIELD_VALUE15 = "" )

    with the update part being

    update HD_TICKET
    set HD_TICKET.CUSTOM_FIELD_VALUE15 = (SELECT DISTINCT(ASSET.NAME) FROM USER USER INNER JOIN ASSET ASSET ON (USER.LOCATION_ID = ASSET.ID) WHERE ASSET.ID = USER.LOCATION_ID AND USER.ID = (SELECT SUBMITTER_ID FROM HD_TICKET WHERE HD_TICKET.ID in (<TICKET_IDS>)) )
    where
    (HD_TICKET.ID in (<TICKET_IDS>)) - Empousa 1 month ago
  • I tested it with a narrowing the select on one specific ticket test number and it works. It does grab the location, which is great, but if the location of the submitter is not correct (like a TL submitting a case), then even if i open the drop-down list, it only has one location, which is obvious from the way i wrote the update
    But the big question is if there is any way to populate that field with a list of all location but automatically select the default one as being the submitter's location - Empousa 1 month ago
  • And i did it by placing this in the custom fields select values:
    query: SELECT DISTINCT(ASSET.NAME) FROM USER USER INNER JOIN ASSET ASSET ON (USER.LOCATION_ID = ASSET.ID) WHERE ASSET.ID = USER.LOCATION_ID
    which will make all location available for selecting, but the ticket rule will select the default location as being the submitter's one

    YAY! - Empousa 1 month ago
    • Looks good, so just setup a custom field as a switch field, so the rule is only run once, so if the value needs to be changed it can be. Set any custom field as text and with a default value of 1. Then add to your ticket rule WHERE statement, when that custom field value is = 1 and then add to your UPDATE statement, set custom field value to 2 and it will only ever work once ;o) - Hobbsy 1 month ago
This post is locked
 
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