Print all public Drive files

[Kody Striplin]

Hi there,

I am trying to print a list of all Google Drive files (per individual/OU/organization) whose share settings are set to 'Public on the web' or 'Anyone with the link'.

So far, I am able to retrieve a list of files with 'show filelist'. Using the resulting .csv, I am able to 'show fileinfo' to retrieve file names and 'show drivefileacl' to retrieve file permissions. The problem I am running into is that neither of these latter 2 functions are outputted in .csv format, so I cannot easily match up the data. Ideally, I would like the data to only include publicly-available files or be displayed as <file name>, <share settings> so that I can sort the data manually.

Here are the commands I am using:

Retrieve list of user's files:

gam user <email> show filelist id | gam csv - gam user <email> show fileinfo ~id id name

Result:

 id: <snipped>

 title: test

 id: <snipped>

 title: test123

 id: <snipped>

 title: test42

 id: <snipped>

 title: test

Retrieve each file's permissions:

gam user <email> show filelist id | gam csv - gam user <email> show drivefileacl ~id

Result:

<account name>

 domain: <domain>

 emailAddress: <email>

 role: owner

 type: user

 id: <user account id>

<account name>

 domain: <domain>

 emailAddress: <email>

 role: owner

 type: user

 id: <user account id>

Anyone

 withLink: False

 role: reader

 type: anyone

 id: anyone

<account name>

<account name>

 domain: <domain>

 emailAddress: <email>

 role: owner

 type: user

 id: <user account id>

 domain: <domain>

 emailAddress: <email>

 role: owner

 type: user

 id: <user account id>

Anyone

 withLink: False

 role: writer

 type: anyone

 id: anyone

I can probably find a way to index the results of each command and match them up in a spreadsheet or via Python, but I was wondering if there was an easier way to do this with GAM.

Thanks in advance for the help.

Kody

[Ross Scroggs]

Kody,

Try: gam user <email> print filelist allfields > userfiles.csv

See if that has all the data you need.

Ross

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.
To post to this group, send email to google-apps-manager@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/ffd134cf-4604-4da3-ae55-81cf731b062d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Ross Scroggs

ross.s...@gmail.com

[Kody Striplin]

Of course it's a simple solution. Thanks Ross!

Kody

To post to this group, send email to google-ap...@googlegroups.com.

Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/ffd134cf-4604-4da3-ae55-81cf731b062d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Ross Scroggs

ross.s...@gmail.com

[Steve - DynTech]

or as I prefer to do

gam user <email> print filelist allfields todrive

seems to be a limit though at 10400 files though and then forces it to csv anyways. just my files export was 26MB

what are the chances that we can query all user files across the domain and only export those with the specific acl level we care to see?

Trying to export all files for a few thousand users across millions of files would take ages

[+KimNilsson]

Yup.

That's why a tool like GAT (GeneralAuditTool) does it in regular scans, and automatically does the funky cross searches for you, and then just allows you to pick and quickly display the files with the "wrong" type of share. Or send you daily reports on users sharing files that way.

On Thursday, 5 January 2017 01:18:49 UTC+1, Steve - DynTech wrote:...

[Jon Limmer]

I used a .csv of user email addresses I wanted to audit/view and then tried this command:

        gam csv userlist.csv gam info ~primaryEmail filelist allfields > drivelist.csv

GAM consumed all my computer's memory and took forever.  The oddest part, however, is that the 'drivelist.csv' output file was inconsistent in layout.  A few accounts from the input listed all fields while the output for most accounts were missing many of the fields including, unfortunately, the ones that indicated visibility (anyonewithlink, anyone, etc).

Why does this command output results that vary widely in what fields show by account?  Why wouldn't there be consistency?

Jon

On Wednesday, January 4, 2017 at 1:32:33 PM UTC-5, Ross Scroggs wrote:

[Ross Scroggs]

Jon,

This isn't valid: gam csv userlist.csv gam info ~primaryEmail filelist allfields > drivelist.csv

I'll assume you meant: gam csv userlist.csv gam user ~primaryEmail print filelist allfields > drivelist.csv

Basic gam doesn't handle this particularly well because (> drivelist.csv) is handled by the shell and

you have multiple processes all writing to drivelist.csv at the same time; you get a header row per user and all of the output gets jumbled up.

In my advanced gam: https://github.com/taers232c/GAMADV-X you would do the following:

gam redirect csv drivelist.csv multiprocess csv userlist.csv gam user ~primaryEmail print filelist allfields

This (redirect csv drivelist.csv multiprocess) says to take the output from all of the processes started by gam and write a single coherent CSV file.

This does not change the time it takes to get the data, it just gives you good data.

Ross

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.
To post to this group, send email to google-apps-manager@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/d2689ab0-eb07-444c-bf91-d92fb3ab1014%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Ross Scroggs

ross.s...@gmail.com

[Jon Limmer]

Hi Ross,

 

First, yes, my typo on “info” vs “user”.

 

Thanks for the approach that should fix the problem of the mixed output.  Your explanation matches what I’m observing perfectly.

 

A great help!

 

Jon

[Julien Test]

Hello, 

I'm trying to do the same thing (showing all the google Drive files that are public).

May I ask you which field show that a file is public ? 

Thank you for your help.

[Temple Rodgers]

Hi, I'd like to resurrect this thread - I want to find everything that is shown public_on_the_web and it doesn't seem simple because when I generate some data using ...print filelist allfields... I get many columns showing the permissions, one of which could (I think) be the permission that gives public_on_the_web. I also note there's a python script here, which does deconstruct the permissions columns - so, is "anyone" equivalent to public_on_the_web? have I got the right python script? 
Thank you

[Kim Nilsson]

Use one of the other scripts instead. 

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

[Temple Rodgers]

thank you @there.is.no ... I'll give it a try

[Temple Rodgers]

BTW, how do I know if this is running? 

[Kim Nilsson]

That command will usually only take a few seconds.

Do note that what is in your image is not how it is supposed to run!

You have to read the script carefully.

All the steps are written inside it.

[Temple Rodgers]

thank you - I bow my head in shame - I didn't read the comments - ech!

strangely though, the script seems to have hung. I'll run it in another window.

[Kim Nilsson]

Running the initial gam command can take a really long time, if you have many users, or many files. It may also never complete, if your system doesn't have enough RAM.

But the script should be very quick. But, as I said, you can't run the script until after you have the list of permissions, which will take a long time. 

[Temple Rodgers]

@ross - thank you for your help and advice. The script ran further last night but hung again. I can see no activity in the log after 20:49:43 yesterday evening. In the short term, I've split the TeamDriveOrganizers.csv into chunks of 50 rows and I'll run them today. Hopefully I'll have some output shortly.

[Temple Rodgers]

I've been running the chunks, e.g.

gam redirect csv ./filelistperms05.csv multiprocess csv ./TeamDriveOrganizers05.csv gam user "~organizers" print filelist select teamdriveid "~id" fields teamdriveid,id,title,permissions pm type anyone em

but nothing has gone into the log since 2021-08-05T13:48:59.644+00:00

on the good news front, the User data has come through, which has given some excellent results - just the TeamDrive to do

[Temple Rodgers]

and the final update - @Ross had an inspired bit of thinking and this is what has worked in around an hour for almost 800 shared drives: 

gam redirect stdout - multiprocess redirect stderr stdout redirect csv ./filelistperms.csv multiprocess csv ./TeamDriveOrganizers.csv showcmds gam user ~organizers print filelist select teamdriveid ~id fields "teamdriveid,id,title,permissions" query "visibility='anyoneCanFind' or visibility='anyoneWithLink'"

thank you very much @Ross!

[Temple Rodgers]

oops! a post-final update 

we're getting more comfortable with the output. Is it possible to run the final python3 command and direct the output to a Google Sheet instead of the CSV? I did a search look through posts but I couldn't see anything (maybe I missed something)

[Ross Scroggs]

Temple,

No, the script is Google Drive ignorant.

Of course, you can use GAM to upload the CSV file: https://github.com/taers232c/GAMADV-XTD3/wiki/Users-Drive-Files-Manage#create-files

gam user us...@domain.com create drivefile localfile xyz.csv

Ross

Disclaimers apply, for full details see: https://hackney.gov.uk/email-disclaimer

--
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/89774eac-2bf5-4691-bceb-27ba7e1327c9n%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Temple Rodgers]

Thank you @Ross Scroggs - that will be easy to work with. I've lost count of the number of beers I owe you! One day .....

You received this message because you are subscribed to a topic in the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-manager/ATbblIIzTjs/unsubscribe.
To unsubscribe from this group and all its topics, 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/CAJkvRS8QWoKTbBc6Mk9DRqPK_s2ALQq3JEKpDNLtzU46JtKFDQ%40mail.gmail.com.