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
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
ITMS 8.5 RU4, 8.6 GA
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.
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:
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.