Complete Audit/Scope of a Google Workspace Tenent

[Dirk Grobler]

Hi everyone,
I’ve been working on a Windows Batch script designed to perform an automated, complete scoping audit of a Google Workspace tenant. The primary goal is to gather all data, security configurations required ahead of a GWS-to-GWS tenant migration.

I’m sharing the workflow here in case it’s helpful for others planning migrations, and to get some feedback on a few performance/issues handling some sections.

What the Script Does:
The batch file executes a series of GAM commands to export CSV reports into a local directory (Scope_Output), and then creates a Master Google Sheet in the target tenant Drive, appending each module as a separate tab.

Where I Need Guidance:
The modules that I haven't figured out completely yet, and would like some guidance on, are:
Module 14: Shared With Me Files
Module 15: Outbound Externally Shared Drive Files

Currently, these modules don't filter strictly on only files shared from external sources (inbound) and files shared to external parties (outbound). This causes massive CSV files in some cases that exceed the upload limits for Google Sheets. Additionally, Module 15 is currently missing Shared Drive files shared externally.

Specifically, I'm looking for advice on:

• the cleanest way to restrict Module 14 to only inbound external files and Module 15 to only outbound external files (including Shared Drives) so internal files don't bloat the output?
• Handling Large Audits & Cell Limits

Script:

@echo off
setlocal enabledelayedexpansion

echo ==================================================
echo        GWS Tenant Scoping Workspace Batch
echo ==================================================
echo.

rem Prompt 1: Migration/Admin user email
set /p "MIGRATION_USER=Enter the Migration/Admin Email for this tenant: "

rem Prompt 2: Custom Sheet Title (with default fallback)
set "REPORT_TITLE=GWS_Tenant_Scope_Report"
set /p "USER_TITLE=Enter Google Sheet Title [Default: GWS_Tenant_Scope_Report]: "
if not "%USER_TITLE%"=="" set "REPORT_TITLE=%USER_TITLE%"

set "OUTPUT_DIR=%~dp0Scope_Output"

rem Prepare clean local directory for CSVs
if exist "%OUTPUT_DIR%" rmdir /s /q "%OUTPUT_DIR%"
mkdir "%OUTPUT_DIR%"

echo.
echo --------------------------------------------------
echo Configuration Selected:
echo User Email  : %MIGRATION_USER%
echo Sheet Title : %REPORT_TITLE%
echo Working Dir : %OUTPUT_DIR%
echo --------------------------------------------------
echo Starting scoping process...
echo.

rem ==========================================
rem 1. Addresses
rem ==========================================
echo Extracting Addresses...
gam redirect csv "%OUTPUT_DIR%\Addresses.csv" print addresses

rem ==========================================
rem 2. Resources
rem ==========================================
echo Extracting Resources...
gam redirect csv "%OUTPUT_DIR%\Resources.csv" print resources allfields

rem ==========================================
rem 3. Shared Drives
rem ==========================================
echo Extracting Shared Drives...
gam redirect csv "%OUTPUT_DIR%\Shared_Drives.csv" print shareddrives fields id, name
copy "%OUTPUT_DIR%\Shared_Drives.csv" .\gwstenantsd.csv >nul

rem ==========================================
rem 4. Shared Drive ACLs
rem ==========================================
echo Extracting Shared Drive ACLs...
gam config csv_output_header_filter "id, name, permission.emailAddress, permission.role" redirect csv "%OUTPUT_DIR%\Shared_Drive_ACLs.csv" print teamdriveacls oneitemperrow

rem ==========================================
rem 5. Shared Drive Activity
rem ==========================================
echo Extracting Shared Drive Activity...
gam config csv_output_header_filter "name, id.time, shared_drive_id, shared_drive_name" redirect csv "%OUTPUT_DIR%\Shared_Drive_Activity.csv" multiprocess redirect stderr multiprocess csv .\gwstenantsd.csv gam report drive filter "shared_drive_id==~~id~~" events view,preview,create,delete,edit,copy,upload,move,rename,trash maxactivities 1 shownoactivities addcsvdata shared_drive_id "~id" addcsvdata shared_drive_name "~name"

rem ==========================================
rem 6. Chat Spaces Settings
rem ==========================================
echo Extracting Chat Spaces Settings...
gam redirect csv "%OUTPUT_DIR%\Chat_Spaces_Settings.csv" user %MIGRATION_USER% print chatspaces

rem ==========================================
rem 7. Chat Spaces Members
rem ==========================================
echo Extracting Chat Spaces Members...
gam redirect csv "%OUTPUT_DIR%\Chat_Spaces_Members.csv" user %MIGRATION_USER% print chatmembers

rem ==========================================
rem 8. Group Settings
rem ==========================================
echo Extracting Group Settings...
gam redirect csv "%OUTPUT_DIR%\Group_Settings.csv" print groups name description settings

rem ==========================================
rem 9. Group Memberships
rem ==========================================
echo Extracting Group Memberships...
gam redirect csv "%OUTPUT_DIR%\Group_Memberships.csv" print group-members fields role,type,email,status

rem ==========================================
rem 10. Google Sites
rem ==========================================
echo Extracting Google Sites...
gam config auto_batch_min 1 num_threads 10 redirect csv "%OUTPUT_DIR%\Google_Sites.csv" multiprocess all users print filelist fields id,name,modifiedTime,lastModifyingUser.emailAddress,mimeType fullpath showmimetype gsite

rem ==========================================
rem 11. SendAs Configuration
rem ==========================================
echo Extracting SendAs Config...
gam redirect csv "%OUTPUT_DIR%\SendAs_Config.csv" all users print sendas compact verifyonly

rem ==========================================
rem 12. Delegation Configuration
rem ==========================================
echo Extracting Delegation Config...
gam redirect csv "%OUTPUT_DIR%\Delegations.csv" all users print delegates

rem ==========================================
rem 13. Gmail Forwarders
rem ==========================================
echo Extracting Gmail Forwarders...
gam redirect csv "%OUTPUT_DIR%\Gmail_Forwarders.csv" multiprocess all users print forwardingaddresses

rem ==========================================
rem 14. Shared With Me Files
rem ==========================================
echo Extracting Shared With Me Files...
gam config auto_batch_min 1 num_threads 10 redirect csv "%OUTPUT_DIR%\Shared_With_Me.csv" multiprocess all users print filelist fullquery "sharedWithMe=True and not 'me' in owners" fields id,name,mimeType,sharedWithMeTime,owners.emailAddress,webViewLink,permissions

rem ==========================================
rem 15. Outbound Externally Shared Drive Files
rem ==========================================
echo Extracting Outbound Externally Shared Drive Files...

echo Fetching registered domains for this tenant...
set "DOMS="

rem Parse domain list directly into comma-separated string with NO spaces
for /f "tokens=1 delims=," %%D in ('gam print domains ^| findstr /v /i "domainName"') do (
    set "RAW_DOM=%%~D"
    set "RAW_DOM=!RAW_DOM:"=!"
    set "RAW_DOM=!RAW_DOM: =!"
   
    if not "!RAW_DOM!"=="" (
        if "!DOMS!"=="" (
            set "DOMS=!RAW_DOM!"
        ) else (
            set "DOMS=!DOMS!,!RAW_DOM!"
        )
    )
)

if "!DOMS!"=="" (
    echo [WARNING] Could not extract tenant domains automatically.
    echo Skipping External Drive Shares extraction.
) else (
    echo Detected Tenant Domains: !DOMS!
    echo.
    echo Running Outbound External Drive Shares extraction...
    echo --------------------------------------------------

    gam config auto_batch_min 1 num_threads 10 redirect csv "%OUTPUT_DIR%\Drive_External_Shares.csv" multiprocess all users print filelist fields id,name,basicpermissions,mimeType,owners.emailAddress,webViewLink filepath pm not domain "!DOMS!" em pmfilter oneitemperrow

    if exist "%OUTPUT_DIR%\Drive_External_Shares.csv" (
        echo [SUCCESS] External shares extracted to: "%OUTPUT_DIR%\Drive_External_Shares.csv"
    ) else (
        echo [ERROR] Drive_External_Shares.csv was not created.
    )
)

rem ==========================================
rem 16. User Identity & Licenses
rem ==========================================
echo Extracting User Identity & Licenses...
gam redirect csv "%OUTPUT_DIR%\User_Identity_Licenses.csv" print users fields primaryEmail,suspended,lastLoginTime,fullname,orgUnitPath,creationTime licenses

rem ==========================================
rem 17. Calendar ACLs
rem ==========================================
echo Extracting Calendar ACLs...
gam redirect csv "%OUTPUT_DIR%\Calendar_ACLs.csv" redirect stderr NUL multiprocess all users print calendaracls

rem ==========================================
rem 18. User OAuth Tokens
rem ==========================================
echo Extracting User OAuth Tokens...
gam redirect csv "%OUTPUT_DIR%\User_OAuth_Tokens.csv" multiprocess all users print tokens

rem ==========================================
rem 19. Vault Holds
rem ==========================================
echo Extracting Vault Holds...
gam redirect csv "%OUTPUT_DIR%\Vault_Holds.csv" print vaultholds

rem ==========================================
rem 20. Vault Matters
rem ==========================================
echo Extracting Vault Matters...
gam redirect csv "%OUTPUT_DIR%\Vault_Matters.csv" print vaultmatters

echo --------------------------------------------------
echo.
echo --------------------------------------------------
echo Creating Master Google Sheet and uploading tabs...
echo --------------------------------------------------
echo.

rem Step A: Create Master Google Sheet from Addresses.csv
gam user %MIGRATION_USER% create drivefile localfile "%OUTPUT_DIR%\Addresses.csv" drivefilename "%REPORT_TITLE%" mimetype gsheet > temp_out.txt 2>&1
type temp_out.txt

set "SHEET_ID="

for /f "tokens=5 delims=/" %%A in ('findstr /C:"https://docs.google.com/spreadsheets/d/" temp_out.txt') do (
    set "SHEET_ID=%%A"
)

if "%SHEET_ID%"=="" (
    for /f "tokens=2 delims=()" %%A in ('findstr /C:"Drive File:" temp_out.txt') do (
        set "SHEET_ID=%%A"
    )
)

del temp_out.txt

if "%SHEET_ID%"=="" (
    echo [ERROR] Failed to extract Sheet ID. Aborting upload.
    pause
    exit /b
)

echo.
echo Master Sheet Created Successfully! ID: %SHEET_ID%
echo Appending remaining CSV tabs...
echo.

rem Step B: Loop through all remaining CSVs using `retainname`
for /f "delims=" %%F in ('dir /b /a-d "%OUTPUT_DIR%\*.csv"') do (
    set "RAW_NAME=%%~nF"
    if not "!RAW_NAME!"=="Addresses" (
        echo Uploading tab: !RAW_NAME!...
        gam user %MIGRATION_USER% update drivefile "%SHEET_ID%" retainname localfile "%OUTPUT_DIR%\%%F" addsheet "!RAW_NAME!"
    )
)

echo.
echo ==================================================
echo Batch process complete!
echo Title: "%REPORT_TITLE%"
echo Sheet ID: %SHEET_ID%
echo Sheet URL: https://docs.google.com/spreadsheets/d/%SHEET_ID%/edit
echo Check Google Drive under user: %MIGRATION_USER%
echo ==================================================
pause

[Paul Ogier]

Hi Dirk,

The bloat in Module 15 is coming from the permission match itself. In pm not domain “!DOMS!”, the domain keyword takes a single regex pattern, not a comma-separated list, so “domain1.com,domain2.com” never matches any permission, and “not” then inverts that into matching every ACL on every file, internal ones included. The list form is a separate keyword: notdomainlist.

The Permission Matches wiki page also confirms domainlist/notdomainlist tests, for types user and group, the domain name in the email address, so one clause covers direct external user/group shares as well as external domain-wide grants. For outbound external only, something like:

gam … print filelist fields id,name,mimeType,owners.emailAddress,basicpermissions filepath pm typelist user,group notrole owner notdomainlist !DOMS! em pm type domain notdomainlist !DOMS! em pm type anyone em pmfilter oneitemperrow

The three matches OR together (pmm or is the default): external users/groups, external domain grants, and anyone-with-link. pmfilter then only emits the matched ACLs, so internal permissions stop inflating the CSV.

Module 14 has a documented one-liner. The wiki’s example for files owned by someone outside your domain is pm type user role owner notdomain mydomain.com em, so keep your fullquery and add:

pm type user role owner notdomainlist !DOMS! em pmfilter

and drop the full permissions field from the output. owners.emailAddress carries the answer, and the permissions blob is most of your file size.

For Shared Drives in Module 15: print filelist has no adminaccess option. It always runs as the named user, and against a drive that user is not a member of it silently returns zero rows, which in a scoping audit reads as “no external shares”. Since you already export gwstenantsd.csv, loop it like your Module 5: add the migration user to each drive first (gam csv gwstenantsd.csv gam add drivefileacl “~id” user youradmin role organizer adminaccess), run the same filtered filelist with select shareddriveid “~id”, then delete those ACLs again. Drive-level external members you already have from Module 4’s teamdriveacls.

On limits: Sheets caps at 10 million cells including CSV imports, and oneitemperrow multiplies rows per file, so even filtered, the two Drive modules are the ones that will blow that on a big tenant. I would leave those as plain CSVs in Drive (create drivefile without the gsheet mimetype) and link them from the master sheet instead of adding them as tabs.

Paul

[Dirk Grobler]

Hi Paul,

Thank you so much for the recommendations

Switching to notdomainlist and dropping the permissions field made a massive difference in runtime, file size, and filtering.

Kind Regards,

Dirk

[Paul Ogier]

You are very welcome Dirk. 

You asking about this, reminded me that I intended to release a new script that we have been using internally with our customer for about 6 months. https://github.com/PaulOgier/GAMScripts/tree/main/Tenant%20Scoping%20Audit

We use this when we onboard a new client to see how bad the tenant is and what we need to repair. 

It is not fully complete and I have been tiding it up, but it might help you with your script as well. Yours is looking good, I am not really a fan on bat scripts as they seem to break for me.