In this page you will find example queries for your AWS data.

  1. AWS instance creation events

SELECT *
FROM AWS_CLOUDTRAIL
WHERE EVENT_TIME BETWEEN '2020-11-23' AND '2020-11-25'
AND event_name = 'RunInstances'
AND RESPONSE_ELEMENTS ilike '%i-%'
LIMIT 10;
CODE

2. AWS web console logins of a user over a period of time

SELECT *
FROM RAW.AWS_CLOUDTRAIL
WHERE EVENT_TIME > dateadd(day, -14, current_timestamp()) -- last 14 days
AND event_name = 'ConsoleLogin'
AND USER_IDENTITY_ARN ILIKE '%username-here%' -- enter username
ORDER BY event_time DESC
LIMIT 5;
CODE


3.Creation of users in AWS

SELECT *
FROM RAW.AWS_CLOUDTRAIL
WHERE EVENT_TIME BETWEEN '2020-11-23' AND '2020-11-25'
AND EVENT_NAME = 'CreateUser'
AND RESPONSE_ELEMENTS ILIKE '%username-here%' -- enter username
ORDER BY event_time DESC
LIMIT 5;
CODE

4. AWS web console logins for a specific User-Agent

SELECT *
FROM RAW.AWS_CLOUDTRAIL
WHERE EVENT_TIME BETWEEN '2020-11-01' AND '2020-11-30'
AND EVENT_NAME = 'ConsoleLogin'
AND USER_AGENT ILIKE '%Mozilla%' -- enter user agent
ORDER BY event_time DESC
LIMIT 5;
CODE