A commonly overlooked feature of the Google Workspace Enterprise Collaboration and Productivity platform is the ability to save logged events and usage reports for a custom retention period. This is accomplished through the Enterprise and Education feature Set up service log exports to BigQuery. 

By default, Google Workspace retains access logs and reports for only 6 months on most types of logged data and purges older logs with no option for administrators to change this behavior. A recent Google Workspace update to the export logs feature has enabled near-real-time exports of logged data, allowing for faster processing and retrieval of recent events

The same type of export can be set up for the Google Workspace Email Log Search Tool. Any admins who have investigated email activity may know that this email metadata is not searchable in the native console if it is older than 30 days, unless you include both a recipient and message ID as search parameters. This makes any type of investigation into past activity difficult to conduct. The Google Vault eDiscovery and Retention platform can be helpful in this regard if it is configured to retain data properly. It is important to note that the Email Log Search Tool does not include message content other than metadata such as date/time of messages, sender/recipient and matched Gmail rules. 

Some of the immediate benefits of these features include complying with laws and regulations that organizations may be subject to, as well as being useful for investigations in the event of a cyber incident. Once logs are ingested to BigQuery, administrators in Google Cloud Console can set default expiration times for the data. Changing the data expiration is advisable because the default expiration is just 60 days before it is deleted from Google Cloud. All logs are searchable with SQL queries once ingested to BigQuery, and Google provides several example queries for each logged data type that is currently available. 

In addition to investigations and longer retention of logs, this type of data can be transformed into new projects including dashboards, rich reports, usage analytics and more. Tools such as Google’s Data Studio and Looker can be used to visualize and explore this data. This visualization capability is especially valuable to organizations that may not be on Google Workspace Enterprise editions that offer pre-built graphs in tools such as Work Insights and the Security Dashboard.

Utilizing logging data within BigQuery and visualizing this in Data Studio or Looker

Below is a sample from BigQuery of Google Workspace Calendar logging data exported into it by following the export process defined in the enterprise and education feature Set up service log exports to BigQuery.

BigQuery is Google’s fully managed serverless, highly-scalable and fully-managed data warehouse designed to allow for performing effective analytics on your data, with the added benefit of being able to utilize the power of GCP, Looker and BigQuery’s AutoML to provide additional in-depth analysis on your data, as described in its documentation.

Once Workspace logging data has been exported into BigQuery via the process described above, the attached BigQuery best practices should be considered to achieve the best possible results and efficiency with your queries and achieve cost optimization within BigQuery.

Below are some examples of more meaningful insights into the logged data, using either Data Studio or Looker as per the below samples from Data Studio and Looker dashboards.

Visualizing logging data in Data Studio

Data Studio is a Google tool that enables users to build visualizations from various data sources (including BigQuery). The above example is a sample of the types of visualizations we can build from our Workspace data exported into BigQuery.

Data Studio itself can connect to numerous types of data sources, including databases such as BigQuery and CloudSQL, Google Marketing Products including Google Ads and Google Analytics, flat files and other Google resources such as Google Sheets.

This makes Data Studio a simple-to-use, user-friendly tool that can quickly produce insightful visualizations from your Workspace data and also allow you to collaborate with your team by sharing your Data Studio dashboards with colleagues across your organization.

Here is a guide to creating the above Data Studio Dashboards so that you can replicate this process.

Visualizing logging data in Looker

Looker is an effective business intelligence and big data analytics platform that you can use with your BigQuery data to deliver quality insights to your business.

The above example takes some of the sample data from the exported Workspace data into BigQuery and transforms it into dashboards similar to the Data Studio example shown before.

The edge Looker provides is the ability to use Machine Learning (ML) techniques to drive further insights from your data. For instance, in the case of our Workspace Calendar data, the ML features can be used to provide suggestions for future calendar events.

Looker users can also drill into any of their visualizations and examine them in BigQuery or use the SQL queries generated by Looker to perform a more finely-tuned analysis of the data using the Data Warehouse’s full serverless power. As a Looker user, you can also apply various conditional formatting techniques on your dashboards to display different elements of your data in different formats according to your use cases.

A useful feature in the case of Google Workspace logging data is the ability to schedule the delivery of your dashboards using the Looker Scheduler. You can also add alerts to your dashboards if you expect to see certain metrics met in some cases.

Wrapping up

If your organization is not already exporting Google Workspace logs to BigQuery for longer retention and for building custom visualizations based on the data that matters most, let’s connect and explore how data-driven insights can improve your security, compliance and productivity across the Google business tools you use. 

DoiT International offers expert consultancy paired with unlimited, world-class support to customers of all sizes across Google Workspace and Google Cloud Platform and was recently named the 2021 Google Cloud Sales Partner of the Year.

Thanks to Matt Richardson, whose contributions made this blog post possible