Group Membership Audit Reporter

Overview

FlowLibs - Group Membership Audit Reporter runs every Monday at 8:00 AM Eastern, reads a SharePoint list of approved Entra ID security-group memberships, fetches the current member list of each group from Microsoft Entra ID, compares the two sets, and emails the Security Team a single HTML report listing every drift — both unauthorized members (in the group but not on the approved list) and missing members (on the approved list but not in the group).

The flow is the canonical "weekly compliance scan with single-email digest" pattern for FlowLibs: scheduled trigger, SharePoint as the system-of-record for expectations, Entra ID as the system-of-record for actuals, comma-wrapped CSV for word-boundary-safe set comparison, two pre-filtered iterators that build HTML rows for each discrepancy class, and one consolidated email at the end.

Use Case

Who: IT Admins, Security & Compliance Operations. When: weekly governance audit of privileged group memberships. Why: groups drift over time as people join, leave, change roles, or get added ad hoc. Without a periodic comparison against an authoritative "should be" list, drift goes unnoticed until the audit. This flow turns the audit from "manually export memberships and diff them in Excel" into "open the Monday morning email and triage."

The flow is intentionally *report-only* — it does not auto-remove unauthorized members or auto-add missing ones. Surface, don't enforce. Remediation stays in human hands so that a misconfigured approved list cannot trigger a self-inflicted access incident.

The flow is ideal for teams that:

• IT Admins running weekly governance audits of privileged group memberships
• Security & Compliance Operations teams who need to detect drift between approved and actual membership
• Organizations that prefer surface-don't-enforce reporting so a misconfigured approved list cannot trigger a self-inflicted access incident
• Teams replacing manual Excel-diff audits with a single Monday-morning triage email

Flow Architecture

Environment Variables

Connectors & Connections

Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.

Customization Guide

Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.

Change the cadence

Edit the Recurrence trigger — adjust frequency (Day / Week / Month), interval, schedule.weekDays / hours / minutes. Keep within Eastern Standard Time or swap timeZone for another Windows time-zone identifier.

Add or remove groups

Add or delete rows in the SharePoint approved-memberships list. No flow edits required — the flow re-reads the list on every run.

Tighten or loosen the approved-list parsing

Today's flow lowercases the ApprovedMembers cell and strips spaces before splitting on commas. To change the delimiter (semicolon, newline) or preserve casing for case-sensitive directories, edit Compose_Approved_Members_Csv and Compose_Approved_Members_Array. The CSV-wrap (leading + trailing comma) and word-boundary contains() check stay the same.

Switch from email to Teams or Adaptive Card

Replace Send_Audit_Report_Email with shared_teams/PostMessageToConversation (slash-path body/recipient/groupId, body/recipient/channelId, body/messageBody) and re-template the HTML to fit the Teams preview. Or add an Approvals action in front of the email so the digest needs an admin click before going out.

Persist the audit results

Append a Dataverse CreateRecord action after the email pointing at a new flowlibs_groupauditrun table with columns for run date, groups audited, total discrepancies, and a JSON snapshot of the discrepancy rows — gives the security team a queryable history of past scans.

Remediate inline (advanced — not recommended for a first deploy)

Inside the unauthorized-members Foreach, add an Entra ID RemoveMemberFromGroup action gated by an Approvals card. Inside the missing-members Foreach, add AddMemberToGroup. Switching from report-only to enforcement is a meaningful risk shift — pair with a "dry run" env var that defaults to true so the first weeks ship as report-only and only flip to enforcement after the team is comfortable with the discrepancy volume.

Key Expressions

The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.

concat(',', join(<array>, ','), ',')

contains(varCsv, concat(',', target, ','))

coalesce(body('Get_Approved_Memberships_From_SharePoint')?['value'], createArray())

toLower(coalesce(item()?['userPrincipalName'], item()?['mail'], ''))

toLower(replace(coalesce(items('Apply_To_Each_Approved_Group')?['ApprovedMembers'], ''), ' ', ''))

concat('[FlowLibs] Group Membership Audit - ', variables('varTotalDiscrepancies'), ' discrepancies across ', variables('varGroupsAudited'), ' groups')