List files shared from suspended accounts that have been modified after the owner's account was suspended

[Neil - UoY]

When people leave our organisation we suspend their accounts for a period of 365 days before deleting them. The issue we’re facing is that some files are still being shared from these users’ Google Drives without the person accessing the file knowing the file is owned by a person who has left the organisation. The person accessing the file is not aware that the file is at risk of being deleted at some point in the future when the owner’s account is deleted.

I’ve been struggling with gamadv-xtd3 to report on these files. I’ve detailed my approach below (it may well not be the best way to do this. . . )

Many organisations have these sort of issues; I started here in trying to identify the files that are at risk:

https://groups.google.com/g/google-apps-manager/c/kO0w3azhaAY?pli=1

I’ve had to break this down and run this on a per user basis as due to the amount of the information retrieved I can’t do all files from all accounts at once as GAM runs into memory issues.

(Please excuse the inelegant and inefficient code below :-) )

gam.exe redirect csv ./SuspendedUsersAll.csv multiprocess all users_susp print fields username

SETLOCAL EnableDelayedExpansion

for /f "Tokens=* Delims=" %%x in (SuspendedUsersAll.csv) do (

  set User=!Build!%%x 

  Echo %%x

gam.exe config auto_batch_min 1 csv_output_header_drop_filter "'.*photoLink'" user %%x print filelist pm not role owner em fields id,name,permissions > SuspendedFiles.csv

python.exe MakeOneItemPerRowACLs.py ./SuspendedFiles.csv >> SuspendedFileACLs.csv

)

grep -v "Owner,id,title," SuspendedFileACLs.csv > SuspendedFileACLs01.csv

type SuspendedFileACLs01.csv | awk 'BEGIN { FS = ","} {print $1","$2}' | sort | uniq > SuspendedFileIDs.csv

This generates a (long) csv of the owner and the ID of all the files being shared from suspended accounts.

My next step is to produce a list of files that have been modified where the last modification date is after the last login date of a suspended owner of the file. I’d like the name of the account that modified the file too. (I would use ‘last accessed date’ but that’s not recorded so ‘last modified date’ will suffice here)

How would I do this? Here’s some ‘pseudo’ GAM that hopefully describes what I’m trying to do:

gam.exe user fr...@bloggs.com fileinfo id 1234567dsgdfg4645765 print owner, title, id, lastModifyingUserName, modifiedDate where modifiedDate > owners’ last_logon_time

Is this achievable using the approach I've taken? Is there a better way to do this?

Cheers

Spike

[Eric Dannewitz]

Maybe transfer all the user's files to a Shared Drive? That might be easier.....

[Ross Scroggs]

Spike,

I missed this when first posted, send me a Meet/Zoom invitation to discuss.

Ross

----

Ross Scroggs

Ross.S...@gmail.com

--
You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/9d58c5ca-b422-4043-af3d-1e4113438248n%40googlegroups.com.

[Neil - UoY]

Ross, many thanks for your kind offer. I'll email you directly in a couple of weeks or so.

Don't worry about missing my first post. It was just a rambling mess. . . I had good intentions but didn't articulate them very well :-)

One of the key things I've been waiting for is for Google to fix https://issuetracker.google.com/issues/68197197 (the date an account was suspended was not being reported if the account was suspended after September 2021 (that's 21 NOT 22!)). It finally appears that this has been resolved (or at least it worked for me this morning!) Without the date the an account was suspended what I was trying to achieve was a bit of a non-starter.

Cheers
Spike

[anthony jimenez]

Hi Neil,

did you ever get a command for this topic? 

[Neil - UoY]

I did, but it's a script it's not a simple as just a command. There are a number of challenges with what I was trying to do. I took 'an approach', it's not perfect, doesn't log 100% of the events but gives me enough insight to prompt further investigations where needed. I'd be very interested in people's comments but as my approach is a bit 'niche' I don't expect much feed back. Ross has offered a Zoom to discuss but I needed to understand what we can do first. Let me try and articulate the issue and my explain my approach to solving it. . .

TL;DR
The result is I get is a csv named after the email address of the user. It contains a merge of a drive report for activities such as edit, move, view, change_user access, remove_from _folder, source_copy, sheets_import_range etc, who the actor was (email address), who the file owner of the file is and when their account was suspended. This data comes from different GAM commands producing CSVs so CSVs are joined using csvkit ( https://csvkit.readthedocs.io/en/latest/ ) to produce one output file.

owner
doc_id
doc_title
doc_type
name
id.time
actor.email
type
accounts:is_suspended
accounts:first_name
accounts:last_name
accounts:admin_set_name
accounts:creation_time
accounts:disabled_time
accounts:disabled_reason
accounts:last_login_time
gmail:last_access_time
gmail:last_imap_time
gmail:last_interaction_time
gmail:last_webmail_time
accounts:drive_used_quota_in_mb
accounts:gmail_used_quota_in_mb
accounts:gplus_photos_used_quota_in_mb
accounts:used_quota_in_mb
accounts:used_quota_in_percentage

The problem
We suspend accounts for 365 days when people leave; they are then deleted.
The problem we face  is that leavers don't always transfer their Google Drive Files to another owner or move them to a Google Shared Drive before leaving. As a result files that they own and have shared are accessible to the 'sharees' for 365 days before being deleted. This can result in data being lost.

Task: Produce a report of files that have been accessed (not by the owner) after an account has been suspended.
Issues: Drive logs only go back 6 months, we suspend accounts for 12 months so we can't see everything.
Without filtering the report could contain activity from before the account was suspended.
Using drive reports results in completely different fields being retuned depending on the action (view, edit, ACL change etc) carried out on the file. I want a single CSV as the output  so only 'common' report fields are retained. This means that some actions are not reported upon; but enough are to gain an insight into if there's an issue with people accessing files that will be deleted in the future.

My proof of concept is in windows batch. It uses some windows ports of Linux commands (awk, sort, grep, uniq) and csvkit https://csvkit.readthedocs.io/en/latest/ to munge all the outputs to a single csv. ( did say it was a bit 'niche' :-) ) It's not perfect, generates some warnings, but does produce what I need as a PoC.

I'm more than happy to share. It has plenty of comments. What's the best way?

Spike