View events from DSE audit table

Use CQL queries to view events captured in the dse_audit.audit_log table.

The dse_audit.audit_log table stores database activity events for all nodes that have auditing enabled with the CassandraAuditWriter logger. Use CQL to query the table and view results.

Important: Set the replication factor for the dse_audit keyspace to at least one in all datacenters where auditing is enabled.

audit_log columns

The audit log table contains all events from nodes that have auditing is enabled. DataStax Enterprise parses the events into the following columns.

Tip: In cqlsh, use DESC TABLE dse_audit.audit_log to view table schema. The table has a compound partition key, date, node, and day_partition and a single clustering column event time.
Table 1. audit_log columns
Column Type Description
date timestamp Date of the event. (Partition column 1)
node inet DSE node IP address. (Partition column 2)
day_partition int Current hour of the day * 3600, using GMT. (Partition column 3)
event_time timeuuid A TIMEUUID generated when the event began. (Clustering column)
authenticated text User name or id used to login.
batch_id uuid The UUID of the batch query the event was grouped with when written to Cassandra.
category text The event category name:
consistency text Request consistency level.
keyspace_name text Keyspace name where the event occurred.
operation text Event description and CQL request if applicable.
source text Client IP address.
table_name text Table name where the event occurred if applicable.
type text The type of the event. See types in each category.
username text DSE role name of the logged in user.

Example audit queries

Get all events on a particular node that occurred at :
SELECT * FROM dse_audit.audit_log;
with sample output from a DSE Graph query:
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 57ff2dc0-3827-11e6-9ea9-2f93eec587f0 | null | QUERY | null | null | 
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 57ffa2f0-3827-11e6-9ea9-2f93eec587f0 | null |   DML |   null |        test |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 57ffca00-3827-11e6-9ea9-2f93eec587f0 | null | QUERY |    ONE |        test |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 58001820-3827-11e6-9ea9-2f93eec587f0 | null |   DML |   null |        test |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 58001821-3827-11e6-9ea9-2f93eec587f0 | null | QUERY |    ONE |        test |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 58008d50-3827-11e6-9ea9-2f93eec587f0 | null |   DML |   null |        test |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 5800db70-3827-11e6-9ea9-2f93eec587f0 | null |   DML |   null |        test |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 5800db71-3827-11e6-9ea9-2f93eec587f0 | null | QUERY |    ONE |        test |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 58012990-3827-11e6-9ea9-2f93eec587f0 | null |   DML |   null |        test |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 58019ec0-3827-11e6-9ea9-2f93eec587f0 | null |   DML |   null |        test |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 58019ec1-3827-11e6-9ea9-2f93eec587f0 | null | QUERY |    ONE |        test |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 5bb86530-3827-11e6-9ea9-2f93eec587f0 | null | QUERY |    ONE |   dse_audit |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 6dfac5d0-3827-11e6-9ea9-2f93eec587f0 | null | QUERY | QUORUM | test_system |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 6dfbb030-3827-11e6-9ea9-2f93eec587f0 | null |   DML |   null | test_system |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 6dfbb031-3827-11e6-9ea9-2f93eec587f0 | null | QUERY | QUORUM | test_system |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 70c70530-3827-11e6-9ea9-2f93eec587f0 | null |   DML |   null |  dse_system |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 70c70531-3827-11e6-9ea9-2f93eec587f0 | null | QUERY | QUORUM |  dse_system |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 78fb6480-3827-11e6-9ea9-2f93eec587f0 | null | QUERY |    ONE |   dse_audit |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 7aadcf70-3827-11e6-9ea9-2f93eec587f0 | null |   DML |   null | test_system |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 7aadf680-3827-11e6-9ea9-2f93eec587f0 | null | QUERY | QUORUM | test_system |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 7aaee0e0-3827-11e6-9ea9-2f93eec587f0 | null |   DML |   null | test_system |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 7aaee0e1-3827-11e6-9ea9-2f93eec587f0 | null | QUERY | QUORUM | test_system |
 2016-06-22 00:00:00+0000 | 127.0.0.1 | 10800 | 8195b190-3827-11e6-9ea9-2f93eec587f0 | null | QUERY |    ONE |   dse_audit |
RequestMessage{, requestId=941d2e1a-7cc9-4c80-8c28-dccb799840b7, op='eval', processor='session', args={gremlin=g.V().count(), 
  aliases={g=testQSagain.g}, session=d179c734-813f-4a3e-89d6-bd756b4fcf57, bindings={}, manageTransaction=true, batchSize=64}}         | /127.0.0.1:60647 |         null | GRAPH_TINKERPOP_TRAVERSAL |   unknown
 SELECT "community_id", "member_id" FROM "test"."meal_p" WHERE "~~vertex_exists" = ? LIMIT ? ALLOW FILTERING;                          |         /0.0.0.0 |       meal_p |     CQL_PREPARE_STATEMENT |    system
 Query string not found in prepared statement cache [bind variable values unavailable]                                                 |         /0.0.0.0 |       meal_p |                CQL_SELECT |    system
 SELECT "community_id", "member_id" FROM "test"."ingredient_p" WHERE "~~vertex_exists" = ? LIMIT ? ALLOW FILTERING;                    |         /0.0.0.0 | ingredient_p |     CQL_PREPARE_STATEMENT |    system
 Query string not found in prepared statement cache [bind variable values unavailable]                                                 |         /0.0.0.0 | ingredient_p |                CQL_SELECT |    system
 SELECT "community_id", "member_id" FROM "test"."author_p" WHERE "~~vertex_exists" = ? LIMIT ? ALLOW FILTERING;                        |         /0.0.0.0 |     author_p |     CQL_PREPARE_STATEMENT |    system
 Query string not found in prepared statement cache [bind variable values unavailable]                                                 |         /0.0.0.0 |     author_p |                CQL_SELECT |    system
 SELECT "community_id", "member_id" FROM "test"."book_p" WHERE "~~vertex_exists" = ? LIMIT ? ALLOW FILTERING;                          |         /0.0.0.0 |       book_p |     CQL_PREPARE_STATEMENT |    system
 Query string not found in prepared statement cache [bind variable values unavailable]                                                 |         /0.0.0.0 |       book_p |                CQL_SELECT |    system
 SELECT "community_id", "member_id" FROM "test"."recipe_p" WHERE "~~vertex_exists" = ? LIMIT ? ALLOW FILTERING;                        |         /0.0.0.0 |     recipe_p |     CQL_PREPARE_STATEMENT |    system
 Query string not found in prepared statement cache [bind variable values unavailable]                                                 |         /0.0.0.0 |     recipe_p |                CQL_SELECT |    system
 SELECT "community_id", "member_id" FROM "test"."reviewer_p" WHERE "~~vertex_exists" = ? LIMIT ? ALLOW FILTERING;                      |         /0.0.0.0 |   reviewer_p |     CQL_PREPARE_STATEMENT |    system
 Query string not found in prepared statement cache [bind variable values unavailable]                                                 |         /0.0.0.0 |   reviewer_p |                CQL_SELECT |    system
 select * from audit_log ;                                                                                                             |       /127.0.0.1 |    audit_log |                CQL_SELECT | anonymous
 SELECT last_updated FROM "test_system".shared_data WHERE dataspace = ?;                                                               |         /0.0.0.0 |  shared_data |     CQL_PREPARE_STATEMENT |    system
 Query string not found in prepared statement cache [bind variable values unavailable]                                                 |         /0.0.0.0 |  shared_data |                CQL_SELECT |    system
 SELECT last_updated FROM "test_system".shared_data WHERE dataspace = ?;                                                               |         /0.0.0.0 |  shared_data |     CQL_PREPARE_STATEMENT |    system
 Query string not found in prepared statement cache [bind variable values unavailable]                                                 |         /0.0.0.0 |  shared_data |                CQL_SELECT |    system
 SELECT last_updated FROM "dse_system".shared_data WHERE dataspace = ?;                                                                |         /0.0.0.0 |  shared_data |     CQL_PREPARE_STATEMENT |    system
 Query string not found in prepared statement cache [bind variable values unavailable]                                                 |         /0.0.0.0 |  shared_data |                CQL_SELECT |    system
 select * from audit_log ;                                                                                                             |       /127.0.0.1 |    audit_log |                CQL_SELECT | anonymous
 SELECT last_updated FROM "test_system".shared_data WHERE dataspace = ?;                                                               |         /0.0.0.0 |  shared_data |     CQL_PREPARE_STATEMENT |    system
 Query string not found in prepared statement cache [bind variable values unavailable]                                                 |         /0.0.0.0 |  shared_data |                CQL_SELECT |    system
 Query string not found in prepared statement cache [bind variable values unavailable]                                                 |         /0.0.0.0 |  shared_data |                CQL_SELECT |    system
 select * from audit_log ;                                                                                                             |       /127.0.0.1 |    audit_log |                CQL_SELECT | anonymous