Posts

GAT Search Choices Explained

New Filters

One of the key features of GAT+ is it’s very powerful Drive audit search capability. Its power comes from its ability to use so many search operators and parameters to find files based on a multitude of different aspects of its metadata. Navigating through Drive audit you will notice how quickly the data loads.

We have even dedicated an entire post to talk about powerful things you can do from the Drive Audit list.

Let us examine the options in detail.

Title – Can be the file name or any part of the name.

Note: GAT remembers document name history, so if someone renames a document GAT will return matches against the new and old names.

File ID – Is the ID for the file in question.

All files have an ID which can be found in the URL of the file or in GAT+ you can click on the title which will display the full ID.

MimeType – MIME stands for Multi-purpose Internet Mail Extensions. MIME types form a standard way of classifying file types on the Internet.

Here are a few examples:

  • image/png
  • video/mp4
  • application/pdf
  • audio/wav
  • text/css

Flags – State conditions applied to files.


Restricted – Whenever the file is prevented from being downloaded, printed or copied.

Editors can’t share – This flag is self-explanatory and refers to files not being able to be shared by editors.

  • Team Drive Extra ACLs – Some files within Team Drives might have additional sharing settings, for example, a TD file can be shared out with a link.
  • ACLs Changed – ACLs Changed is set when a super admin makes some changes through GAT+ (e.g. remove editor/reader, change owner etc.)
  • Title Truncated – Some files have reaaaaaaaaaaaaaaaaally long file names and we’re forced to truncate them so that they can be indexed.
  • Incomplete data – When changes are made to some files using GAT Unlock the data in the database can be out of date.

Sharing Flags – This flag covers all of the scenarios a file can be exposed.

Anyone in Domain – Anyone within your domain (myOrganisation.com)

Quota Bytes – this parameter refers to the size of the files. Native Google files do not display any size details but all non-Google files do. This parameter takes Bytes. For example:

1 Kilobyte = 1024

1 Megabyte = 1048576

1 Gigabyte = 1073741824

The below example will return all files greater than 1 Gigabyte:

Type – The most popular file type extensions are shown with this search parameter.

Users – Anyone who is an Owner, Editor or Reader of a file. Can also be entered as a regular expression.

Owner – Anyone who is the owner of a file. This can be a full email address or a partial address.  For example to find all files owned by joe@gmail.com

You can select the following:

Owner equal joe@gmail.com or Owner contains (case insensitive) joe

You can also use the contains (case insensitive) to find all files owned by gmail accounts for example:

Owner contains (case insensitive) gmail.com

Editors – Anyone who is the editor of a file. Same search criteria as ‘Owners’.

Readers – Anyone who is the viewer of a file.

Created – When a file was created/uploaded into Google Drive.

Updated – The updated date field changes whenever certain actions are taken. Please see below:

  • File permission changes (add/removing editors or reader, add/removing internal or public share)
  • A file has been edited
  • A files name has changed

Updated is NOT changed whenever:

  • A user is viewing a file.
  • A user is moving a file (surprisingly!).

Advanced Filters

Full Content Search

Using the Advance filters, select ‘Full Content Search’ by default ‘Simple Filter’ is ON. ‘Full Content Search’ allows you to find files which contain specific words or sentences. Even in images and or videos using Googles OCR (Optical Character Recognition) technology.

Examples:

“Credit Card” – This will return files which contain exactly this sentence.

Credit Card without the quotation marks will return files which contain the words Credit and/or Card. If you don’t specify a scope either by entering a user, Google group or Org Unit it will return filtered files for all non-deleted and non-suspended users by default which contain the query.

Title / Description Search

Title / Description Search queries are performed using only files metadata, that is only text columns presented in Drive result table. Contents are not considered. This is a very fast method of finding files using their title or description.

Example 1: The following example below finds all file records containing ANY terms from the list: “java”, “shop” and “coffee” with a file’s title or description.

Example 2: You can also search for exact phrases by wrapping them in double quotes. For example, the following finds all records containing “java” or “coffee shop”:

Example 3: To exclude a word, you can prepend a hyphen “-” character. For example, to find all file records containing “java” or “shop” but not “coffee”, use the following:

Sorting by text score

GAT returns results in unsorted order by default. However, tile / Description queries compute a relevance score for each record that specifies how well a record matches a query.

Also, each text column has a weight which denotes the significance of this column relative to the other ones in terms of a text search score. The order of importance is:

title (10), description (5), owner (4), organizers (4), writers (3), readers (1)

For each column, GAT multiplies the number of matches by the weight and then sums the results. Using this sum, GAT then calculates a score for a record.

To sort results in order of relevance score, you must enable the following option:

It’s disabled by default.

Case Sensitivity

Terms queries are case insensitive by default. You can make them case sensitive by enabling this option:

It’s disabled by default.

Note also that both the options (case sensitive and sort by text score) can be combined:


Additional Resources

Who Read What Document and When?

This question comes up from time to time and GAT makes it easy to find the detailed answer.

 

First, we find all the documents the person reads (or has ‘viewed’).
To achieve this we navigate to the Drive audit in GAT+. We select the Event tab, then we apply a custom filter.
For the search we enter the email address of the person we’re interested in, then pick an event type as “View” (you can select different options such as download, upload, print, created).
You can select a date parameter to narrow down your search results (If you don’t put date it will scan the user and event type and find every document ever read by that person).

 

The example in the screenshot below will display all events (view) by the user in the past 29 days(since the start of month).

 

However, we can refine this further…

Let’s exclude files where this user is also the owner of those files because we are only interested in files this user viewed which are not his own.

To do this export the results, this will create a spreadsheet where we can edit the owner tab to exclude the person you are currently searching for.

The result you get is all the documents visited by the subject, excluding the files he owns.

A follow-on question from this is how do we create daily/weekly reporting for all or some documents in our domain?

We have a post about how to schedule daily/weekly report on event activities on files you can read more about that below here:

How to Track Visitors and Editors

How to Find if the Contents of a Folder Changed

Here is a frequent question we get from our G Suite super admins who use GAT+.

‘I want to know when the contents of a folder change?’ In GAT+ we can automate and set up a scheduled report to give us this information.

Go to Drive Audit, Search for the folder you are interested in using the Apply custom filters button.

"Drive" audit section

Select this filter

Select the following search parameters:

    • Make the type of the search equal to a ‘Title / Description Search’
    • Enter the title of the folder into the Terms field.
  • In the Definition area, select the search parameter Type equal Folder and also Owner equal to user’s email address.

"filers filters" section

Once the folder appears in the Drive result table. Click on the drop-down menu next to the title of the folder. Select the option to ‘Show contents of this folder and its subfolders’. Selecting this option will expand the folder completely open and all files within its folder tree will be displayed.

Select the option to ‘Show contents of this folder and its subfolders’.

Now a search will start. You can refresh the screen to see if the search is complete. Once its done, Apply the filter.

"long search" filter

Once we access the content of the folder we can make another search to find files which have been updated since yesterday. The reason why we are looking back a single day is that we want to create a scheduled report which runs daily and shows us files which have been updated in this folder, this report will run automatically each day and it will update the dates selected and increment it to reflect the new day.

Select filter

Underneath the Long Search ID parameter, add another rule called Updated after or equal yesterday’s date. The select the option ‘Scheduled’.

Underneath the Long Search ID parameter, add another rule called Updated after or equal yesterday’s date. The select the option ‘Scheduled’.

select "apply & schedule"

The report will be shared with you via email with a Google spreadsheet attached.

If this receives 0 results in the spreadsheet it just means nothing changed in that folder since the previous day.

Audit Google Team Drives Users and Activity with GAT+

Applying a search for Team Drive files

 

Use the exposure summary table in Drive Audit to quickly display all of the files within your Team Drive for all of your domain users.

Use the exposure summary table in Drive Audit

In the above example, our domain has a total of 1778 Team Drive files. Once you click on exposure summary table for Team Drive files a filter will be automatically applied with the following search parameter selected.

Drive files filter option

Don’t hesitate to build on top of this filter search. Let’s search for Team Drive files which have been updated in the past few months and which are images and docs only. Follow the steps below to achieve the same search:

  1. Clicked on the ‘Add rule’ button.
  2. Select the Updated search parameter and then select ‘after or equal’ and then enter the months of interest.
  3. After clicking on the ‘Add group’ button.
  4. Select the OR operator so the search parameters in this group will be OR’d together.
  5. The first search parameter was Type is equal to ‘Image’.
  6. Click on ‘Add rule’ button to create the second search parameter.
  7. The second search parameter was Type is equal to ‘Doc’.
  8. Apply the filter.

Click on the button ‘Show stats for the current filter’,

To know the exact number of Team Drive files which have been updated in the past few months and are images or docs. Click on the button ‘Show stats for the current filter’, this will run a search and calculate what file types are appearing for this current filter.

‘Stats for the current filter’ will take some time to generate the results.

The ‘Stats for the current filter’ will take some time to generate the results.

A look at what the "stats of the current filter" feature shows

View Events History for Team Drives

 

In the Files Tab of Drive audit, apply a search filter for Team Drive files, once the filter is applied click on the Events Tab. This will show you all of the events carried out on those files.

Click on the 'events' tab

expanded info in the feature

See Where Certain Files Are On The Domain ‘Drive’

Keeping an organised Team Drive or myDrive folders structure is important so important that GAT+ has a specific tab called Folder Tree. The Folder Tree audit area lays out all of the folder structures for all your users myDrives and Team Drives.

 

You can now search for a specific Team Drive or users myDrive. Once you’ve located the folder you can descend through the folder tree.

You can now search for a specific Team Drive or users myDrive

Click on the folder name to display the ‘Detailed view’ and the ‘Files list’.

Click on the folder name to display the ‘Detailed view’ and the ‘Files list’.

See the detailed view

In the ‘Files list’ you can apply a custom to filter to find files based on a multitude of different search criteria.  You can click on the drop-down menu for any file and remove a particular editor or reader.

apply a custom to filter to find files based on a multitude of different search criteria

 

How to Easily Identify and Organise Orphaned Files in G Suite

Orphaned files are those that a user has access to but do not exist in any folder for that user, including the root folder. They are a particular problem because the Google Drive client app on PC’s allows other users to delete a folder and leave the files orphaned. GAT now allows Admins to find all orphaned files for any user.

Here is a detailed how-to video that shows you how to find and fix orphaned files:

Displaying Orphaned Files

In the Drive Audit, click on the “Apply custom filter” button. This is one way to display orphan files.

apply custom filter in GAT's Drive Audit

When the filter menu appears select the “Flags” operator contains “Orphaned”. Then press apply.

Drive files filters tab

You can easily display orphaned files by using the exposure summary table. Click on Orphaned.

see number of orphaned files in GAT filter

Fixing Orphaned Files

 

Once you are ready to fix the orphan files, Click on the “Files operations” button, then select “Fix orphans”.

 

A popup menu will appear, you will be able to perform the fix in 2 ways, one way will fix all of the orphan files for everyone on the domain who has them or you can select individual users and fix their orphan files.

 

A folder will be automatically created on the users myDrive, the folder name can be changed to whatever you prefer, by default its called Orphaned.

Selecting the “Process all users” option will fix orphaned files for all domain users who have them.

select 'process all users'

Or you can fix them for individual users in small batches if you prefer.

update 'users'

Visualizing specific G Suite relationships or workloads

Specific Use Case

Specific use cases for these features include decommissioning users, analysing relationships, understanding the state of a business relationship with a specific company, identifying contacts with a specific company, assessing workload and many other important Business Intelligence tasks.

Read more

What is collaboration in G Suite?

What is collaboration?

Google, rightly, talks a lot about the importance of collaboration and about how G Suite enables and enhances the collaborative experience in the workplace.

We have thought hard about what collaboration might be and how we could measure it in the Google environment. Sharing a document with someone is not collaboration, it is a connection. Only when that document is read or edited does it become collaboration. Likewise firing emails off at targets is not necessarily collaborating with them, only when replies come and threads are built up do we start to collaborate. In all, Google currently gives us three areas where we can, with reasonable accuracy measure collaboration, these are documents, emails and calendar appointments.

From this data, you can estimate how each individual is collaborating (at least inside G Suite) with everyone else. However, for organizations, it can be even more interesting to understand how teams are performing and how strong the collaborative performance is over a period of time (by default we look at the last 6 weeks)

Group Analyses

As mentioned a collaboration score for an individual is useful to know, but in fact from a G Suite perspective the collaboration inside a group is something that is really interesting. Google groups are core to how we split up our workforce. Groups can cross OU boundaries and can often represent teams.

Groups collaboration needs to be measured along three axes, volume, degree and time.

Volume represents the amount of collaboration between the members of a group.

Degree represents the number of members each individual member collaborates with.

Time represents the period over which collaboration happens.

GAT, because of its highly accurate auditing can measure not just connections, but collaboration for not only docs, but also for emails and calendars. In addition it can do this over a period of time and because it understands Google groups, it can calculate both the volume and degree for each group.

Group analyses presents this information to you.

In a simple, yet powerful table, GAT pulls out every Google group on your domain (presents groups of 5 or more members by default but you can select smaller), looks at the internal behaviour of every member of each group over the the last 6 weeks (you can set your own time window) and then lets you rank the groups by highest average volume and/or degree of collaboration. In one click you can see which groups are working for you as collaborating groups and which you need to look at more closely to see if that group is still useful.

To explore a group in detail, just click on the group name. You will see the group is expanded to show you the individual detail behind the collected averages. This can be very useful to help identify the top collaborators in a group.

Using this data you can start to correlate other performance indicators with collaboration. You can now start to ask and answer questions like ‘do collaborating sales teams close more deals?’, ‘do collaborating support teams have higher customer satisfaction scores?’. It will also allow you to identify unnecessary groups, perhaps allowing for them to be formally wound up, allowing for the better use of resources.

Using some carefully chosen mathematics (to accommodate the fact that degree is upper bounded by group size) we blend both collaboration event and degree to give you a single unique ranking system taking into account both Volume and Degree. This will allow you to sift the highest collaborating groups to the top in a single sort.

Only with G Suite and GAT+ is this sort of analyses now possible.

 

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

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.

Security Tips for the Google Apps Environment

Google Drive

‘Bin’ or ‘Trash’ is just a folder. If a user moves a file, which was shared out, from ‘My Drive’ to ‘Trash’, the file is still shared out, still visible and still subject to changes. Files do not automatically leave trash. Users should know moving a file to ‘Trash’ is not a solution to a sharing violation.

Your audit tool must audit trash correctly. Shared trashed files must be deleted to remove the security risk. Deleted files must be kept in an audit log.

A file shared into your Domain with Edit rights is just as big a security risk as a file shared out with edit rights. Tracking files shared out of your domain only addresses part of the data leakage risk on Drive. You must be aware of the files shared in with ‘Edit’ rights. Policies must work for file shares in both directions and ideally for internal and external shares.

If you were using files in a shared folder and another user deletes the folder, the files become ‘orphaned’ on Google Drive. The files are there, but they are not in ‘My Drive’ or any other folder. Files that disappear are typically orphaned. GAT lets Admins and Users find orphaned files. Orphaned files may remain fully shared, even public. Out of site for your users does not mean out of sight for externally shared or public files.

Learn how to easily identify and organize orphaned files.

Your audit tool should extend to the end user. Admins are often not the right people to assess the risk or the provenance of a file. End users know their own files best. End users should be shown how to do audits and encouraged to do them frequently.

 

Passwords

Passwords of any length and any change frequency are almost waste of time as a security device. Most password attacks now are not dictionary driven, but keyboard scarpes. Google Apps are particularly vulnerable to password loss by this method because of the of the access from anywhere, anytime model. Home PCs are used to access corporate networks. Public spaces with cameras on users. Airport kiosks. All present an opportunity for a keyboard scrape. Enable 2FA and use either a code or a fob to provide additional security. If any part of your security model is solely based on passwords and frequent changes you are deluding yourself into a false sense of security. GAT reports 2FA status by user and you can schedule reports for non-2FA accounts.

 

Login location

Carriers often obfuscate the true location of the IP address used to make a Google Apps login, but they do not do so at random. Admins should familiarise themselves with the regular IP locations for all logins to their domain. Admins should investigate logins from unexpected locations. GAT tracks and maps IP address locations for connections to your domain. Suspicious or failed logins on Google mean very little to Admins on their own, they need to be seen in the context of where they are coming from. See this post on the subject.

With GAT, you can set an alert type based on IP address or IP subnet.

User Behaviour

A change in user behaviour is often a sign that should alert a security conscious Admin. Changes in behaviour include increased or excessive file shares or emails. It is important to know the regular volumes for your domain. GAT can alarm when it detects thresholds set by you are exceed for files shared in or out, or emails sent or received.

 

Third Party Apps

Marketplace Apps can be installed at Admin console level, by end users as document, spreadsheet or browser extensions and as browser-based apps. These are all different. Marketplace Apps reported by Google only represent a small portion of the apps users install.

Blocking Third Party Drive Apps does not necessarily cover Chrome extensions. If you are not restricting both these types you need an audit tool that can audit, risk assess and alarm and enforce policy on new instances of both Drive Apps and Chrome extensions. GAT can cover all these areas. It can apply policy by user, group or OU.

 

Idle Accounts

Accounts that have been idle for a long time that suddenly become active should attract the attention of an Admin, likewise accounts that have suddenly gone quiet. Is HR keeping IT up to date on personnel changes? Are departed employees coming back into their accounts? GAT can alarm you when it detects thresholds for idle account times have been breached.

 

Idle devices

Devices that have been inactive for a long period and suddenly become active may be a security risk. Likewise, a device that has gone quiet. Has the user reported it missing or stolen? Was it thrown in a drawer for a kid to use later? Is the new user suddenly reading the finance files? GAT can alarm when it detects thresholds for device syncs have been breached.