View Events from DSE Audit 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.
Set the replication factor for the |
`audit_log`columns
The audit log
table contains all events from nodes that have auditing enabled.
DataStax Enterprise parses the events into the following columns.
In |
Column | Type | Description |
---|---|---|
|
timestamp |
Date of the event. (Partition column 1) |
|
inet |
DSE node IP address. (Partition column 2) |
|
int |
Current hour of the day * 3600, using GMT. (Partition column 3) |
|
timeuuid |
A TIMEUUID generated when the event began. (Clustering column) |
|
text |
User name or id used to login. |
|
uuid |
The UUID of the batch query the event was grouped with when written to Cassandra. |
|
text |
|
|
text |
Request consistency level. |
|
text |
Keyspace name where the event occurred. |
|
text |
Event description and CQL request if applicable. |
|
text |
Client IP address. |
|
text |
Table name where the event occurred if applicable. |
|
text |
The type of the event. See types in each category. |
|
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