Issue with queries related to LDAP/Service Desk
We've recently updated the k1000 to version 7.0.121306. Unfortunately some queries we had working before the update are no longer working.
If I bring up a User account which has been imported from AD it's no longer specifying the Location. I have this field mapped to "physicalDeliveryOfficeName" in the query. It's coming up as Unassigned for any new users imported. The Custom_1 Field for the user accounts, which I have mapped to "department" in the query, lists the correct Department name.
Within the Service Desk it's also failing to auto-populate the fields I've setup for Department and Location. Below is the setup for these custom fields as well as the ticket rule.
Ticket Label "Location" = Ticket Custom_1
Ticket Label "Department" = Ticket Custom_2
Ticket field "Custom_1" = query: select distinct(LOCATION) from USER
Ticket field "Custom_2" = query: select distinct(CUSTOM_1) from USER
Within the ticket, if those users try to select the location, or department they receive this messages: "An SQL error occurred in generating the list: select distinct(LOCATION) from USER" and "An SQL error occurred in generating the list: select distinct(CUSTOM_1) from USER".
The Ticket Rule I have in place to Auto-populate the Department field is:
SELECT HD_TICKET.ID AS ID FROM HD_TICKET
JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 6 and HD_TICKET.SUBMITTER_ID <>0)
UPDATE HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE2 = S.CUSTOM_1 WHERE T.ID=<TICKET_IDS>