You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

This wiki attempts to describe use cases for reporting in grouper, optionally there is a description of how to write the query to solve the problem

Membership report with attributes and other memberships (as booleans)

From Chris and Shilen

For example, we have a group for the arts and sciences school, we want each membership in a row, and some other attributes, and memberships about MFA

SELECT gmlv.subject_id INSTITUTION_ID,
       gm.name NAME,
       gm.description DESCRIPTION,
       mea.net_id NET_ID,
       mea.active_code ACTIVE_CODE,
       -- see if enrolled in two step
       CASE WHEN EXISTS (SELECT 1 FROM grouper_memberships_lw_v gmlv_2step WHERE gmlv_2step.member_id = gmlv.member_id 
                          and gmlv_2step.group_name = 'app:twoFactor:enrolledUser' and gmlv_2step.list_name = 'members')
             THEN 'T' ELSE 'F' END as ENROLLED_IN_TWO_STEP,
FROM grouper_memberships_lw_v gmlv, grouper_members gm, my_extra_attributes mea
WHERE gmlv.subject_id = mea.my_id(+)                         --outer join so if they are in the group and not in attribute table they still show up
  AND gmlv.list_name = 'members'
  AND gmlv.subject_source = 'mypeople'
  AND gm.id = gmlv.member_id
  -- report has rows for each person in the arts and sciences org
  AND gmlv.group_name = 'ref:org:artsAndSciences'


Check all groups in a folder with a certain name, return all members, and if they are active employees

From Matt Black

For example, all admin groups for apps



  • No labels