Using GAM to output a Google Drive Permissions Audit of all files?

Cuda Prime

unread,

Jan 4, 2018, 9:46:43 PM1/4/18

to GAM for G Suite

Hi!

I've used GAM for a bit of data mining in Google Suite, but I'm stuck on this one.

I want to move a data set from Google to Sharepoint and want to be able to intelligently plan the structure.

Knowing who owns a file, and who else has access to it, and to what degree, is obviously needed information when migrating data like this.

Ideally I am trying to generate a .csv that lists the following information.

File Path (So I know where in Google drive it actually lives)

File Name (Not the ID, though that might be useful too, but the actual name i.g. "Workbook_Accounting.xls")

File Owner

All other users the file has been shared with.

The current permissions given to each person it is shared with.

If GAM is not capable of this, does anyone know of a third party tool that will get this job done?

Thanks a ton!

Ross Scroggs

unread,

Jan 4, 2018, 10:36:31 PM1/4/18

to google-ap...@googlegroups.com

Cuda,

With standard Gam you can get all the info you listed except file path.

With my Advanced Gam (https://github.com/taers232c/GAMADV-X) you can get everything you listed.

One user:

gam redirect csv ./filelist.csv user test...@domain.com print filelist fields id,title,permissions filepath

redirect csv ./filelist.csv - redirect the output to a CSV file

user test...@domain.com - user of interest

print filelist fields id,title,permissions filepath - what to do

filelist.csv will have these headers:

Owner,id,title,paths,path.0,path.1,path.2,...,permissions,permissions.0.deleted,permissions.0.domain,permissions.0.emailAddress,permissions.0.id,permissions.0.name,permissions.0.photoLink,permissions.0.role,permissions.0.type,permissions.0.withLink...

Owner - File owner email address

id - Drive file Id

title - Drive file name

paths - How many paths there are to the file: 0-N. 0 is an orphan, 1 is typical, N for files with multiple parents

path.x - Full path to file, there will be one column per path

permissions - How may ACLs there are for the file: 1-N.

permissions.x.field - permission fields, there will be one set per ACL. See https://developers.google.com/drive/v2/reference/permissions#resource for a description of the fields

Multiple users from a selection:

gam config auto_batch_min 1 redirect csv ./filelist.csv multiprocess <UserSelection> print filelist fields id,title,permissions filepath

config auto_batch_min 1 - use parallel processing

redirect csv ./filelist.csv multiprocess - redirect the output to a CSV file, combine data from the multiple parallel processes

<UserSelection> - users of interest

group Testgroup - all users in the group Testgroup

ou /Path/To/OU - all users in the OU /Path/To/OU

all users - all users in domain

print filelist fields id,title,permissions filepath - what to do

Multiple users from a CSV file Users.csv with a column User containing email addresses:

gam redirect csv ./filelist.csv multiprocess csv Users.csv gam user ~User print filelist fields id,title,permissions filepath

redirect csv ./filelist.csv multiprocess - redirect the output to a CSV file, combine data from the multiple parallel processes

csv Users.csv gam user ~User - file with user email address in column User

print filelist fields id,title,permissions filepath - what to do

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/2b39e0de-65c2-4a14-946f-2e81eb77d1cb%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Cuda Prime

unread,

Jan 5, 2018, 2:31:29 PM1/5/18

to GAM for G Suite

Hi Ross!

Thanks for the information, I've installed your GAMADV, and it appears to be functioning as intended as I can run the command:

$gam redirect csv ./fileist.csv user specif...@domain.com print filelist fields id,title,permissions filepath

And I believe it's pulling all the information I need, plus some...

But I'm not so savvy that I can figure out how to best use this for my situation.

In short I would like a command that lists every file in my organizations Drive, its path, who the owner is, who the files are shared out to and with what permissions.

Is that possible with one command, and if so, is that wise?

Meaning, would the file created be fairly ridiculous in size considering a user base of 300, some with thousands of files owned?

I'm thinking perhaps a batch file that has a list of all the users, puts them in an array, then runs the command for each generating a unique filename, so in the end I would have 300 uniquely named files, showing the file ownership and shared permissions of each of my users.

Not that I'm so savvy I know exactly how to write that yet, but I'm hopeful I can figure out the bash scripting needed. :)

But what do you think?

Thanks again Ross, I really appreciate your time and help!

Ross Scroggs

unread,

Jan 5, 2018, 2:55:57 PM1/5/18

to google-ap...@googlegroups.com

Cuda,

To get all of the output in a single file, do this.

gam config auto_batch_min 1 redirect csv ./filelist.csv multiprocess all users print filelist fields id,title,permissions file path

To get the output in a file per user, do this:

gam all users print users primaryemail | gam csv - gam redirect csv ./~~primaryEmail~~-filelist.csv user ~primaryEmail print filelist fields id,title,permissions file path

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/893e19a9-cee9-467a-891a-3979ebc0152a%40googlegroups.com.

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

--

Ross Scroggs

ross.s...@gmail.com

Message has been deleted

Ross Scroggs

unread,

Jan 5, 2018, 4:51:33 PM1/5/18

to google-ap...@googlegroups.com

Cuda,

Excel on the Mac terminates lines with CR/LF (\r\n).

The CR(\r) is being read by your bash script as part of the file name, it's being converted to a ?

Add this to your script: perl -i -pe 's/\r//' users.csv

#!/bin/bash

perl -i -pe 's/\r//' users.csv

while read -r LINE; do

/<PATH>/gam redirect csv ./Output/"$LINE".csv user "$LINE" print filelist fields id,title,permissions filepath

done < users.csv

Ross

On Fri, Jan 5, 2018 at 1:10 PM, Cuda Prime <cuda...@gmail.com> wrote:

Thanks Ross! You've been amazingly helpful and I can't express my gratitude!
But as a small thank you I can show you what I came up with as a solution, in case anyone else is trying to do something similar.

In the interests of not having a single file that was completely obscene I figured it might be best to have a separate file for each user.
I figures out a way to generate a separate file for each user by first creating a file with each user on a separate line using the command;

gam print users emails > users.csv

Then I used MSExel and trimmed all the headers and excess info other than email address, so I have a simple list of user email addresses that looks like this;

$cat truncusers.csv

us...@domain.com

us...@domain.com

us...@domain.com
... etc.

Then I created this simple bash script;

$cat filelist.sh

#!/bin/bash

while read -r LINE; do

/<PATH>/gam redirect csv ./Output/"$LINE".csv user "$LINE" print filelist fields id,title,permissions filepath

done < users.csv

And voila, I've got a directory with a separate file for each user, showing their files, shares, permissions, etc.

$ls Output/

user1@domain.com?.csv user2@domain.com?.csv user3@domain.com?.csv

Not sure why the username is getting that '?' appended to it, but the contents of the files are correct at least.

If you have a thought as to why my script is inserting the ? into my filenames I'd appreciate the guidance.
And thanks again for your support of this, I've found it, and you, insanely helpful!!!

On Friday, January 5, 2018 at 11:55:57 AM UTC-8, Ross Scroggs wrote:

Cuda,

To get all of the output in a single file, do this.
gam config auto_batch_min 1 redirect csv ./filelist.csv multiprocess all users print filelist fields id,title,permissions file path

To get the output in a file per user, do this:
gam all users print users primaryemail | gam csv - gam redirect csv ./~~primaryEmail~~-filelist.csv user ~primaryEmail print filelist fields id,title,permissions file path

Ross

On Fri, Jan 5, 2018 at 11:31 AM, Cuda Prime <cuda...@gmail.com> wrote:

Hi Ross!
Thanks for the information, I've installed your GAMADV, and it appears to be functioning as intended as I can run the command:

$gam redirect csv ./fileist.csv user specif...@domain.com print filelist fields id,title,permissions filepath
And I believe it's pulling all the information I need, plus some...

But I'm not so savvy that I can figure out how to best use this for my situation.
In short I would like a command that lists every file in my organizations Drive, its path, who the owner is, who the files are shared out to and with what permissions.

Is that possible with one command, and if so, is that wise?
Meaning, would the file created be fairly ridiculous in size considering a user base of 300, some with thousands of files owned?

I'm thinking perhaps a batch file that has a list of all the users, puts them in an array, then runs the command for each generating a unique filename, so in the end I would have 300 uniquely named files, showing the file ownership and shared permissions of each of my users.
Not that I'm so savvy I know exactly how to write that yet, but I'm hopeful I can figure out the bash scripting needed. :)
But what do you think?

Thanks again Ross, I really appreciate your time and help!

--
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+unsubscribe...@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/893e19a9-cee9-467a-891a-3979ebc0152a%40googlegroups.com.

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

--
Ross Scroggs
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-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/c614fe7d-1493-4544-942b-26e1b3f91f73%40googlegroups.com.

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

--

Ross Scroggs

ross.s...@gmail.com

Cuda Prime

unread,

Jan 5, 2018, 5:28:47 PM1/5/18

to GAM for G Suite

Please disregard the question about the added ?

It was simply the caraige return, I got of it by using;

sed -e 's/^M//g' input > output

Cuda Prime

unread,

Jan 5, 2018, 5:30:22 PM1/5/18

to GAM for G Suite

LOL! Thanks Ross, guess I could have seen if you'd replied before I answered my own question.

Thanks again for all of you work on this!

Cuda Prime

unread,

Jan 8, 2018, 2:08:21 PM1/8/18

to GAM for G Suite

Hi Ross, one more, hopefully quick question for you...

I've got the command that will generate a list of all files owned by a user, and who those files have been shared with...

Is there a command that will generate a file of output that shows all files that are shared with a particular user?

For example, user1 has shared file A with user2... user3 has also shared file B with user2... I want to pull a list of all files shared with user2, regardless of who owns them, does that make sense?

TIA!

Ross Scroggs

unread,

Jan 8, 2018, 2:20:56 PM1/8/18

to google-ap...@googlegroups.com

Cuda,

Try this: gam user testuser1 print filelist fullquery "sharedWithMe=True and not ('me' in owners)" fields id,title,permissions,sharinguser filepath

This might get more than you want based on the comment here: https://developers.google.com/drive/v2/web/about-organization#shared_with_me

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/679b8959-eedf-44d2-9991-1a10437d98b3%40googlegroups.com.

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

--

Ross Scroggs

ross.s...@gmail.com

David Shawn Covert

unread,

Jan 8, 2018, 2:25:45 PM1/8/18

to google-ap...@googlegroups.com

When I execute:

gam redirect csv ./test.shared.csv user testuser1@domain.com print filelist fullquery "sharedWithMe=True and not ('me' in owners)" fields id,title,permissions,sharinguser filepath

I get...

Help: Syntax in file /Users/Cuda/GAM/GamCommands.txt

Help: Documentation is at https://github.com/taers232c/GAMADV-X/wiki

Ross Scroggs

unread,

Jan 8, 2018, 2:28:52 PM1/8/18

to google-ap...@googlegroups.com

David,

Do: gam version

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/CALDsw%3DR4XKJGPBLhhOVWOQ6-Ho2pL7SD%3D8_8RRYjz9q5K44Ezw%40mail.gmail.com.