twingate athena

create_table.sql

CREATE EXTERNAL TABLE twingate_logs (
    `event_type` string,
    `event` struct<
              `time`:string,
              `action`:string,
              `actor`:struct<`type`:string,`id`:string>,
              `targets`:array<
                struct<
                  `version`:string,
                  `type`:string,
                  `name`:string,
                  `id`:string,
                  `role`:string,
                  `email`:string,
                  `isAdmin`:boolean,
                  `isActive`:boolean,
                  `secureDNSGroupException`:boolean
                >
              >
            >
)
ROW FORMAT  serde 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://twingate-admo-reports/';

example.sql

select
    event_type,
    event.time,
    event.action,
    event.targets[1].type who_type,
    event.targets[1].name who_name,
    event.targets[1].role who_role,
    event.targets[1].email who_email,
    event.targets[1].isAdmin isAdmin,
    event.targets[1].isActive isActive,
    case
        when cardinality(event.targets) > 1
        then event.targets[2].type
        else ''
    end as "on_type",
    case
        when cardinality(event.targets) > 1
        then event.targets[2].name
        else ''
    end as "on_name"
from twingate_logs
limit 10

still need some work