Posts

Identify All Externally Owned Files with GAT+

G Suite Admins can now Identify externally owned Google Drive files and which folders they reside in in your G Suite domain.

An admin can click on “One Click Report” – External users – Docs

This will show us all external users who have ‘shared in’ Google Drive files into your domain.

By clicking on each of the numbers under the column ‘Owns (not ours)’, the admin will be taken to Drive Audit Files tab where you can examine these Google Drive files in greater detail.

Another way to find all external owned Google documents within your G Suite domain is to open Drive audit and apply a custom filter – show the files which have been shared in. (we excluded deleted/trashed Google docs in this example) because they are included by default.

The result will show all Google docs “Shared in” to your domain and you will be able to view their paths. Since these files are externally owned, you as a G Suite Super admin, your only course of action is to remove and cut the ties to those users they’ve been shared with.

Note: To remove editors and readers from shared in files, there has to be at least one local editor from your Google domain on each of those files.

For each file, you can see the folder or folders that each particular file resides in.

Many files may not have a folder path because they haven’t been added to the local user’s myDrive.

The G Suite Admin can export a Google spreadsheet or a CSV of all shared in file paths by selecting the option ‘with path flattened’. With paths flattened each unique path will be displayed.

Find Internally Shared Google Documents

In Drive audit, we can see a nice overview of all drive files of your entire G Suite domain.

overview of all drive files in GAT+

An admin can select each of the categories and it will lead them to all the files from which the category was created from.
In this case will display all Google Drive files which are Open to internal users.
Sharing flags is set ‘Open to internal’, the users are in grey background color, which also indicates that the user are local and not from an outside domain.

Sharing flags tab in GAT+

How to a Create a Report of End Users Documents They Own

In this usage case, GAT allows domain admins to prepare a report that is sent to each user, showing the files that they own as a spreadsheet attachment.

The report can be prepared around several different user sets, depending on the scope the admin wishes to give to the report.

Select the Drive feature

First, we select Drive Audit then we apply a custom filter and search for files owned by our domain.

Select the filter

We simply search for files where the owner is our domain.

Select apply

Once we find all files owned by our domain, we can click the Apply button and this will run the search.

Click on the Files Operations button and select Remove Permissions. Remove Permissions has a feature to notify the owners of the files in this search result and provide them with a spreadsheet of all the files they own.

Click on the Files Operations button and select Remove Permissions.

Select 'scan' in the multi permission change tab

In the Permission change option select “Report onlythis will ensure no actions are happening on the selected search.

Add a message of your choice.

Key terms to know about when creating a custom message:

{{RECIPIENT}} – recipients full name
{{RECIPIENT.FIRSTNAME}} – recipient first name,
{{RECIPIENT.LASTNAME}} – recipient surname,
{{RECIPIENT.EMAIL}} – recipient email.

{{FILES}} – Displays in the email the files in question with a URL link.


This is the message the end user will see. The email will show him all of the files he/she owns.

This is the message the end user will see. The email will show him all of the files he/she owns.

Clicking on the ‘View files’ button at the bottom will open a Google Spreadsheet for the user.

Remove Access Rights to Documents and Folders with GAT+ & GAT Unlock

With just a few clicks G Suite super admins can remove access rights to any document or folder owned by their domain users at any time, even without Security Officer approval (GAT Unlock functionality). In the Drive result table just click on the drop-down menu option next to a user’s email address or the actual permission for example “everyone” or “everyone with link”. You will be prompted with several options.

When you click on the drop-down menu for “everyone” or “everyone with link” permission you will have 3 options, you can remove the permission just for the doc or folder you selected or you can remove that permission for all files within the applied filter search where ever that permission appears for editors or readers.

The same 3 options are available when the drop-down menu is pressed for external or internal email address. You will be prompted with options to remove the users access rights.

When you click on an local domain user’s email address drop-down menu. You will see additional options, for example you can quickly navigate to their owned docs, files they have access to (Files in user_x@mycorp.com) and actions they’ve taken on files (Events for user_x@mycorp.com)

Note: For internal public files shared to your entire domain you may see the following permission: “mycorp.com (with link)”. The same actions can be taken to remove this permission.

You may also find this post helpful:

How to Remove Public and Published Permissions

How to Restore Permissions Removed By a Policy

How to Update GAT Permissions

Which files are shared externally and not visited in the last 90 days?

This is a really good question for Admins interested in cleaning up externally shared files that have gone ‘cold’.

GAT can help answer this request.

The simple solution is implemented as follows.

From the Drive Audit select all files viewed in the last 90 days and then click on ‘Negate Filter’

Next select ‘Clear Filter’ to create a fresh search and then select only those files share ‘Out’

Now go to the tab ‘Recent Filters’, select the last two searches you did, combine with ‘AND’ and click on show.

This shows all files not read in the last 90 days that are shared out. This may be quite a large number of files. Using ‘Schedule/Save’ you can do the following.

Run a daily report to get this list.

Automatically revoke the external sharing.

Warn the local owners with a tailored message asking them to remove the share.

Or you can do any combination of the above.

You can also run a one off job to do the same.

If you schedule this task to run daily, the 90 day lookback window will automatically be moved forward on a daily basis.

 

The problem with unbounded reports is that they tend to be too large to follow daily and with unbounded actions is that they tend to be too broad and you spend a lot of time fixing or reversing exceptions.

 

The ideal solution is to create a time frame window through which you can view the ‘at risk’ files and make smaller more calculated decisions. You do this as follows.

Select ‘Clear Filter’ again, to reset your search.

Next select all files ‘Created’ in a window 97 days to 90 days back. This will give you a 1 week window. After you complete that search, select ‘Out’ to show just the files from that set that are shared out of the domain.

Again go to recent filters and select the original filter to show all files not viewed in the last 90 days. Combine this with the last filter showing all files shared out that were created in the 97 to 90 days window and select ‘Show’.

This will give you the new combined search string

  • Not (Docs not deleted viewed from 26/03/2015) and Docs shared out not deleted created from 19/03/2015 to 26/03/2015

You can schedule this to run nightly at 23.00 and each morning you will have a brief report of the files created 90 to 97 days ago, but not read in the last 90 days. As time progresses all new files 97 days and younger will pass through this window. Each file should last one week in the report if it is not visited, automated alerts can be sent to the owners for the week or you can take action on these files as Admin.

 

If you don’t want the bother of building up the rule yourself you can click on the ‘pen’ icon

 beside the rule and post the following…

 

{

 “0privacy”: “NULL_PRIVACY”,

 “0searchTextType”: “DOC_NAME”,

 “#multi”: “and 0 1”,

 “1createdFrom”: “19/03/2015 00:00:00”,

 “0lastViewedFrom”: “26/03/2015 00:00:00”,

 “0deleted”: “false”,

 “1deleted”: “false”,

 “0negate”: “true”,

 “1privacy”: “NULL_PRIVACY”,

 “1sharedOut”: “true”,

 “_reportType”: “USER_DOCS”,

 “1searchTextType”: “DOC_NAME”,

 “1dateTo”: “26/03/2015 00:00:00”

}

 

DON’T forget to change the dates! Dates above are in European format.

 

GAT will automatically move all dates in the search string forward by one day as the job runs daily.

How to Remove Published Access to a Google Document

‘Published’ documents are created when a specific version of the document is ‘Published to the web’ via the ‘File’ option in a Google document. This creates a link to the version of the document at the point of publishing. This link can be embedded in a web paged or passed in an email. It is searchable by search engines. Subsequent changes to the document will not change the ‘Published’ version.

To remove the ‘Published’ status of the published version you will need to follow the following steps.

Removing Public Access to a Google Document

Open the offending document (Click on the link in the email we sent you).

In the top right corner of your document, you will find a share button…

Repeat these steps for each document you are concerned about.

Notifying Local Owners If Their Doc Was Matched in an Audit

In this Tech Tip, we have two examples of how to frame a search for an ongoing Audit or Policy.

In the first example, we look for all documents shared to or from Users in the ‘gmail.com’ domain

We are going to run a daily scan to alert local owners that their documents may breach company policy by being shared to or from the gmail.com domain.

In the Drive Audit, select ‘Users’, select from a date, pick today’s date for daily audits, pick a week back for weekly audits, etc.

Next, select ‘Documents Changed’ and then click ‘Search Documents’, this will build your search ‘chip’ and see if you have any documents that match the criteria, for the time period chosen. Remember you may have no hits, but the search rule is still valid and will catch future hits.

Finally, schedule that chip to run every night (See the next page).

In the second example, we look for all documents shared outside the domain.

We are going to run a daily scan to alert local owners that their documents may breach company policy by being shared out of the domain.

In the date range pick today’s date for daily audits, pick a week back for weekly audits, etc.

Next, select ‘Documents Changed’ and then click ‘Search Documents’, this will build your initial search ‘chip’. You further refine your search by selecting documents shared ‘Out’. Remember you may have no hits, but the search rule is still valid and will catch future hits.

Finally, schedule the full chip to run every night.

When you click Schedule/Save you will be taken to the scheduler, here you can select how you wish to see the report, as a CSV or a PDF, whether you want it to run as a policy or an audit (as an audit you get notified every time it runs, even if nothing is found, as a policy, you only get a message if a matching file is found) and the period over which it should run.  If you selected a week’s worth of data, run the report weekly, if it was a day’s worth, run the report daily.

Finally, you will see a checkbox, ‘Notify Local Users’, if you check this, local owners of the file will be warned that their files were found by the search, that they may be in breach of company policy and they will be given a list of the files.

How to Find Identical Files in Multiple Folders

Do you want to identify all instances of two or more copies of a Google Doc in different folders? This is now possible with GAT and the Drive Audit ‘Download Full Paths’ button.

find identical files with GAT

By default the ‘Drive Audit’ looks at the entire domain, but you can pre-filter by selecting an OU, Group or User before you press ‘Download Full Paths’. Pressing the button creates a CSV file download which you can then open and save as a Google spreadsheet. In this spreadsheet Column 2 shows the folders or labels and is titled ‘Path’. Column D shows the file Id. Files with the same id are identical. The results are grouped by ‘Id’

'Download Full Paths' feature in GAT

To highlight the files that appear as two or more copies follow the following steps.

Select Column D. Then select ‘Format’ from the top of the sheet, from the dropdown menu choose ‘Conditional Formatting’. A new table will open on the right hand side of your sheet. From here your range (Column D) should already be filled in, choose ‘Custom Formula is’ from the ‘Format Cells’ selection and add the following formula =countif(D:D,D1)>1

All rows with matching file id’s will now be highlighted by the ‘id’ cells having a green background. See screenshot below.

'conditional formal rules' feature

4 Examples of Google Docs Policy Enforcement Using GAT+ [Old UI]

A policy is what an organisation decides is best practice for the use of its Google Drive.

With GAT a Policy violation can manifest itself as

  1. a warning to the Admin,
  2. a warning to the Admin and document owner
  3. a warning to the Admin and document users
  4. a warning to the Admin and/or the owner and/or the users, and the removal of access.

Option 4 is often described as policy enforcement, and sometimes incorrectly as document ‘security’.  It is important to understand that it is not possible to do security enforcement using Google’s current APIs. Firstly removal of access is ‘after the event’, meaning the contents are already leaked and secondly, local Admins have no control over documents shared into the domain, they can not remove local access from these, they can only warn users that their access to these documents is being monitored.

In this paper we hope to show you four use cases of ‘Policy enforcement’ you can do using the General Audit Tool

Example 1Removing access to documents shared to personal accounts.

Here the organization allows documents to be shared with other business domains but does not want it’s documents shared with personal accounts (like gmail.com).

Example 2Removing all remote access to documents that contain the words “private and confidential”.

Here we do a text search of every document for the selected words and remove sharing if those words are found.

Example 3Removing external access to spreadsheets used by members of the group Finance.

Here we identify all the members of a Google Group and make sure one document type is not shared outside the domain (except to the Auditors).

Example 4Warning local users about organization policy with respect to images.

Here we check for all images shared into the domain and warn local users about the company policy on viewing images in the workplace.

For each of the examples, we show the Admin how to run the policy on an ongoing basis. To apply the rules retrospectively you can leave the ‘From’ date blank and run the policy as a scheduled job once. Before doing so, look through the file listing returned in your filter.  These are the files that will be changed. The beauty of integrating a policy engine with the drive audit is that you can see what will be affected. Having run the rule once, you can revisit the rule after its first run and set it up as a scheduled job, to run daily, weekly or monthly.

Example 1 – Removing access to documents shared to personal accounts

Image above is of the completed fields to build the policy. Expand the image to see it better.

Remember to select ‘Users’ and you can cut and paste the regular expression below.

([^\s]+(\@(?i)(gmail\.com|hotmail\.com|yahoo\.com))$)

Add your own popular local domains. ‘.’ is a special character, so we escape it with a ‘\’ in the expression. (Example is given to show you how to use regular expressions with GAT)

NOTE:

GAT also allows for the much simpler non-regular expression based listing of domains. To add this list simple type after ‘Users’ gmail.com,hotmail.com,yahoo.com

Select from today’s date to have this ready for a nightly scan.  

Leave the start date blank to see all the files that match. You can run the rule once with no start date to clean up your back history, then schedule it as a daily or weekly job.

When you click ‘Schedule/Save’ you are taken to the following scheduler configuration screen

Complete the fields to your own requirements.  Free text boxes can be dragged to expand.

See the update on this feature set at the end of this document.

Example 2 – Removing all remote access to documents that contain the words “private and confidential”

See the update on this feature set at the end of this document.

Example 3 – Removing access to spreadsheets shared by members of the group Finance.

See the update on this feature set at the end of this document.

 

Example 4 – Warning local users about organization policy with respect to images.

This is the sample Reg Ex for images used in the above example, you can cut and paste

([^\s]+(\.(?i)(png|jpg|gif))$)

png, jpg and gif are the image type ending, extend with your own selection, for example if you include jpeg the expression will be ([^\s]+(\.(?i)(png|jpg|jpeg|gif))$)

You can also include audio and video files in this sweep.

Remember we are only interested in images ‘Shared in’, so after the initial search, we narrow the filter by clicking on ‘In’

See the update on this feature set at the end of this document.

 

GAT has a unique concept of a ‘search chip’, as you add more filters the ‘chip’ builds up, eventually giving us something like this

  • Docs shared in not deleted with Document name [([^\s]+(\.(?i)(png|jpg|gif))$)] changed from 07/10/2013 23:00:00

These ‘chips’ can be scheduled, saved and even added to other chips. (see recent filters)

 

Example 5 – Free Bonus Example 🙂 – Removing remote access to a specific Folder

This is a very useful concept because instead of trying to protect the domain, it protects a working area, any file shared into the working area will be subject to its protection.

First, find the folder

Then find the contents by clicking on the arrow behind the directory name

Next click on ‘Out’ to add that filter to the ‘Chip’.

We have our search filter, ‘all files in this folder shared out’ 

Now let’s apply a policy.

Note, it does matter that there are zero files that match this search criteria – that just shows the folder is in compliance right now. If it were not, you would see exactly the files that were non-compliant, the owners and the external parties. This is really useful because it lets you have a discussion with the parties before applying the rule.

To add the policy, click ‘Schedule/Save’

In scheduling the job we can now set all our rules.

We set it as a ‘policy’, audits and policies do the same thing, but with a policy you only get notified if there was a breach of the rule, an audit will give you a status report everyday, even if there was no change.

Have the report output in CSV or PDF format.

Run everyday, after midnight.

Send a copy of the report to a particular manager

Remove the external shares.

Notify the local owners.

Send them a message, which can be in any language.

And finally, click ‘Update’ to activate.

 

This final example is interesting because it combines several features of GAT that are simply not available on other audit or security tools and adds them to a Policy engine that is at once both simple and powerful. The solution is fully integrated, you can see the results as you progress, you are not making the rule up in one place and hoping you will see the correct results in another place.  Before you affect the policy, you can see it built up on your live data in front of your eyes.

Everything follows from Audit and of course the Audit has to be accurate in the first place. If your audit tool is still not finding all the files, it is not even off first base.

 

Update on Audit and Policy Scheduler

We have greatly enhanced the features on the scheduler page, see the screenshot below.

 

Portfolio Items