Security Update for Google Drive

[Brian Kim]

From Google's new Community, I came across this post.

https://www.googlecloudcommunity.com/gc/Workspace-Discussions/Would-scanning-Drive-for-securityUpdateEligible-True-find-the/td-p/162409

looks like you should be able to do CSV output filtering with `linkShareMetadata.securityUpdateEligible`, right?

not sure what exactly the eligibility refers to though yet.

[Brian Kim]

With Ross' update, this is where I am at so far. 

gam config csv_output_row_filter "'linkShareMetadata.securityUpdateEligible:boolean:true'" auto_batch_min 1 redirect csv - multiprocess todrive all users print filelist fields id,title,permissions,owners.emailaddress,resourcekey,linksharemetadata,mimetype query "visibility='anyoneWithLink'"

It's getting closer to the information that I see in alert center, but I have Shared Drives listed too, so I will need to scan items in Shared Drive as well. Why couldn't Google just provide the list of files?

[Ross Scroggs]

Brian,

I've made two scripts that will help in this process

https://github.com/taers232c/GAM-Scripts3/blob/master/GetLinkSharedDriveACLs.py

https://github.com/taers232c/GAM-Scripts3/blob/master/GetLinkSharedTeamDriveACLs.py

Ross

--
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/d77f04c0-4b54-4aca-ab37-9c0e398bb594n%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Brian Kim]

You're amazing, think you! I will run these and report back and see if it matches the report that Google provided.

[Mohamed]

Thank you guys so much for these scripts. What should one conclude about the files attributes?

linkShareMetadata.securityUpdateEligible
linkShareMetadata.securityUpdateEnabled

If enabled is true, the file is all set? no changes needed, and share is safe?

eligible is false means the file is safe and needs no security update? 

what would it mean when eligible is false and enabled is true?...

Also, what does it mean when both eligible and enabled are missing, show blank in the result csv.

Thanks a lot!

Mohamed.

[Ian Crew]

Thanks very much Ross!

I’m guessing this is going to take a while to run:

$ date;gam config auto_batch_min 1 redirect csv ./filelistperms.csv multiprocess all users_ns_susp print filelist fields id,name,permissions,owners.emailaddress,linksharemetadata,resourcekey query "visibility='anyoneWithLink' or visibility='domainWithLink'";date

Fri Jul  9 14:45:06 PDT 2021

Getting all Users, may take some time on a large Google Workspace Account...

Got 299281 Users...

Got 299281 Users

2021-07-09T14:53:27.720-07:00,0,Using 5 processes...

😜

(I’ll report back on the elapsed time once it completes…)

Cheers,

Ian

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAJkvRS_6NrD3UZHO6qQEb%2BNcbZiyB3S6Ly2%2B%3Dkdt7_McxRX-Dw%40mail.gmail.com.

 

Ian Crew Solutions Architect Productivity & Collaboration Services Information Services and Technology

[Ian Crew]

…and it ran out of memory (on a 32GB Linux VM) in the “ay….” accounts. Sigh. Oh well, back to the drawing board!

Ian

--

[Ross Scroggs]

Ian,

Yikes! Say you do 1000 users at a time, that's 300 bunches! Well, let's test.

gam redirect csv Users.csv all users_ns_susp print users fields primaryemail

Copy the header and 1000 users to Chunk.csv

Get the current (as of 6:30 Friday) version of the script; i've added mimeType and webViewLink to the fields

date;gam redirect csv ./filelistperms.csv multiprocess csv Chunk.csv gam user "~primaryEmail" print filelist fields id,name,permissions,owners.emailaddress,linksharemetadata,resourcekey.mimetype,webviewlink query "visibility='anyoneWithLink' or visibility='domainWithLink'";date

python GetLinkSharedDriveACLs.py filelistperms.csv linksharedperms.csv

Ross

--

ross.s...@gmail.com

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAD2sLFvc2DYpcdGEG4jx0LkbKvLLSJhR9NYgcTqZagmrUa8RWA%40mail.gmail.com.

[Kim Nilsson]

Just lurking here, since I want to know how this goes. :-)

[Ian Crew]

Thanks Ross:

Looping back to this after being on vacation for a week.

Here’s what I just tried:

gam redirect csv ./Users.csv all users_ns_susp print users fields primaryemail

head -n 1001 Users.csv > Chunk.csv

date;gam redirect csv ./filelistperms.csv multiprocess csv Chunk.csv gam user "~primaryEmail" print filelist fields id,name,permissions,owners.emailaddress,linksharemetadata,resourcekey,mimetype,webviewlink query "visibility='anyoneWithLink' or visibility='domainWithLink'”;date

date;python GetLinkSharedDriveACLs.py filelistperms.csv linksharedperms.csv;date

That seems to have worked:

$ wc -l filelistperms.csv
76134 filelistperms.csv

$ wc -l linksharedperms.csv 

77613 linksharedperms.csv

Though I don’t understand why there are more lines in linksharedperms.csv than in filelistperms.csv?

Generating the filelistperms.csv took about 12 minutes; linksharedperms.csv took about 3 minutes.

Also, another question: Is there some way to only get the files where linkShareMetadata.securityUpdateEligible is TRUE and linkShareMetadata.securityUpdateEnabled is FALSE? That’d get us down to the list of files that are actually affected by this change, as far as I understand it. (Or am I wrong about that? To be clear about my goal, I mainly want to get down to a list of users that have 1 or more files that are affected by this change, so that we can communicate with them about it.)

On a more “meta” level

This also provides an opportunity to ask a significantly more general question that I’ve long wondered about: It appears that Python (and many other scripting languages) leak memory pretty badly when asked to loop with very large numbers of repeats of the loop, even if there aren’t any saved data structures or state saved between iterations. I’ve never understood why that’s the case—is it just that it’s not great at garbage collection? Specifically, in this case, i would expect that "print filelist” would output each file it finds to the output file, then move onto the next file/user, without using additional RAM. But that doesn’t seem to be what’s actually happening. 

Thanks,

Ian

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/362F6F11-5E3A-42F2-BA2A-8CEB7E369B42%40gmail.com.

[Kim Nilsson]

Yes, Ian!

You can use csv_output_row_filter and csv_output_row_drop_filter to select the rows you want, and drop those you don't. 

[Ross Scroggs]

Ian,

date;gam config csv_output_row_filter "'linkShareMetadata.securityUpdateEligible:boolean:true','linkShareMetadata.securityUpdateEnabled:boolean:false'" redirect csv ./filelistperms.csv multiprocess csv Chunk.csv gam user "~primaryEmail" print filelist fields id,name,permissions,owners.emailaddress,linksharemetadata,resourcekey,mimetype,webviewlink query "visibility='anyoneWithLink' or visibility='domainWithLink'”;date

In filelistperms, there is one row per file with all ACLs on that row.

In linkshareperms, there is one row per file/ACL combination, so if a file has multiple link shares, it will generate multiple rows.

GAM has to keep all of the data in memory because the CSV headers aren' known until the last file is processed.

Ross

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/BAB96C92-B5A5-4C53-AAB9-D0E64C775CB5%40berkeley.edu.

--

Ross Scroggs

ross.s...@gmail.com

[Grant D]

Hello, 

So, trying to run a similar report here, with just 500 users at a time.  I'm getting the following error: 

WARNING: csv_output_row_filter column "^linkShareMetadata.securityUpdateEligible$" does not match any output columns

WARNING: csv_output_row_filter column "^linkShareMetadata.securityUpdateEnabled$" does not match any output columns

I'm able to run the gamadv command from the script GetLinkSharedDriveACLs.py, but that gives me a 18795 ./filelistpermsV2.csv

Hoping I've just got some syntax wrong. 

Thanks,

Grant 

[Ross Scroggs]

Grant,

I need to see the command you're issuing.

Ross

--

ross.s...@gmail.com

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/fbe12fb5-e1fb-4cef-ba4f-52b85f6237bdn%40googlegroups.com.

[Grant D]

Hey Ross, 

Here's the command I was using: 

gam config csv_output_row_filter "'linkShareMetadata.securityUpdateEligible:boolean:true','linkShareMetadata.securityUpdateEnabled:boolean:false'" redirect csv ./filelistperms.csv multiprocess csv ./Chunk.csv gam user "~primaryEmail" print filelist fields id,name,permissions,owners.emailaddress,linksharemetadata,resourcekey,mimetype,webviewlink  query "visibility='anyoneWithLink' or visibility='domainWithLink'"

Thanks,

Grant

[Ken Dionne]

hello,

Now that Google started rolling out the list of files, I realized that files that Google show me doesn't come out from the GAM export command.

I compared the permissions and I think I found the difference.

The files the GAM script gives me are for files that cannot be searched for in the drive, while the files Google lists are searchable :

all files that comes out from the GAM command have the allowFileDiscovery flag False, and all files that I checked manually from the Google list have the "allowFileDiscovery" flag true.

the GAM command I use is :

gam config auto_batch_min 1 redirect csv ./step1/us...@domain.com.csv multiprocess user  us...@domain.com  print filelist fields id,name,modifiedtime,mimeType,permissions,owners.emailaddress,linksharemetadata,resourcekey,webViewLink,parents query "visibility='anyoneWithLink' or visibility='domainWithLink'"

(I do each of my user individually, I have only 50)

Anyone else noted the same thing ?

How can we make sure to include the files that are allowFileDiscovery = True in the above GAM Command ?

Thanks !

Ken

[Ross Scroggs]

Ken,

This command only lists files with "visibility='anyoneWithLink' or visibility='domainWithLink'".

gam config auto_batch_min 1 redirect csv ./step1/us...@domain.com.csv multiprocess user  us...@domain.com  print filelist fields id,name,modifiedtime,mimeType,permissions,owners.emailaddress,linksharemetadata,resourcekey,webViewLink,parents query "visibility='anyoneWithLink' or visibility='domainWithLink'"

If you want to include files that allow file discovery change the query: "visibility='anyoneWithLink' or visibility='anyoneCanFind' or visibility='domainWithLink' or visibility='domainCanFind'"

Ross

Consultez notre nouveau site web ! 

Consult our new web site !

 www.concerti.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/c044b06a-0cf2-4a64-9143-5bd72a0b36d7n%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Grant D]

Thanks again, Ross for taking the time to walk through this process. 

1. Generated list of my Users. (5k)

2. Break the list of users into 1k Chunks. 

3. 

gam redirect stdout - multiprocess redirect stderr stdout redirect csv ./filelistperms.csv multiprocess csv ./Chunks.csv gam user "~primaryEmail" print filelist fields id,name,permissions,owners.emailaddress,linksharemetadata,resourcekey,mimetype,webviewlink  query "visibility='anyoneWithLink' or visibility='domainWithLink'"

4. Run the GetLinkSharedDriveACLs script to generate linksharedperms.csv  (v1 - v5) 

5. Take all 5 linksharedperms.csv files and filter by resourceKey exists, and then create one large report of all the Drive Files that have a resourceKey.

My Alert in Admin console for the number of users affected was from a month ago, so the number of Users/Files wasn't an exact match, but it was very close. 

Thanks,

Grant