matwerber1

-- I was helping a company using Google Workspace for email, and they were having challenges
-- with email deliverability due to domain reputation. While it's true that gmail provides
-- provides detailed logs, I found their docs to be only marginaly helpful and lacking
-- more robust, real-world ways to interpret the data. I also struggled to find quality examples
-- when searching unofficial sources (blogs, etc.). 
-- 
-- The query below is my best effort attempt to translate gmail logs into something half-way usable. 
-- I'd be shocked if there weren't mistakes or areas for improvement, but either way, sharing in
-- the hope that it at least helps give a jumping-off point for anyone else that finds themselves
-- saying "OK, I enabled Gmail Logs for BigQuery.... now what?".

matwerber1

CLUSTER="your_cluster_name"
REGION="cluster_region"
PRIVATE_SUBNETS="subnet-someSubnet123,subnet-someOtherSubnet456"
TASK_SECURITY_GROUP="security-group-for-ECS-task-ID"
PUBLIC_IP_SETTING="DISABLED"
TASK_CONTAINER_NAME_FOR_ECS_EXEC="container-name-for-ecs-exec"

# Run a Fargate task with ECS Exec enabled:
RUN_TASK_RESULT=$(
    aws ecs run-task \

matwerber1

-- Query assumes your Athena table is partitioned with a "day" column as described here: 
-- https://www.linkedin.com/pulse/using-athena-partition-projection-query-cloudtrail-other-kinsman/

with detail AS (
SELECT
  day,
  eventtime as timestamp,
  account,
  awsregion as region,
  eventid,

matwerber1

Google sheet formula to convert a ISO86001 string to a date time:

Input: 2022-06-23T16:31:34.036000+00:00 (cell A2)

Formula: SUM(SPLIT(left(A2,len(A2)-6),"T"))

Output: (a properly-formatted datetime)

matwerber1

#!/bin/bash
# Program:
# EC2 initially install node.js, git for development environment.
# You can modify nodev and nvmv for changing node and nvm version.
# Set permission to ec2-user install above.
# History:
# 2017/07/25	Hans	First release
home=/home/ec2-user
nodev='8.11.2'
nvmv='0.33.11'