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 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 enabled. DataStax Enterprise parses the events into the following columns.

In cqlsh, use DESC TABLE dse_audit.audit_log to view table schema. The table has a compound partition key, date, node, day_partition, and a single clustering column event time.
audit_log colums
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.

Audit logging event categories and types

All events have both a category and a type. A type usually maps directly to a CQL command. The following list shows all the types in each category.

Data Definition Language (DDL)

DDL (Data Definition Language) logs the following database schema changes:

Event category CQL or dsetool commands

ADD_KS

CREATE KEYSPACE

DROP_KS

DROP KEYSPACE

UPDATE_KS

ALTER KEYSPACE

ADD_CF

CREATE TABLE

DROP_CF

DROP TABLE

UPDATE_CF

ALTER TABLE

CREATE_INDEX

CREATE INDEX

DROP_INDEX

DROP INDEX

CREATE_TYPE

CREATE TYPE

DROP_TYPE

DROP TYPE

UPDATE_TYPE

ALTER TYPE

CREATE_FUNCTION

CREATE FUNCTION

DROP_FUNCTION

DROP FUNCTION

CREATE_AGGREGATE

CREATE AGGREGATE

DROP_AGGREGATE

DROP AGGREGATE

CREATE_VIEW

CREATE MATERIALIZED VIEW

DROP_VIEW

DROP MATERIALIZED VIEW

ALTER_VIEW

ALTER MATERIALIZED VIEW

SOLR_CREATE_SEARCH_INDEX_STATEMENT

CREATE SEARCH INDEX

SOLR_ALTER_SEARCH_INDEX_STATEMENT

ALTER SEARCH INDEX CONFIG or ALTER SEARCH INDEX SCHEMA

SOLR_DROP_SEARCH_INDEX_STATEMENT

DROP SEARCH INDEX

SOLR_RELOAD_SEARCH_INDEX

RELOAD SEARCH INDEX

SOLR_REBUILD_SEARCH_INDEX

REBUILD SEARCH INDEX

SOLR_GET_RESOURCE

dsetool create_core

SOLR_UPDATE_RESOURCE

dsetool reload_core

Data Manipulation Language (DML)

DML (Data Manipulation Language) logs the following database data changes:

Event category CQL command

SET_KS

USE

INSERT

INSERT

BATCH

BATCH

TRUNCATE

TRUNCATE

CQL_UPDATE

UPDATE

CQL_DELETE

DELETE

CQL_PREPARE_STATEMENT

DataStax driver prepared statement, such as Java - Prepared Statement

SOLR_UPDATE

SOLR_COMMIT_SEARCH_INDEX_STATEMENT

COMMIT SEARCH INDEX

MANAGEMENT_API_OP

Data Control Language (DCL)

DCL (Data Control Language) logs the following database control, or role or permission changes:

Event category CQL command

CREATE_ROLE

CREATE ROLE

ALTER_ROLE

ALTER ROLE

DROP_ROLE

DROP ROLE

LIST_ROLES

LIST ROLES

LIST_USERS

LIST USERS

LIST_PERMISSIONS

LIST PERMISSIONS

GRANT

GRANT

REVOKE

REVOKE

RESTRICT

RESTRICT

UNRESTRICT

UNRESTRICT

RESTRICT_ROWS_STATEMENT

RESTRICT ROWS

UNRESTRICT_ROWS_STATEMENT

UNRESTRICT ROWS

QUERY

QUERY logs the following data retrieval events:

Event type CQL or other command

CQL_SELECT

SELECT

SOLR_QUERY

SELECT statement filtered by the Search index.

GRAPH_TINKERPOP_TRAVERSAL

Calls to a table using the graph traversal instance (g). See Discovering properties about graphs and traversals.

RPC_CALL_STATEMENT

Remote Procedure Call (RPC) statement.

ADMIN

ADMIN logs the following backup and restore events:

Event type CQL command or other information

CREATE_BACKUP_CONFIG

CREATE BACKUP CONFIGURATION

DROP_BACKUP_CONFIG

DROP BACKUP CONFIGURATION

ALTER_BACKUP_CONFIG

ALTER BACKUP CONFIGURATION

LIST_BACKUP_CONFIGS

LIST BACKUP CONFIGURATIONS

ENABLE_BACKUP_CONFIG

Enabling the backup and restore service

DISABLE_BACKUP_CONFIG

Enabling the backup and restore service

CREATE_BACKUP_STORE

CREATE BACKUP STORE

DROP_BACKUP_STORE

DROP BACKUP STORE

ALTER_BACKUP_STORE

ALTER BACKUP STORE

LIST_BACKUP_STORES

LIST BACKUP STORES

VALIDATE_BACKUP_STORE

VALIDATE BACKUP STORE

RUN_BACKUP

RUN BACKUP

CANCEL_BACKUP

CANCEL BACKUP

RUN_RESTORE

RESTORE

CANCEL_RESTORE

CANCEL RESTORE

FORCE_RESTORE

FORCE RESTORE

LIST_BACKUPS

LIST BACKUPS

CLEAN_BACKUPS

CLEAN BACKUPS

AUTH

AUTH logs the following authentication and authorization events:

Event type CQLSH command

LOGIN_SUCCESS

Successful login attempt. LOGIN and login requests sent from DataStax drivers.

LOGIN_ERROR

Failed login attempt.

UNAUTHORIZED_ATTEMPT

Unauthorized access attempt.

Messages not captured by DataStax Enterprise (DSE) include:

  • Successful Kerberos authentication events. You can audit Kerberos authentication from the Kerberos Distribution Server (KDS). DSE logs a failed Kerberos authentication request as type LOGIN_ERROR.

  • AUTH messages, when whitelisting keyspaces; included_keyspaces.

ERROR

ERROR logs the following error events:

Event type Information

ERROR

Logs CQL statement failures.

REQUEST_FAILURE

Logs failed requests.

UNKNOWN

UNKNOWN logs the following unknown events:

Event type Information

UNKNOWN

Logs unknown events.

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

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com