SharePoint Content Sprawl Audit Framework

Set-SPOTenant -DisableCustomAppAuthentication $false

let
  token   = // bearer from Azure Function proxy,
  headers = [
    #"Authorization" = "Bearer " & token,
    #"Accept"        = "application/json;odata=nometadata"
  ],
  url      = siteUrl & "/_api/web/...",
  response = Json.Document(Web.Contents(url, [Headers = headers]))
in
  response

GET {siteUrl}/_api/web?$select=Title,Url,Created,LastItemModifiedDate,Description,MembersCanShare
GET {siteUrl}/_api/site?$select=Owner,StorageUsed,StorageUsageCurrent
GET {siteUrl}/_api/web/associatedownergroup/users?$select=Email,LoginName,Title

let
  GetAllPages = (url as text, token as text) =>
    let
      GetPage = (pageUrl as text, accumulated as list) =>
        let
          response = Json.Document(Web.Contents(pageUrl, [
            Headers = [
              Authorization = "Bearer " & token,
              Accept        = "application/json;odata=nometadata"
            ]
          ])),
          items    = response[value],
          nextLink = try response[#"odata.nextLink"] otherwise null,
          combined = accumulated & items,
          result   = if nextLink = null
                     then combined
                     else @GetPage(nextLink, combined)
        in result
    in GetPage(url, {})
in GetAllPages

GET {siteUrl}/_api/web/lists
    ?$filter=Hidden eq false
    &$select=Title,BaseTemplate,ItemCount,ContentTypesEnabled,
              HasUniqueRoleAssignments,LastItemModifiedDate,Id
    &$expand=ContentTypes($select=Name),
              Fields($filter=Hidden eq false;$select=Title)

HasCol_BusinessUnit    = "Business Unit"     in Fields[Title]
HasCol_Mine            = "Mine"              in Fields[Title]
HasCol_Geography       = "Geography"         in Fields[Title]
HasCol_DocumentStatus  = "Document Status"   in Fields[Title]
HasCol_Confidentiality = "Confidentiality"   in Fields[Title]
HasCol_RetentionLabel  = "Retention Label"   in Fields[Title]
HasCol_OwnerApprover   = "Owner"             in Fields[Title]
BaseColumnScore        = // sum of 7 flags above (0–7)
BaseColumnGapCount     = 7 - BaseColumnScore

IsExternalUser = Text.Contains(LoginName, "#EXT#") or UserType = "Guest"

IsCustomPermissionLevel = RoleDefinitionName not in
  {"Full Control","Design","Edit","Contribute",
   "Read","View Only","Limited Access",
   "Approve","Manage Hierarchy"}

GET {siteUrl}/_api/web/lists/getById('{listId}')/items
    ?$filter=HasUniqueRoleAssignments eq true
    &$select=Id,FileLeafRef,FileRef,Author/Email,
              Editor/Email,Modified,HasUniqueRoleAssignments
    &$expand=RoleAssignments/Member/Email,
              RoleAssignments/RoleDefinitionBindings,
              Author,Editor

KeywordGroup =
  if any of [contract, agreement, renewal, pricing, PLV, HCC, PCI,
              nomination, charter, reopener] in FileName
    then "Commercial"
  else if any of [earnings, EBITDA, forecast, budget, guidance,
                   acquisition, Anglo, Moranbah, bid, LOI, NDA]
    then "Financial-MNPI"
  else if any of [reserve, resource, geological, seam, bore, JORC]
    then "Geological"
  else if any of [incident, MSHA, injury, fatality, LTI, permit,
                   reclamation, bond, compliance, violation]
    then "Safety-Regulatory"
  else if any of [laycan, vessel, demurrage, disruption, outage,
                   Black Warrior, lock, BNSF, Aurizon] in FileName
    then "Logistics"
  else "General"

KeywordHitFlag = KeywordGroup <> "General"

GET /v1.0/sites?search=*&$select=id,displayName,webUrl,sharepointIds
GET /v1.0/sites/{siteId}?$select=id,displayName,webUrl,sensitivity,sharepointIds
GET /v1.0/sites/{siteId}/termStore

// Hub association: response includes isHubSite, hubSiteId properties
// isHubSite = false AND hubSiteId = null → ungoverned standalone

// Teams connection:
GET /v1.0/groups?$filter=resourceProvisioningOptions/any(x:x eq 'Team')
    &$select=id,displayName,resourceProvisioningOptions,sharepointIds

GET /v1.0/sites/{siteId}/drives
    ?$select=id,name,driveType,createdBy,lastModifiedDateTime

GET /v1.0/drives/{driveId}/items/{itemId}/permissions
    ?$select=id,roles,link,grantedTo,grantedToIdentities,
              inheritedFrom,hasPassword,expirationDateTime

IsAnonymousLink   = link/scope = "anonymous"
IsOrgWideLink     = link/scope = "organization"
IsNeverExpires    = link/expirationDateTime = null and link <> null
IsApprovedDomain  = recipientDomain in DimApprovedDomains
IsExternalUnknown = IsExternalUser and not IsApprovedDomain

let
  CallWithRetry = (url as text, token as text, maxRetries as number) =>
    let
      Attempt = (n as number) =>
        let
          resp   = try Web.Contents(url, [
            Headers = [Authorization = "Bearer " & token],
            ManualStatusHandling = {429, 503}
          ]),
          status = Value.Metadata(resp)[Response.Status],
          result = if status = 429 and n < maxRetries
                   then @Attempt(n + 1)
                   else Json.Document(resp)
        in result
    in Attempt(0)
in CallWithRetry

[_ Total Sites] = COUNTROWS(FactSiteInventory)

[_ Sites Without Sensitivity Label] =
  CALCULATE(COUNTROWS(FactSiteInventory),
            DimSensitivityLabel[LabelName] = "Unlabeled")

[_ % Sites Unlabeled] =
  DIVIDE([_ Sites Without Sensitivity Label], [_ Total Sites], 0)

[_ Sites Without Two Owners] =
  CALCULATE(COUNTROWS(FactSiteInventory),
            FactSiteInventory[HasTwoOwners] = FALSE)

[_ Libraries Without Enterprise CT] =
  CALCULATE(COUNTROWS(FactLibraryClassification),
            FactLibraryClassification[HasEnterpriseContentType] = FALSE)

[_ Libraries With Broken Inheritance] =
  CALCULATE(COUNTROWS(FactLibraryClassification),
            FactLibraryClassification[LibraryHasBrokenInherit] = TRUE)

[_ Anonymous Links Active] =
  CALCULATE(COUNTROWS(FactExternalShares),
            FactExternalShares[LinkScope] = "anonymous")

[_ Unknown External Shares] =
  CALCULATE(COUNTROWS(FactExternalShares),
            FactExternalShares[IsExternalUnknown] = TRUE)

[_ Zombie Links] =
  CALCULATE(COUNTROWS(FactExternalShares),
            FactExternalShares[IsZombieLink] = TRUE)

[_ Never-Expiring Links] =
  CALCULATE(COUNTROWS(FactExternalShares),
            FactExternalShares[IsNeverExpires] = TRUE,
            FactExternalShares[LinkScope] <> "users")

[_ MNPI Files Shared Externally] =
  CALCULATE(COUNTROWS(FactExternalShares),
            FactExternalShares[KeywordGroup] = "Financial-MNPI")

[_ OneDrive Critical Risk Files] =
  CALCULATE(COUNTROWS(FactOneDriveShadowContent),
            DimRiskTier[RiskTier] = "Critical")

[_ Governance Risk Score] =
  VAR AnonymousWeight  = [_ Anonymous Links Active]        * 10
  VAR MNPIWeight       = [_ MNPI Files Shared Externally]   * 8
  VAR UnknownExtWeight = [_ Unknown External Shares]        * 6
  VAR ZombieWeight     = [_ Zombie Links]                   * 4
  VAR NoLabelWeight    = [_ Sites Without Sensitivity Label]* 5
  VAR NoOwnerWeight    = [_ Sites Without Two Owners]       * 3
  VAR NoCTWeight       = [_ Libraries Without Enterprise CT]* 2
  RETURN
    AnonymousWeight + MNPIWeight + UnknownExtWeight +
    ZombieWeight + NoLabelWeight + NoOwnerWeight + NoCTWeight

// On DimBusinessUnit table
[BUName] = LOOKUPVALUE(
  DimUserBUMapping[BUName],
  DimUserBUMapping[UserPrincipalName], USERPRINCIPALNAME()
)

// On DimUser table — mask individual identities for execs
[UserPrincipalName] <> USERPRINCIPALNAME() &&
  FALSE()  // blocks user-level rows; aggregations roll up safely

let
  Source         = SharePointAuditTable,
  FilteredByDate = Table.SelectRows(Source, each
    [LastModifiedDate] >= RangeStart and
    [LastModifiedDate] <  RangeEnd)
in
  FilteredByDate

feat(model):    add FactOneDriveShadowContent table
fix(rls):       correct BU President filter cross-direction
perf(query):    use delta token for OneDrive enumeration
docs(measure):  add description for [_ Governance Risk Score]
chore(theme):   update gold accent to brand-compliant #e8920a