question

cbebek avatar image
cbebek asked Erick Ramirez answered

How do I query the audit table efficiently by event time?

How to best query the default audit table dse_audit.audit_log by an event_time range? The docs say it has PRIMARY KEY ((date, “node”, day_partition), event_time), of course:

select XXXXX from dse_audit.audit_log WHERE event_time > minTimeuuid('YYYYY') AND event_time <= maxTimeuuid('ZZZZZ')"

will (too) slowly traverse all partitions.

I supposed I can refine and specify a date range and day_partition in the query?
I'm also wondering if I can use tokens? Note that I won't know the structure of the nodes/cluster. I just have permission to query the audit table.

Also does the date field correspond to the date in event_time? Or that's a date when the record is written to the table? TYIA

audit logging
10 |1000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered

With the way the table is partitioned, it isn't practical to query it based on a range of event_time.

You can export the table and manipulate it externally but since it's really an analytics query, your best option is to query it with an ETL app like Spark. Cheers!

Share
10 |1000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.