Skip to main content

Query Cloud Trail logs with Data Query

Learn how to analyze and query Cloud Trail logs stored in Object Storage using Data Catalog and Data Query.

Basic information

About this scenario​

This tutorial walks you through storing Cloud Trail logs in Object Storage, then querying those logs with SQL using Data Catalog and Data Query.

Key topics covered in this tutorial:

  • Configure Cloud Trail logs to be stored in an Object Storage bucket as gzip files
  • Create a database in Data Catalog connected to the log bucket
  • Create a table in Data Query and query log data
  • Appendix 1: Project event query examples
  • Appendix 2: Domain event query examples
  • Appendix 3: Event log fields for reference when querying

Before you start​

To query Cloud Trail logs with Data Query, you must first create an Object Storage bucket. Prepare a bucket by referring to Create an Object Storage bucket, then proceed with the steps below.

Getting started​

Step 1. Enable Cloud Trail log storage​

Configure Cloud Trail logs to be stored automatically in Object Storage by referring to Enable Cloud Trail log storage.

Caution

To analyze logs in Data Catalog and Data Query, you must select gz as the file extension. Errors may occur if gzip or zip files are stored together in the Object Storage bucket.

Step 2. Configure Data Catalog​

In Data Catalog, create a catalog and a database connected to the bucket where logs are stored.

  1. Go to KakaoCloud console > Analytics > Data Catalog.

  2. In the Catalog menu, click Create catalog.

  3. In the Create catalog pop-up, enter the following information, then click Create.

    CategoryValue
    TypeStandard catalog
    NameCatalog name, for example catalog
    VPC settingsSelect the network for the catalog. Only one catalog can be created per VPC-subnet.

  4. Click the catalog you created, such as catalog, then click Create database in the database list.

  5. In the Create database pop-up, enter the following information, then click Create.

    CategoryValue
    TypeStandard database
    CatalogSelect the catalog created above
    NameDatabase name, for example cloudtrail
    Path- S3 connection: selected
    - Bucket name: enter the Object Storage bucket name
    - Directory: trail/project_event for project events or trail/domain_event for domain events
    Properties (optional)-
    Description (optional)-
    Note

    Cloud Trail events are stored under the trail folder in the specified bucket using the following structure.

    trail/
    ├── project_event/
    │   └── date_id=yyyy-mm-dd/
    │       └── hour_id=hh/
    └── domain_event/
        └── date_id=yyyy-mm-dd/
            └── hour_id=hh/

    To query both project events and domain events, you must create a separate database for each directory. Repeat step 5 above to create one database for trail/project_event and another for trail/domain_event.

    For details, refer to Manage Cloud Trail log storage.

Step 3. Query data with Data Query​

Create a table through the database created in Data Catalog and query log data.

  1. Go to KakaoCloud console > Analytics > Data Query > Query Editor.

  2. Select the data source and database as follows.

    CategoryValue
    Data sourceSelect the Data Catalog catalog catalog
    DatabaseSelect the Data Catalog database cloudtrail

  3. Create a table in the query editor on the right.

    Caution

    The table creation queries below are based on logs stored in the date_id=yyyy-mm-dd/hour_id=hh folder structure. Tables may not be created correctly for existing log files stored in the previous structure, yyyy-mm-dd/trail_yyyy-mm-dd-hh.gz.

    [Project event] Create table
    CREATE TABLE ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME} (
        event_version varchar,
        event_id varchar,
        event_name varchar,
        event_source varchar,
        event_time varchar,
        region varchar,
        domain_id varchar,
        domain_name varchar,
        project_id varchar,
        project_name varchar,
        resource_name varchar,
        resource_id varchar,
        resource_type varchar,
        user_id varchar,
        user_name varchar,
        user_agent varchar,
        source_ip_address varchar,
        other_additional_info varchar,
        date_id varchar,
        hour_id varchar
    )
    WITH (
        external_location = 's3a://${BUCKET_NAME}/trail/project_event',
        format = 'JSON',
        partitioned_by = ARRAY['date_id', 'hour_id']
    );
    환경변수설명
    CATALOG_NAME🖌︎ Catalog name created in Data Catalog
    DATABASE_NAME🖌︎ Database name created in Data Catalog
    TABLE_NAME🖌︎ Table name
    BUCKET_NAME🖌︎ Bucket name
    [Domain event] Create table
    CREATE TABLE ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME} (
        event_version varchar,
        event_id varchar,
        event_name varchar,
        event_source varchar,
        event_time varchar,
        region varchar,
        domain_id varchar,
        domain_name varchar,
        project_id varchar,
        project_name varchar,
        resource_name varchar,
        resource_id varchar,
        resource_type varchar,
        user_id varchar,
        user_name varchar,
        user_agent varchar,
        source_ip_address varchar,
        other_additional_info varchar,
        date_id varchar,
        hour_id varchar
    )
    WITH (
        external_location = 's3a://${BUCKET_NAME}/trail/domain_event',
        format = 'JSON',
        partitioned_by = ARRAY['date_id', 'hour_id']
    );
    환경변수설명
    CATALOG_NAME🖌︎ Catalog name created in Data Catalog
    DATABASE_NAME🖌︎ Database name created in Data Catalog
    TABLE_NAME🖌︎ Table name
    BUCKET_NAME🖌︎ Bucket name

  4. Query the data.

    Query data
    SELECT *
    FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
    WHERE date_id = '2026-04-26' AND hour_id = '01'; -- Query using partition columns.
    환경변수설명
    CATALOG_NAME🖌︎ Catalog name created in Data Catalog
    DATABASE_NAME🖌︎ Database name created in Data Catalog
    TABLE_NAME🖌︎ Table name

    Query result example Query result example

Appendix​

Appendix 1. Project event query examples​

1. Query data for a specific date
SELECT *
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE date_id = '2026-04-26'; -- Query using a partition column.
환경변수설명
CATALOG_NAME🖌︎ Catalog name created in Data Catalog
DATABASE_NAME🖌︎ Database name created in Data Catalog
TABLE_NAME🖌︎ Table name
2. Query only events for a specific resource
SELECT *
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE date_id = '2026-04-26' -- Query using a partition column.
  AND event_source = 'Virtual Machine';
환경변수설명
CATALOG_NAME🖌︎ Catalog name created in Data Catalog
DATABASE_NAME🖌︎ Database name created in Data Catalog
TABLE_NAME🖌︎ Table name
3. Query only a specific event, for example VM Evacuation events
SELECT *
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE date_id = '2026-04-26' -- Query using a partition column.
  AND event_source = 'Virtual Machine'
  AND event_name LIKE '%Evacuate%'; -- Query Virtual Machine service events that include Evacuate.
환경변수설명
CATALOG_NAME🖌︎ Catalog name created in Data Catalog
DATABASE_NAME🖌︎ Database name created in Data Catalog
TABLE_NAME🖌︎ Table name
4. Query events for a specific user
SELECT *
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE date_id = '2026-04-26' -- Query using a partition column.
  AND user_name = 'test@kakaoenterprise.com';
환경변수설명
CATALOG_NAME🖌︎ Catalog name created in Data Catalog
DATABASE_NAME🖌︎ Database name created in Data Catalog
TABLE_NAME🖌︎ Table name

Appendix 2. Domain event query examples​

1. Query data for a specific date
SELECT *
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE date_id = '2026-04-26'; -- Query using a partition column.
환경변수설명
CATALOG_NAME🖌︎ Catalog name created in Data Catalog
DATABASE_NAME🖌︎ Database name created in Data Catalog
TABLE_NAME🖌︎ Table name
2. Filter by a specific event name, for example Console Login
SELECT event_time, user_name, event_name, source_ip_address
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE event_name = 'Console Login' -- Query Console Login events.
  AND date_id = '2026-04-26'; -- Query using a partition column.
환경변수설명
CATALOG_NAME🖌︎ Catalog name created in Data Catalog
DATABASE_NAME🖌︎ Database name created in Data Catalog
TABLE_NAME🖌︎ Table name
3. Filter logs for a specific user
SELECT event_time, event_name, source_ip_address
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE user_name = 'test@kakaoenterprise.com'
  AND date_id = '2026-04-26'
ORDER BY event_time DESC;
환경변수설명
CATALOG_NAME🖌︎ Catalog name created in Data Catalog
DATABASE_NAME🖌︎ Database name created in Data Catalog
TABLE_NAME🖌︎ Table name
4. Login count by IP address
SELECT source_ip_address, COUNT(*) AS access_count
FROM ${CATALOG_NAME}.${DATABASE_NAME}.${TABLE_NAME}
WHERE date_id = '2026-04-26'
  AND event_name = 'Console Login'
GROUP BY source_ip_address
ORDER BY access_count DESC;
환경변수설명
CATALOG_NAME🖌︎ Catalog name created in Data Catalog
DATABASE_NAME🖌︎ Database name created in Data Catalog
TABLE_NAME🖌︎ Table name

Appendix 3. Event log fields for reference when querying​

Event log information

ItemDescription
event_versionEvent version
event_idEvent ID
event_nameEvent name
event_sourceEvent resource, or service name
event_timeEvent time
regionRegion
domain_idDomain ID
domain_nameDomain name
project_idProject ID
project_nameProject name
resource_nameResource name
resource_idResource ID
resource_typeResource type
user_idUser ID
user_nameUser name
user_agentUser browser and operating system
source_ip_addressUser IP address
other_additional_infoAdditional information