Monitoring User Events

This topic shows you how to query the PostgresSQL database that is embedded within the Cloudera Machine Learning deployment to monitor or audit user events.

Querying the PostgresSQL database that is embedded within the Cloudera Machine Learning deployment requires root access to the Cloudera Machine Learning Master host.
  1. SSH to the Cloudera Machine Learning Master host and log in as root.
    For example, the following command connects to cdsw-master-host. as root:
    ssh root@cdsw-master-host.yourcdswdomain.com
  2. Get the name of the database pod:
    kubectl get pods -l role=db
    The command returns information similar to the following example:
    NAME                  READY   STATUS    RESTARTS   AGE
    db-86bbb69b54-d5q88   1/1     Running   0          4h46m
  3. Enter the following command to log into the database as the sense user:
     kubectl exec <database pod> -ti -- psql -U sense
    For example, the following command logs in to the database on pod db-86bbb69b54-d5q88:
    kubectl exec db-86bbb69b54-d5q88 -ti -- psql -U sense
    You are logged into the database as the sense user.
  4. Run queries against the user_events table.
    For example, run the following query to view the most recent user event:
    select * from user_events order by created_at DESC LIMIT 1
    The command returns information similar to the following:
    
    id          | 3658
    user_id     | 273
    ipaddr      | ::ffff:127.0.0.1
    user_agent  | node-superagent/2.3.0
    event_name  | model created
    description | {"model":"Simple Model 1559154287-ex5yn","modelId":"50","userType":"NORMAL","username":"DonaldBatz"}
    created_at  | 2019-05-29 18:24:47.65449
  5. Optionally, you can export the user events to a CSV file for further analysis:
    1. Copy the user_events table to a CSV file:
      copy user_events to '/tmp/user_events.csv' DELIMITER ',' CSV HEADER;
    2. Find the container that the database runs on:
      docker ps | grep db-86bbb
      The command returns output similar to the following:
      c56d04bbd58 c230b2f564da "docker-entrypoint..." 7 days ago Up 7 days k8s_db_db-86bbb69b54-fcfm6_default_8b2dd23d-88b9-11e9-bc34-0245eb679f96_0
      The first entry is the container ID.
    3. Copy the user_events.csv file out of the container into a temporary directory on the Master host:
      docker cp <container ID>:/tmp/user_events.csv /tmp/user_events.csv
      For example:
      docker cp 8c56d04bbd58:/tmp/user_events.csv /tmp/user_events.csv
    4. Use SCP to copy /tmp/user_events.csv from the Cloudera Machine Learning Master host to a destination of your choice.
      For example, run the following command on your local machine to copy user_events.csv to a local directory named events:
      scp root@cdsw-master-host.yourcdswdomain.com:/tmp/user_events.csv /local/directory/events/
For information about the different user events, see Tracked User Events.