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
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 2 years ago
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 2 years ago
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 2 years ago
query: SELECT DISTINCT(ASSET.NAME) FROM USER USER INNER JOIN ASSET ASSET ON (USER.LOCATION_ID = ASSET.ID) WHERE ASSET.ID = USER.LOCATION_ID
I tried adding an order by asc and no go - barchetta 1 year ago
query: SELECT DISTINCT(ASSET.NAME) FROM USER USER INNER JOIN ASSET ASSET ON (USER.LOCATION_ID = ASSET.ID) WHERE ASSET.ID = USER.LOCATION_ID
I tried adding an order by asc and no go
Whoops. got it.. order by NAME - barchetta 1 year ago