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.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