I had the challenge of creating a report for every site collection to review all external users with permissions for site collections and shared documents.

The perfect base was the “shared with external users” report. You will find the report under site settings –> site usage

The report generate a CSV file with all users in the site and there sharing permissions. The CSV file must be saved in a folder (save on DocLib root level is not possible)

Resource Path;Item Typ;Permission;User Name;User Email;User Or Group Type;Link ID;Link Type;AccessViaLinkID
sites/DataSite/Lists/6000 Items List;List;Full Control;Martin Alter;martin.alter@co*.de;External					

Generate report automatically for every site

For me, it was important to generate the report for every site in my tenant. I did the task with a Power Automate flow:

• Get all Site Collections
• check if there is a folder “report” in default DocLib –> IF Not: create the folder
• Create the Report
• If it failed send a mail

/_api/web/lists/getbytitle('DO_NOT_DELETE_SPLIST_TENANTADMIN_AGGREGATED_SITECOLLECTIONS')/items?$select=Title,SiteUrl

To work with the output we need to analyse the JSON we got from the API call

{ “type”: “object”, “properties”: { “odata.metadata”: { “type”: “string” }, “value”: { “type”: “array”, “items”: { “type”: “object”, “properties”: { “odata.type”: { “type”: “string” }, “odata.id”: { “type”: “string” }, “odata.etag”: { “type”: “string” }, “odata.editLink”: { “type”: “string” }, “Title”: { “type”: “string” }, “SiteUrl”: { “type”: “string” } }, “required”: [ “odata.type”, “odata.id”, “odata.etag”, “odata.editLink”, “Title”, “SiteUrl” ] } } } }

After we got all the site collections we can loop through and check if there is a folder “report” in “Shared Documents”. To do this we need to get the ServerRelativeUrl

In the end, we create the report If the folder exists. If not we create the folder and after that we create the report.

Details

1. Configure run after between “API get Folder Report 2” and “Bedingung”

2. Schema – JSON analysieren – API Get Folder report 2

3. Create Report / Create Report 2