Skip to content

AWS

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;
    

  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;
    

  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;
    

  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;