/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Issue with queries related to LDAP/Service Desk

03/01/2017 1294 views

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 SQL:
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 SQL:
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>

2 Comments   [ + ] Show comments

Comments

  • Do you know if the LDAP query is getting the Location field OK? Or is the LDAP query itself not returning the info it used to? You can test LDAP queries in Home > Label Management > LDAP Browser
  • I'll be honest in saying I'm not sure I know how to use that tool appropriately to find/display the Location field. That's pretty cool though!! Didn't know they had that browser available. I'll need to learn a bit more about using that.

    That said, it seems the LDAP query is pulling all account information, except the Location field. There are some new users who have all their fields except location populated.
    • Hello csninja, apologies for reaching out to you via this old thread. After our recent upgrade we're experiencing the same issue you mention in your last comment - users are stuck with unassigned location data. We've been stuck like this for almost two weeks now and Quest has been unable to resolve this issue. Was this issue resolved at your company?
      • Apologies for the late reply. At this point we've not resolved this issue. That said, we aren't on the newest version yet at this point. We'll be upgrading shortly, which could address this.

All Answers

2
Answered 03/02/2017 by: chucksteel
Red Belt

  • AWESOME!! That seems to have done the trick for my Custom Ticket fields.

    I'm having some trouble getting the Ticket Rule updated. Going to see what I can come up with on getting it working again.
  • We're going to remove that Ticket Rule and update the Department field manually.

    Would you happen to have any thoughts on getting the Location field in AD to come over in LDAP?
    • In your LDAP user import make sure that the attribute that contains your location is included in the "Attributes to receive" list. For use we use physicalDeliveryOfficeName. Then, on the define mapping page be sure to set Location to that attribute.
      • Double-checked and confirmed that's how we have it setup. Ran the import again but the Location fields still show "Unassigned".

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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