Dealing with the pandemic with Google Cloud – office activity monitoring

8 October 2020, Łukasz Maczułajtys

COVID-19 pandemic has radically changed the way companies work around the world. To avoid infection, all Pretius employees have been working remotely for half a year now. Even though, as a technology company, we have no significant problems with working in a distributed environment, some employees need to visit the office from time to time.

This text will show how Pretius used Google Cloud Platform to create reports showing real-time and historical data about employees’ presence in the office. The solution is used to identify people at risk of virus infection quickly.

Challenge

We wanted to monitor who visited our office on a given day. In the case of the coronavirus infection in the company, it would help us to identify quickly:

  • when an infected employee was in the office
  • who was in the office at the same time

It was crucial for us to promptly get all the necessary information and inform all affected people and sanitary services.

Idea

It is possible to ask the office complex administration for logs from electronic locks to check when and which cards were used. But it takes a lot of time to get those logs, and it takes even more time to do all the necessary analyses.

Since employees use computers and other electronic devices connected to our network, we decided to use the information stored in the network services logs. The built solution provided real-time insight into the devices and their owners present in the office. It doesn’t require any employee to do anything extra.

Solution

The solution was built on the Google Cloud Platform using serverless, quickly accessible, production-ready services.

GCP solution architecture
Solution architecture

Network activity logs collected by DHCP and VPN services are continuously ingested from on-premises servers and parsed by Blue Medora BirdPlane – hybrid cloud solution for on-premises logs and system metrics collection and loading into Google Cloud. The data is stored in Cloud Logging – real-time log management and analysis service. All the information is available for full-text search and advanced filtering in one place. It is also possible to create log-based metrics, alerts, etc.

Cloud Logging in action
Cloud Logging in action

To provide the logs data in the form of a user-friendly report, we used Logs Router – a Cloud Logging feature enabling an automatic export of chosen logs to BigQuery. BigQuery is a petabyte-scale serverless data warehouse. The whole setup takes minutes, and the data is accessible through SQL queries with powerful extensions.

The data from BigQuery is presented in an interactive dashboard built in Google’s free reporting solution – Data Studio. Plenty of ready-to-use visualizations, aggregate, and transformation functions make the data analysis really simple and fun.

Data Studio report
Data Studio report

Results and cost

The real-time and historical information about who was present in the office on a particular day is easily accessible to employees with proper clearance. We have already used the solution to handle a COVID-19 incident in our office.

We can also analyze situations when many people visit the office at the same time and check if it impacts employees’ safety in the current pandemic situation.

Office presence histogram
Office presence histogram

The solution was built in one day by our two network and cloud experts. The whole cloud infrastructure costs us 0 USD. Logs ingestion and BigQuery storage and queries fit GCP Free Tier. It will work even if the company grows twice (now Pretius hires 100+ people).

New possibilities

The presented architecture can be used in many different ways. Pretius uses it to:

  • monitor traffic in our on-premises network to detect anomalies and possible attacks;
  • store audit data about employees accessing our customers’ resources;
  • collect and analyze access logs from applications. For example, we can check how many users read our internal newsletter published on Confluence or detect excessive permissions to restricted pages.

Let’s talk about how our experts can solve your issues using the public cloud. Contact us.

Hire us!

Pretius is a software development company.
We create web applications using: Java, Oracle DB, Oracle Apex, AngularJS.
Contact us to talk about how we can help you with your software project!