How to Create an External Users Report

Occasionally you may be asked to report on all the external visitors or editors to files shared out from your domain. This worked example is broken into two parts. The first part will show you how to find the files that were edited by external users or read by external users.

Note: This is different to finding the files shared as read ‘read’ or ‘edit’ to external users. Just because your users allow someone to edit a file by sharing it with them, does not mean the external party edited that file.

The second part shows you how to find the files shared out, then extract the reader and editor history for each file, then eliminate all the readers and editors from your domain, and produce a report that contains the external files that have specific external readers or editors and the times of their actions.

These examples contain useful Reg. Ex. selection and Google ‘Filtering’ examples.

Note: If a file is made ‘Public’ or ‘Public with Link’ then Google do not return names or times for visits from the public.

Find all files that were edited by external users or read by external users

To find all the files edited by people not on your domain you go to the GAT Drive audit and from the ‘General Search’ selection selected ‘Edited by’.

To find all editors NOT from your domain we are going to need to use a regular expression. The expression used is

[a-zA-Z0-9-]+@(?!your-domain\.com).+

Replace just the ‘your-domain’ (and ‘com’ words if required) with your own domain name. DO NOT change any other character. Be sure to tick ‘Reg. Exp.’ (If you need another ‘.’ in your domain name, remember to escape it with a ‘\’, so you get \. )

GAT filters

Then click on ‘Search Documents’ to find all the files that were edited by non-local users. Finally select ‘Out’ from the returned results because you are only interested in files shared out, that external people have edited.

Repeat for ‘Visited by’.

Find all files shared out, then extract the reader and editor history for each file

In the Drive Audit, select all files shared ‘Out’

GAT features

Next select ‘Export Visits to Spreadsheet”

GAT report: visits to spreadsheet

If you have thousands of files this may take a long time.

When finished you will see a screen like this.

GAT finished reports

Select ‘Open Actions …’ (link in blue).

You will now see the spreadsheet in Google Docs.

Select Column ‘E’ and insert a new column to the right.

In the cell F2 paste this formula (replacing YourDomain with your domain name)…

=REGEXMATCH(D2,“YourDomain”)

Copy that cell (select the cell, then CTRL C, make sure it has blue broken line box around the cell), select the entire column (Click on ‘F’), paste the cell (CTRL V).

Column F should now be filled with TRUE or FALSE

TRUE is the editor or visitor was from YourDomain.

FALSE if the user is from any other domain.

GAT spreadsheet

Next, select the filter icon.

Sheets filter

Move to Column F and from the first row select the filter choices

spreadsheet

Turn off ‘True’, click ‘OK’

You should now be left with every editor and every visitor and the date and time of their action to every external shared file from your domain, excluding users from YourDomain.

Comments

comments