Skip to content

Query PolicySync Audits in Amazon Athena

When connector audits are exported to Amazon S3 via Audit Fluentd (see Send Audits to S3 and Connector Audit Output Destinations), you can query the policysync_audit data directly in Amazon Athena without loading it into a separate store.

Note

  • This is applicable only for self-managed deployments. For PrivaceraCloud, please reach out to your contact at Privacera.
  • This page applies to connector audits from the policysync_audit collection that are exported to Amazon S3.

Prerequisites

Prerequisite Description
S3 export configured Connector audits must be flowing to S3. See Send Audits to S3.
Athena access An AWS account with permission to run Athena queries and read the target S3 bucket.
S3 path The bucket and prefix where auditType=policysync_audit data is written.

Create the database

Run the following in the Athena query editor to create a database for your audit tables.

SQL
1
CREATE DATABASE IF NOT EXISTS <your-database>;

Create the external table

The table uses partition projection so Athena discovers partitions automatically without running MSCK REPAIR TABLE. Replace the LOCATION and storage.location.template values with your actual S3 bucket and prefix.

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
DROP TABLE IF EXISTS <your-database>.policysync_audit;

CREATE EXTERNAL TABLE IF NOT EXISTS <your-database>.policysync_audit (
    msg            STRING,
    eventType      STRING,
    evtTime        STRING,
    timeTakenMs    BIGINT,
    status         STRING,

    connectorType  STRING,
    connector      STRING,

    uuid           STRING,
    changeLogId    BIGINT,
    changeLogType  STRING,

    policyId       STRING,
    policyVersion  STRING,
    policyName     STRING,
    policyType     STRING,
    policyLabel    STRING,

    resourceType   STRING,
    resource       STRING,
    permission     STRING,

    action         STRING,
    cmd            STRING,
    cmdType        STRING,

    actor          STRING,
    actorType      STRING,

    loaderType     STRING,
    triggerFrom    STRING,
    ondemandTaskId BIGINT,

    extraInfo      STRING
)
PARTITIONED BY (
    year  STRING,
    month STRING,
    day   STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<bucket>/<prefix>/auditType=policysync_audit/'
TBLPROPERTIES (
    'projection.enabled'        = 'true',

    'projection.year.type'      = 'integer',
    'projection.year.range'     = '2020,2035',

    'projection.month.type'     = 'integer',
    'projection.month.range'    = '1,12',
    'projection.month.digits'   = '2',

    'projection.day.type'       = 'integer',
    'projection.day.range'      = '1,31',
    'projection.day.digits'     = '2',

    'storage.location.template' =
        's3://<bucket>/<prefix>/auditType=policysync_audit/year=${year}/month=${month}/day=${day}/'
);

Verify the table

Run the following statement to confirm Athena can read the table definition and list projected partitions. With partition projection enabled, Athena generates partition metadata dynamically from the ranges you defined, so you do not need to run MSCK REPAIR TABLE.

SQL
1
SHOW PARTITIONS <your-database>.policysync_audit;

Sample query

Filter by policy ID, event type, and date to retrieve recent connector audit records. Replace <year>, <month>, and <day> with the date values you want to query.

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT *
FROM <your-database>.policysync_audit
WHERE
    policyId  = '<policy-id>'
    AND eventType = 'ACCESS'
    AND year  = '<year>'
    AND month = '<month>'
    AND day   = '<day>'
ORDER BY evtTime DESC
LIMIT 10;