How to audit externally shared files in Drive?

chrisrosa · 11-09-2022 01:37 PM

-- reposting after this got closed on the Google Workspace Community...

I just got off a lengthy chat with support on this issue, and haven't found a solution. So I thought I'd try here to see if any of you admins have found a work around or even a 3rd party tool to do this.

I’m trying to figure out if there’s a way for me to see all files that are shared with someone other than (or in addition to) our domain.

I can search the Drive Events Log, but that only goes back 6 months. The Security Dashboard File Exposure report seems to only go back 180 days. There doesn’t seem to be any way to audit the current state of all files in our Google Drive…individuals or shared drives.

Initially, I was simply trying to find any files that might be shared with an old vendor of ours, let’s call him <guy@otherdomain.com>. I noticed him randomly in the share tab of a file I found through the File Exposure report, so I was wondering if there were more files shared with this person (because it seems pretty likely there would be).

I tried the Investigation tool, and did find the recent access change for that file where I removed his access, but since it’s a log and not really searching all drive files, it’s pretty inconclusive.

I thought Vault might be a solution, but that didn’t seem to work at all either.

So I’m curious if there’s a way to do this as a Google Admin, or no? How are you managing forgotten files/folders that are still shared with people that maybe shouldn’t be any longer?

Thanks

icrew

It's not an audit, but the newly-released Drive Trust Rules might be a big help here. Check out https://support.google.com/a/answer/10621317?hl=en for the details.

Hope that helps,

Ian

chrisrosa

Yes...will definitely be handy going forward. Would be nice if there was a way to do a retroactive scan.

Tricent's solution looks pretty slick, but of course I'm looking for a way to do something similar with what I have. Thanks for the reply.

KAM

@chrisrosa have you looked at Cloudlock or BetterCloud? -KAM

chrisrosa

At first glance, those look a bit overboard for what I'm trying to do, but will definitely check them out. Thanks!

icrew

You can likely do this with the free, open source, command-line, indispensable GAM tool. Check out:

GAM: https://github.com/jay0lee/GAM/wiki
GAM Advanced (a variant with a few more features): https://github.com/taers232c/GAMADV-XTD3/wiki
and the mailing list at https://groups.google.com/forum/#!forum/google-apps-manager

for more details.

Specifically, see the following thread from the GAM mailing list a couple of years ago, which might point you in the right direction: https://groups.google.com/g/google-apps-manager/c/SAJtuDiNO-0 (and a post to the GAM mailing list would likely yield results if not...)

Hope that helps,

Ian

cryptochrome

Unfortunately, you discovered one of the biggest pain points and security issues in Google Drive. Google does not provide a good solution to that. There are countless "hacks" that can get you there, but they all require manual labor, fiddling with sheets, using command line tools, etc. - None of it is a great user experience.

We had the same issue and looked for third-party solutions to this problem and found Nira. It does give you everything you need to get an overview of which files are shared externally, by whom, with whom, etc. You can also take action right from the Nira app (like removing shares, for example). You can find the app here: https://nira.com/

Hope this helps.

chrisrosa

Looks kind of similar to Tricent's solution. Will definitely check out a demo. Thanks!

NoSupportHere

It is impressive in a bad way that there's no easy way to find all the files that are shared externally or publicly. Wow. What basic permissions management- that you should be able to search for "external" or -something- you can put in here to find those files...

Wow.

JB123

I can't even find the "Security Dashboard File Exposure report", despite looking through the instructions multiple times. 😞

ajojose33333344

@JB123 Here it is:

JB123

There is no such "Security Center" for me:

I guess it's not included in Google Workspace for Nonprofits? "Supported editions for this feature: Enterprise; Education Standard and Education Plus."

ajojose33333344

@JB123 yes, it's available with enterprise and education licenses only.

https://support.google.com/a/answer/7492003?hl=en

ajojose33333344

@JB123 some security center features are also available with cloud identity premium you can simply take a free trial and see if you want.

The actual price of it would be around 5$/user/month after the trial period i guess , but not sure if it is available for nonprofits, if available you might get a considerable discount.

JB123

All I want to do is identify documents that are shared with people outside our domain. There's no other way to do that? Or even to identify documents shared with a particular person? Or shared with someone other than a particular group of people?

ajojose33333344

@JB123 which plan do you have?

JB123

Google Workspace for Nonprofits

cryptochrome

There isn't, AFAIK. It's a big pain point in the Google Workspace ecosystem. There are third-party solutions like nira.com that can help.

Marcus1

That is nice but it is about "events" i.e. whenever someone actually shared something vs what the OP would like which is to know what exactly it is shared and with whom. If a user shared a folder 2 yrs ago with an external, it is not going to be shown on those reports.

kim_nilsson

@chrisrosa if you only want to find files shared externally, this companion script for GAMADV-XTD3 will list them. If you instead want to see files shared with a specific user, this script is used.

Yes, regardless... you will want to install GAMADV-XTD3 for any and all bulk management of objects and/or settings. It's free and open source.

Marcus1

Is GAM suitable for a corporate environment?

I have concerns about giving an open source tool from github domain-wide super admin access. It seems scary.

ajojose33333344

@Marcus1 Yes it is, I was just being part of a google conference and ask the same question there and the answer was:

"It was developed by a googler and it is a trusted application by google, used by many customers and absolutely trusted and please feel free to use"

kim_nilsson

@Marcus1 you're actually not giving "the tool" any access at all.

You are giving yourself access, when using the tool.

The tool is installed on your device, under your control, and it does absolutely nothing if you don't tell it to, and then it only does what you tell it.

GAM and it's more capable sibling GAMADV-XTD3 (which I and most people I know use) are fully open source, and can be run as plain text python files, if you want, but almost everyone runs the pre-compiled binaries. It's just easier.

GAM was created by and is developed by a googler, Jay Lee.

GAMADV-XTD3 was forked from GAM and is developed independently by Ross Scroggs, but still in close cooperation with Jay, and much code and ideas flow between the two projects. XTD3 has many more features than regular GAM. The XTD3 wiki is also vastly superior.

Both versions are used all over the world in organisations with hundreds of thousands (millions in som cases) of users and devices. And it is just as reliable and useful in organisations of only a handful of users.

Marcus1

Many thanks Kim. I was not worried about giving access to the tool, but rather something malicious in the code acting with my credentials. Would not be the first time an open source tool is trojanized. But it seems it is pretty safe from what you explained. Much appreciated!

Erik-Nordahl

Thanks for this solution. But I can't make it work. I installed GAMADV-XTD3 on my Windows laptop, inserted our doamin,and executed: python GetSharedExternallyDriveACLs.py

The output I get is this:
Owner,driveFileId,driveFileTitle,mimeType,permissionId,role,type,emailAddress,domain,allowFileDiscovery

And then the scripts hang.

Any thoughts?

/Erik

kim_nilsson

So, you didn't actually read the information in the script.

There are very detailed instructions in the script on how to use it.

Erik-Nordahl

Sorry! You're right, and I apologuise.

Actually reading the script, I got this:

C:\GAMWork>gam all users print filelist id title permissions owners mimetype > filelistperms.csv
Getting all Users, may take some time on a large Google Workspace Account...
Got 42 Users...
Got 42 Users
User: <FIRST_USER>, Service not applicable/Does not exist (1/42)

And then all users are listed. But the .csv file only contain the headers.

Hope you haven't fully given up on me.

kim_nilsson

No worries. We'll see what is wrong.

Is this the first time you are using GAM? Perhaps you didn't go through the entire setup properly. If you are receiving the same error message for all users, it means you don't have rights to impersonate them, and therefore can't access any of their files.

What do these commands say?

gam version

gam info domain

Please, it's fine to obfuscate/hide/replace any details that are personal.

Also, this command is the one that gives you impersonating rights.

gam user SomeOtherUserInYourDomain check serviceaccount

It should say PASS for all rows of scopes.

If it doesn't then your setup isn't correct.

Erik-Nordahl

Thanks! You are correct. I am a new user. 🙂

I ran all your suggested commands and they all seemed fine/PASS. So I redid the set up. (I did actually do this initially, but must have done something wrong), because now it worked, and I can move on to next step. I think I must have mistyped something when setting up the service account.

Many thinks for you support (and patience).

/Erik

kim_nilsson

Ahhh, brilliant!

wgibson

Antidotally, what's an estimated runtime for this against an active domain with X active users?
(First step seems like a doozie)

icrew

This sort of thing is far more bound by the number of files, not the number of users. So it really depends on how many files your folks have, and that depends a lot on what sort of work your folks do. For example, we have some researchers with tens of millions of files in their Drive, and we have retirees with a few dozen....

nzlittleman

Searching for what I think is the same issue. We are a non-profit and want to remove all sharing across our Google Drive workspace, so we can start again as over the years there's been files/folders shared with people no longer involved - or they've created folders using an external email and we can't take ownership of them (even though they're in our Workspace). Haven't been able to find a solution to this yet. Exploring some of the apps suggested here, but this seems to be something that Google should just provide!

kim_nilsson

Of course, that'd be nice, but they don't.

As I explained above, GAMADV-XTD3 is the best tool for the job, especially for a NPO where money usually is tight.

VAK12sysadmin

Another issue they acknowledge as an issue is this (at Workspace support level):

There is no audit trail in Vault or Drive or Gmail when you share a file/folder with comments to an EXTERNAL user, outside your domain. You can see internal shares to other users in your domain (WITH comments) since that is retained by Gmail retention in Vault. That is not the case though, apparently, for emails generated by the Google service account that shares comments and the share link with an external user, DESPITE your internal user being the one to initiate the external share with a comment. This appears to be a big loophole in Vault retention.

icrew

Do you have Comprehensive Mail Storage turned on? (See https://support.google.com/a/answer/3547347?hl=en) If you do, all of those sorts of messages will show up in users' Sent mailboxes, and I think will therefore also be preserved in Vault. I think....

Hope that helps,

Ian

VAK12sysadmin

Will give that a try with a test user...thanks for the suggestion!

icrew

Please report back either way--it'd be good to know for sure.

VAK12sysadmin

In my early testing, I think that does the trick! Thanks so much for the tip. It wasn't on by default.

Marcus1

Has anyone found more options than the ones discussed here (Nira, Tricent and GAM)?