This how-to guide shows you how to apply transformations to your VPC Flow Logs.
In our Data Ingestion - VPC Flow Logs guide, we showed how to load your data into Upsolver, refine it, and prepare it for transformations. Below are examples of transformations you can apply to your refined data.
Show who deleted an object and when (timestamp, IP address, and IAM user)
SELECT RequestDateTime, RemoteIP, Requester, Key
FROM s3_access_logs_db.mybucket_logs
WHERE key = 'images/picture.jpg' AND operation like '%DELETE%';
Show all operations performed by an IAM user
SELECT *
FROM s3_access_logs_db.mybucket_logs
WHERE requester='arn:aws:iam::123456789123:user/user_name';
Show all operations performed on an object in a specific time period
SELECT *
FROM s3_access_logs_db.mybucket_logs
WHERE Key='prefix/images/picture.jpg'
AND parse_datetime(RequestDateTime,'dd/MMM/yyyy:HH:mm:ss Z')
BETWEEN parse_datetime('2017-02-18:07:00:00','yyyy-MM-dd:HH:mm:ss')
AND parse_datetime('2017-02-18:08:00:00','yyyy-MM-dd:HH:mm:ss');
Show how much data was transferred by a specific IP address in a specific time period
SELECT SUM(bytessent) AS uploadTotal,
SUM(objectsize) AS downloadTotal,
SUM(bytessent + objectsize) AS Total
FROM s3_access_logs_db.mybucket_logs
WHERE RemoteIP='1.2.3.4'
AND parse_datetime(RequestDateTime,'dd/MMM/yyyy:HH:mm:ss Z')
BETWEEN parse_datetime('2017-06-01','yyyy-MM-dd')
AND parse_datetime('2017-07-01','yyyy-MM-dd');
