We have an dynamic rule which has 700+ users but when we test the condition it has 270 objects so dynamic schedule is not deleting users from org table who are not matching the where clause there by leading to having membership instead not matching requirements of business.
We could see DB queue performing dynamic clock schedule successfully but not removing users from rule.
Could you please check and let us know how we can troubleshoot the issue.
Where clause is as below, please let us know if its best practice to have these conditions in query.
from Person p
join *****_ControlStructure c on p.*****_UID_ControlStructure = c.UID_*****_ControlStructure
join ADSAccount a on p.UID_Person = a.UID_Person
join ADSContainer ac on a.UID_ADSContainer = ac.UID_ADSContainer
-- Check the market the person belongs to
where (c.*****_Fullpath like '****\AMS\Brazil\BR-Brasilia-GRBS\%'
or c.*****_FullPath like '***\AMS\Brazil\BR-Curitiba-GRSU\%'
or c.*****_FullPath like '*****\AMS\Brazil\BR-Recife-GRNE\%'
or c.*****_FullPath like '*****\AMS\Brazil\BR-RioDeJaneiro-GSRJ\%'
or c.*****_FullPath like '*****\AMS\Brazil\BR-VilaVelha-Site\%')
and ac.CanonicalName <> '*****.com/Organizations/AMS/Brazil/Users and Groups/BR-Generic-Users'
and ac.CanonicalName <> '*****.com/Organizations/AMS/Brazil/Users and Groups/BR-Partner-Users'
-- Person should have a full managed AD account
and exists (select itdatausage from TSBBehavior t where t.uid_tsbbehavior = a.uid_tsbbehavior and itdatausage = 1)
-- Person should not have a certain AD group
and not exists
from ADSAccountInADSGroup aig
join ADSGroup g on g.UID_ADSGroup = aig.UID_ADSGroup
-- The AD Groups the user should not have
where (g.cn = 'BRAZIL: The *****t Inclusion')
and a.UID_ADSAccount = aig.UID_ADSAccount
and Person.UID_Person = p.UID_Person