Grouper reporting use cases

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 PERSON_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 gmlv2 WHERE gmlv2.member_id = gmlv.member_id 
                          and gmlv2.group_name = 'app:twoFactor:enrolledUser' and gmlv2.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

SELECT gmlv.group_name GROUP_NAME,
       gmlv.subject_id PERSON_ID,
       gm.name NAME,
       gm.description DESCRIPTION
       -- see if active employees
       CASE WHEN EXISTS (SELECT 1 FROM grouper_memberships_lw_v gmlv2 WHERE gmlv2.member_id = gmlv.member_id
                          and gmlv2.group_name = 'ref:payroll:activeEmployee' and gmlv2.list_name = 'members')
             THEN 'T' ELSE 'F' END as ACTIVE_EMPLOYEE,
FROM grouper_memberships_lw_v gmlv, grouper_members gm
WHERE gmlv.list_name = 'members'
  AND gmlv.subject_source = 'mypeople'
  AND gm.id = gmlv.member_id
  -- all groups with name 'admins' in the 'apps' folder
  AND gmlv.group_name like 'app:%:admins'

Check for the presents or absents of a group (based on a specific name) inside a folder (and it's sub folders) and return a report of folders with (or without) the group and membership of the group if it was found. ( Then email to the membership of a "report send to group" )

"Full sync" / "Full check" reporting is also a use case as well. The report can ( based on config values at run time) either report all memberships ( on both sides) or just output deltas. ( When doing a "check" no changes are made, and when doing a "sync" mode.)

"Full sync" reporting is also a use case as well. The report can ( based on config values at run time) either report all memberships ( on both sides) or just output deltas and make corrections during the process.

"Full check" reporting is also a use case as well. The report can ( based on config values at run time) either report all memberships ( on both sides) or just output deltas. ( When doing a "check" no changes are made on either side.)

Page tree