How to Keep LDAP Filter in Sync With the KACE SMA Appliance User Records (131519)

How to Keep LDAP Filter in Sync With the KACE SMA Appliance User Records

LDAP filters cannot effectively be used to govern help desk permissions because LDAP filters are only evaluated on the appliance users when they log in to the user portal. They are not evaluated on import of a new user, they are not evaluated on updating user via import, they are not evaluated when the user sends in an email

Scenario 1: You may try to assign a ticket to a user who is the member of the appliance help desk group, but you cannot because they did not login since they obtained this new group membership

Scenario 2: A submitter tries to email the help desk about their problem but are rejected because although they should be in the submitter label because they have not logged in to the portal they have not had the appropriate LDAP filter that would associate them to the proper label evaluated 

You can use ticket rules to keep your LDAP filters in sync with new or modified user information. As long as the user information is kept up to date with a scheduled import the rules will keep the user's labels up to date.

The basic way that this works is:

1. A scheduled LDAP import runs daily (or more often) on the groups that need to be kept up to date. Typically this means on everyone, but not necessarily.
2. When the LDAP runs it will be setup to email the special queue that we created. This email will create a ticket in that queue
3. The ticket will trigger a rule that will update any missing label information
4. The ticket will trigger a rule that will delete any obsolete label information

The complete conditions for this FAQ to work are:

• You must create a separate queue to handle the processing of this information. This queue will only have one owner and two submitters. The owner can be anyone you designate (e.g. admin). The submitters are not negotiable -- one submitter will be the same as the owner you designated and the other owner will be a special user. Details below. This separate queue will have all its email on events turned off.
• The LDAP membership information (e.g. memberOf for Active Directory) must be imported into the custom_4 field of the users that you want to update
• You must still create an LDAP filter.
  • This filter must represent the memberOf information above. The filter doesn't actually have to do any LDAP evaluations but it is recommended that it does.
  • The name of each filter must end with an underscore then the name of the LDAP group or simply be the name of the LDAP group. E.g. if the AD group is called "KACE SMA appliance Admins" then the filter can be called "ldap_K1000 appliance Admins" or "K1000 appliance Admins_" or "_K1000 appliance Admins" or "ldap filter_K1000 appliance Admin"
  • The name of the LDAP group cannot have any underscores in it. E.g. "K1000 appliance_Admins" is illegal
  • The name of the LDAP label associated to the filter cannot have any underscores in the portion of the name that matches the memberOf information. E.g. for an LDAP group called "K1000 appliance Admins" the label cannot be called "ldap_K1000 appliance_Admins" nor "ldap_filter_K1000 appliance_Admins"

The Steps

Schedule your LDAP user import

Note: an import is also an update automatically so if you are using LDAP you should have a scheduled import. Follow the documentation on this. It is set at Settings | User Authentication | Bell icon. The key elements that you need for this to work are:

• a schedule on your import (in step 1)
• a recipient (in step 1)E.g.

Scheduling: Don't Run on a Schedule Run Every at : Run on the of at :
	Recipients:	helpdesk4@K1000 appliance.questkace.local
Email Notification:

• import the memberOf attribute and map it to the custom_4 field of your user record. After an import your user record might look like this:

User Name:
Full Name:
Email:
Domain:
Budget Code:
Location:
Work Phone:
Home Phone:
Mobile Phone:
Pager Phone:
Custom 1:
Custom 2:
Custom 3:
Custom 4:

You must create an LDAP filter for your users. See documentation on this. As an example we have the following filter:

Associated Label Name:
Associated Label Notes:	Imported LDAP Label
Server Hostname:
LDAP Port Number:
Search Base DN:	DC=questkace,DC=local
Search Filter:	(&(memberOf=CN=K1000 appliance Global Admins,DC=questkace,DC=local)(samaccountname=K1000 appliance_USER_NAME))
LDAP Login:
LDAP Password:
Label Attribute:
Label Prefix:

Create a label called FilterQueueSubmitters

• Go to Home | Label | Label Management and create a new label called FilterQueueSubmitters

Create the following user in your K1000 appliance:

Go to Settings > Users and create a new user with this information (note that you will have to give values relevant to your appliance

• user name: reporter@K1000 appliance.questkace.local
• email: reporter@K1000 appliance.questkace.local
• label: FilterQueueSubmitters

Create a Special Queue

• Go to Service Desk | Configuration | Queues and add a new queue called Queue to detect ldap import
• Open the configuration page for this queue and do the following:
  • Uncheck all "Email on events" checK1000 appliancees
  • Uncheck allow all users as submitters and specify FilterQueueSubmitters
  • Uncheck Accept email from unknown users
  • Check Allow users with an Administrator role to read and edit tickets in the End User Console
  • Optional: specify a label for ticket owners

Create this rule

---

Update Query:	UPDATE HD_TICKET T JOIN USER NEWOWNER ON NEWOWNER.USER_NAME='admin' /* set replacement user here */ JOIN HD_STATUS S ON S.HD_QUEUE_ID=T.HD_QUEUE_ID and S.NAME='Closed' SET OWNER_ID=NEWOWNER.ID, HD_STATUS_ID=S.ID WHERE T.ID IN (<TICKET_IDS>)
Run an update query, using the results from the one above
Send an email for each result row
Results are tickets, add a comment to each one
Title:
Order:
Queue:	Queue to detect ldap import
Notes:	When an ldap import is run it might email this queue. The email will detect if any filters need to be updated. Also auto-close the ticket
Frequency:
Enabled:
Select Query:	select HD_TICKET.ID FROM HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID> JOIN USER SUBMITTER ON SUBMITTER_ID=SUBMITTER.ID WHERE DESCRIPTION LIKE 'Ticket Created%' and TITLE ='Scheduled User Import Summary' and SUBMITTER.EMAIL LIKE 'reporter@%'
Send query results to someone

Create Rule To Add Labels

Update Query:	INSERT INTO USER_LABEL_JT( LABEL_ID, USER_ID) select LABEL.ID, USER.ID from LABEL JOIN USER ON FIND_IN_SET(SUBSTRING_INDEX(NAME,'_',-1), REPLACE(CUSTOM_4,', ',','))>0 LEFT JOIN USER_LABEL_JT UL ON USER_ID=USER.ID and LABEL_ID=LABEL.ID WHERE UL.USER_ID IS NULL and NOTES LIKE '%Imported LDAP Label%' and NAME LIKE '%K1000 appliance%' and USER.MODIFIED > DATE_SUB(NOW(), INTERVAL 24 HOUR)
Run an update query, using the results from the one above
Send an email for each result row
Results are tickets, add a comment to each one
Title:
Order:
Queue:	Queue to detect ldap import
Notes:	updates users labels based on their memberof information that is imported into custom field #4 and the record was updated in the last 24 hours
Frequency:
Enabled:
Select Query:	select HD_TICKET.ID FROM HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID> JOIN USER SUBMITTER ON SUBMITTER_ID=SUBMITTER.ID WHERE DESCRIPTION LIKE 'Ticket Created%' and TITLE ='Scheduled User Import Summary'
Send query results to someone

Create Rule to Delete Labels

Update Query:	DELETE UL FROM USER JOIN USER_LABEL_JT UL ON UL.USER_ID=USER.ID JOIN LABEL L ON L.ID=UL.LABEL_ID WHERE NOTES LIKE '%Imported LDAP Label%' and FIND_IN_SET(SUBSTRING_INDEX(NAME,'_',-1), REPLACE(CUSTOM_4,', ',','))=0
Run an update query, using the results from the one above
Send an email for each result row
Results are tickets, add a comment to each one
Title:
Order:
Queue:	Queue to detect ldap import
Notes:
Frequency:
Enabled:
Select Query:	select HD_TICKET.ID FROM HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID> JOIN USER SUBMITTER ON SUBMITTER_ID=SUBMITTER.ID WHERE DESCRIPTION LIKE 'Ticket Created%' and TITLE ='Scheduled User Import Summary'
Send query results to someone