AD Import for User Resources with Security Groups is creating duplicate user entries in Inv_Security_Groups Table

Products

• IT Management Suite

Issue/Introduction

The customer noticed that their AD Import Rules, especially for Users resources, that use Security Groups takes more than 30 minutes to process and/or timeout.

No errors in SMP Logs.

One log entry stating that the rule is already running when the same AD Import Rule tries to run again as scheduled:

Directory import will not start, the rule is already running: 'Import User resources from WestWing.us.domain.net, from Support_Team/Security Groups, Support_Team-USDA/Security Groups, Lab_Users/Security Groups... (12) and using the default column mappings and these resource associations. Import all users on the spec' (908832c7-7b7c-4a14-bd33-0b63a71ae383)

When checking the "Inv_Security_Groups" table,  the customer had 151 Million rows in the database.  2 days later, they have 161 Million rows.

Steps to duplicate

1. Create some OUs in AD and place a few users in each OU.
2. Add users to various Security Groups
3. Create a Users AD Import rule and specify the Security Groups you want to import.
4. Run a full Import.
5. Look at the contents of the table to verify the import
6. Run a full import again.
7. The Inv_Security_Groups table will have multiple entries.

Also, remove all but one of the Security Groups from the Import rule. Run the rule. The rule will import the users contained in the remaining Security Group from all of the Security Groups that the users are listed in.

 

How to validate the issue:

The following SQL queries should show you if you have this issue after running your AD import rule:

--Check how big this table is (how many rows it has)

select count * [total rows] from Inv_Security_Groups

--Count members of groups

select distinct [Group Name] ,count(_id) as qty

select top 10 * from Inv_Security_Groups
group by [Group Name]
order by count(_id) desc

 

--The following SQL Query will show the duplicate entries.

select * from Inv_Security_Groups
order by 4, 2

 

Cause

The issue is caused when there are multiple security groups per resource. Most customers don't see this issue if their users are only included in one security group.

Environment

ITMS 8.5 RU4, 8.6 GA

Resolution

This issue has been reported to the Broadcom Development team. A fix has been added to our next release, ITMS 8.6 RU1. It has been added a cleanup procedure for this "security groups" data class.

For those with 8.5 RU4 and 8.6 GA, the following pointfix is available:

1. Download the files: "sp_Inv_Security_Groups_clean.sql" and "Inv_Security_Groups_deduplicate.sql"
2. Run "sp_Inv_Security_Groups_clean.sql" from SQL management studio and check that sp_Inv_Security_Groups_clean.sql is up to date. 
3. Run "Inv_Security_Groups_deduplicate.sql" and check that now "Inv_Security_Groups" SQL table contains only unique data without any duplicate records even if you run again "AD import" rule to import users from security groups.

After the "sp_Inv_Security_Groups_clean" stored procedure is updated and the next AD import rule run, it will remove duplicate unnecessary records from "Inv_Security_Groups" SQL table and will contain unique records which were gathered from AD and imported into the database from appropriate security groups.