How to find COE security policies which has no groups attached to it

[kartikey]

Hi Everyone,

I have hundreds on COE security policies set up on my instance, i wish to find COE security policy which has no groups linked to it so that i can deactivate/delete those.
Now i know groups in coe security policy are from a related list 'sn_hr_core_m2m_security_policy_group' so i created a database view between 'sn_hr_core_m2m_security_policy_group' and 'sn_hr_core_coe_security_policy' 

But the records that i'm getting are the ones which already has groups , maybe because of my where clause, 
can someone help me with this?

[axpo-marcburk]

Using the gear icon, add the column "Left join" to the view. Then set it to true.

[VaranAwesomenow]

@kartikey  if you look at this data model, COE Security Policy column in table  sn_hr_core_m2m_security_policy_group refers to sn_hr_core_coe_security_policy table. In order to find out COE security policies without a group mapping create a DB view with left join

you DB view would look like below.

You can filter the list of records where group is empty which would give you the list of COE Security policies with missing groups.

I made a youtube video explaining the solution

https://youtu.be/N3SjFDprxPw

and here is the updateset of database view : communitycode/COE Policy With No Groups.xml at master · anilvaranasi/communitycode