Find all files above a certain size?

[Ian Crew]

Hi all:

Does anyone know of a way (ideally using GAM, but other tools are a possibility) to generate a list of all files above a given size for an entire G Suite instance? Ideally, we’d want to retrieve owner and file size for each file above the limit. 

Note that we’re interested in the cases where a *single file* is over the limit, not the ones where a user’s *total* usage/quota is over that limit...

Thanks!

Ian

--

___

Ian Crew

IST-Architecture, Platforms and Integration (API)

Earl Warren Hall, Second Floor

University of California, Berkeley

[Ross Scroggs]

Ian,

The API has no support for querying about file size, you'll have to write a script.

Do the following:

gam user us...@domain.com print filelist id title filesize mimetype query "mimeType != 'application/vnd.google-apps.folder'" > userfiles.csv

You get these headers: Owner,id,title,fileSize,mimeType

fileSize is only populated for non Google files

Ross

--

ross.s...@gmail.com

--
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-man...@googlegroups.com.
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/CAD2sLFtb%3DTwGr0t44KXtNudPKPhx%3DXze0Vt4kr2jUndfkKixHQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Ian Crew]

Thanks Ross!

So if I wanted to do that for all users, I’d take the output from 

gam print users emails and pipe that into the command you referenced?

I’m thinking this could be a pretty simple script:

grab the list of all of the users (above)

loop through that list with the “get all files” command you suggested (below)

extract fields 1 and 4

look for files that are above the limit

save the results

Of course, it’d take a while to run, but otherwise it’s not (conceptually) difficult...

Sound about right?

Cheers,

Ian

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/B9CF8AAF-FA21-4A5A-BE68-4C9D6146CBB6%40gmail.com.

[Ross Scroggs]

Ian,

No piping required:

gam all users print filelist id title filesize mimetype query "mimeType != 'application/vnd.google-apps.folder'" > userfiles.csv

That's going to be a big file

Ross

--

ross.s...@gmail.com

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/34420B7F-1013-4853-854B-B61CBEE7AD49%40berkeley.edu.

[Ian Crew]

Even easier, thanks.

This could become a one-liner, I think:

gam all users print filelist id title filesize mimetype query "mimeType != 'application/vnd.google-apps.folder'"  | awk -F ‘,’ $4>1000000000{'{print "$1,$4”}'

(which would also cut down on the output a good bit….)

Cheers,

Ian

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/86B5F4E1-A351-461A-82DC-DFC04935D440%40gmail.com.

[+KimNilsson]

Ian, di you figure out a complete oneliner?

When I tried yours with piping to file awk gave me an error.

gamx all users print filelist id title filesize mimetype query "mimeType != 'application/vnd.google-apps.folder'"  | awk -F ‘,’ $4>1000000000{'{print "$1,$4”}' > large_userfiles.csv

awk: no program given

[Joetje F]

This will work:

gamx all users print filelist id filesize mimetype query "mimeType != 'application/vnd.google-apps.folder'"  | awk -F, '{ if ($3 > 100000) { print $2,$3 } }'

I'm leaving out title as it may contain a comma

[+KimNilsson]

Yes, so far so good.

1834/5947 users processed.

[Ian Crew]

Thanks for debugging the code, Joetje! I think I accidentally left an extra single quote in there someplace, and great catch re. commas in the filename. I might still have the output be  { print $1,$2,$3 }  to include the user’s account name, and make sure that the output is comma-separated, so:

gam all users print filelist id filesize mimetype query "mimeType != 'application/vnd.google-apps.folder'"  | awk -F, '{ OFS = ","; ORS = "\n"; if ($3 > 100000) { print $1,$2,$3 } }’

Extending that a bit, it’s possible to have it all run in the background, and have things end up in log files and csv files, named with the date and time (assuming you’re running macOS/Linux):

gam all users print filelist id filesize mimetype query "mimeType != 'application/vnd.google-apps.folder'" 2> "./large_files_`date '+%Y-%m-%d_%H-%M-%S'`.log" | awk -F, '{ OFS = ","; ORS = "\n"; if ($3 > 100000) { print $1,$2,$3 } }' > “./large_files_`date '+%Y-%m-%d_%H-%M-%S'`.csv" &

This could be useful, for example, if you wanted to run it via cron job on a periodic basis.

Cheers,

Ian

--
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-man...@googlegroups.com.
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/cd0d44fb-4b9c-4076-8709-4cd972f07137%40googlegroups.com.

[Ross Scroggs]

Ian,

I've added this to Advanced GAM 4.65.67.

$ gam user testuser1 print filelist minimumfilesize 10000 title size

Getting all Drive Files/Folders that match query ('me' in owners) for test...@rdschool.org

Got 133 Drive Files/Folders for test...@rdschool.org...

Owner,name,size

test...@rdschool.org,TestRevision.pdf,10286

test...@rdschool.org,GamUpdate.txt,359837

test...@rdschool.org,users3.csv,23578

test...@rdschool.org,users.csv,23578

test...@rdschool.org,dashedusers.txt,30691

test...@rdschool.org,clip.dv,625560000

test...@rdschool.org,allusersprofile.out,54709

test...@rdschool.org,TestMHTML.mht,186659

test...@rdschool.org,Kurzweil-K2000-Dual-Bass-C1.wav,1322298

test...@rdschool.org,TextWithPicture.odt,11452334

Ross

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/D0E0EAA4-F2D7-44CF-9AA7-8C63C9A032B1%40berkeley.edu.

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

--

Ross Scroggs

ross.s...@gmail.com

[Seth Dimbert (Hillel Yeshiva)]

Ross, how can your new, one-line command be used against all users in a particular OU?

[Ross Scroggs]

Seth,

gam redirect csv ./BigFiles.csv ou "/Path/To/OU" print filelist minimumfilesize 10000 title size

Ross

--

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/d09e367a-8b52-40e3-9f18-8b47adcd0608n%40googlegroups.com.

[Seth Dimbert (Hillel Yeshiva)]

Thanks. I ran a slightly modified command:

gam all users print filelist minimumfilesize 10000 title size todrive

It's slow... but it's working. Two follow-ups:

1. I realize I should have asked gam for the fileID of each file, too. That way, I could grant myself View access to the ones I want to check out, right?
2. I recall another discussion you and I had where we broke a similar command into two parts, filtering the list of results before querying for details. Could we have somehow filtered this list by files above 10000 before reporting the file list? Would that be faster?

-Seth