top of page
horizontal lines
Gigasheet Primary logo
  • Ankit Vora

How to Open Large AWS CloudTrail Logs

Did you know that Amazon Web Services has more than a million active users? Enterprise customers make up nearly 10% of the overall AWS users whereas the rest are Small and Medium Businesses (SMB's).

With more and more companies moving to cloud computing, we’re witnessing on-demand cloud computing platforms like AWS, IBM Cloud, Microsoft Azure, Oracle Cloud Infrastructure (Gen 2), and many more leading the revolution.

And while transitioning to the cloud has helped companies save money and resources, organizations should easily be able to analyze and understand activities or events happening in their AWS environment.

Whether you want to monitor user changes, security risks, and/or compliance, AWS CloudTrail is Amazon Web Services’ integrated service to perform risk management, compliance, and governance checks on the cloud. Learn more about AWS CloudTrail here.

And while you can monitor and filter AWS CloudTrail log data using Amazon CloudWatch, the only problem is – it’s complicated if you have no experience running command line queries or lack technical expertise.

Using Amazon CloudWatch to Monitor & Filter CloudTrail Log Data

AWS CloudWatch Logs Insights is an integrated interactive log analytics capability that allows AWS users to query the CloudTrail logs and analyze the trend of API activities. In simple words, you can use AWS CloudWatch Logs Insights to search and analyze your AWS CloudTrail log data.

But the only problem is – if you want to dive deep into the data, you need to familiarize yourself with Amazon CloudWatch query commands. Following are a few sample queries we fetched from AWS Docs for CloudTrail Logs:

Queries to Use to Filter AWS CloudTrail Logs
AWS CloudTrail Log Queries

Learn more about how to monitor and analyze AWS CloudTrail Log Data in Amazon CloudWatch here.

But if you’re someone with little-to-no technical experience and like to filter data using the old-fashioned way (using spreadsheets!), you can always use Microsoft Excel or Google Sheets.

How to open and analyze large AWS Cloudtrail Logs

Using Microsoft Excel or Google Sheets to Monitor & Filter CloudTrail Log Data

First, it’s important to note that AWS CloudTrail log events are stored in JSON format. While you can also use jq – a lightweight + flexible command-line JSON processor - if you have little-to-no technical experience running jq queries, you’ll face a difficult time getting your hands on the data you want.

Two good alternatives that’ll allow you to analyze and filter data in good old-fashioned manner are – Microsoft Excel & Google Spreadsheets.

Large spreadsheets are always a problem, especially if you’re using Microsoft Excel or Google Spreadsheets. However, when it comes to the simplicity of using spreadsheets to monitor and filter your data, there’s nothing better.

BUT! There are potential performance issues with these spreadsheet options that you should know about.

Using Microsoft Excel to Monitor & Filter AWS CloudTrail Log Data

You can directly open your AWS CloudTrail log data file using Excel. However, the data will be all messed up. Here’s what it’ll look like:

The Wrong Way of Opening a JSON file in Microsoft Excel
Opening a JSON file in Excel

To ensure that your AWS CloudTrail data is structured right in Excel, you need to import data using Excel’s in-built import functionality.

Here’s how to do it: