Grouper Developers Forum

[Chris Hyzer <>]

Hey,

 

Penn had three composite groups that didn’t have correct members.  I added some queries to the bad membership finder wiki that quickly shows if you have any (oracle, and depends on the v2.0 grouper_memberships_lw_v, which you could install in 1.6 below (try in test first :) ))

 

https://spaces.internet2.edu/display/Grouper/Bad+Membership+Finder+Utility

 

I added a jira that we could turn these queries into views, and have the WS status checker (which could be hooked up to monitoring software like nagios), to periodically run these queries to see if there are any discrepancies:

 

https://bugs.internet2.edu/jira/browse/GRP-575

 

Thanks,

Chris

 

 

 

CREATE OR REPLACE VIEW GROUPER_MEMBERSHIPS_LW_V

(SUBJECT_ID, SUBJECT_SOURCE, GROUP_NAME, LIST_NAME, LIST_TYPE,

 GROUP_ID, member_id)

AS

select distinct gm.SUBJECT_ID, gm.SUBJECT_SOURCE, gg.name as group_name, gfl.NAME as list_name,

gfl.TYPE as list_type, gg.ID as group_id, gm.ID as member_id from grouper_memberships_all_v gms, grouper_members gm,

 grouper_groups gg, grouper_fields gfl where gms.OWNER_GROUP_ID = gg.id and gms.FIELD_ID = gfl.ID

 and gms.MEMBER_ID = gm.ID and gms.IMMEDIATE_MSHIP_ENABLED = 'T'

/

 

COMMENT ON TABLE GROUPER_MEMBERSHIPS_LW_V IS 'Grouper_memberships_lw_v unique membership records that can be read from a SQL interface outside of grouper.  Immediate and effective memberships are represented here (distinct)'

/

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_LW_V.GROUP_ID IS 'GROUP_ID: uuid of the group'

/

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_LW_V.SUBJECT_ID IS 'SUBJECT_ID: of the member of the group'

/

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_LW_V.SUBJECT_SOURCE IS 'SUBJECT_SOURCE: of the member of the group'

/

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_LW_V.GROUP_NAME IS 'GROUP_NAME: system name of the group'

/

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_LW_V.LIST_NAME IS 'LIST_NAME: name of the list, e.g. members'

/

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_LW_V.LIST_TYPE IS 'LIST_TYPE: type of list e.g. access or list'

/

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_LW_V.member_id IS 'MEMBER_ID: uuid of subject in grouper'

/