Query Audit Log via Jupyter and Trino

Query Audit Log via Jupyter and Trino#

Also see:

https://grafana.com/grafana/dashboards/22292-vast-audit-dashboard/

import urllib3

urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

%load_ext sql

%sql trino://admin@${DOCKER_HOST_OR_IP}:8443/vast  --connection_arguments '{"http_scheme":"https", "verify": false}'

Connecting to 'trino://admin@10.143.11.241:8443/vast'

%%sql

SELECT
    cluster_name,
    cnode_name,
    time,
    client_ip,
    cluster_vip,
    tenant,
    protocol,
    rpc_type,
    --path, -- this field causes a jupyter error
    view_path,
    --name, -- this field causes a jupyter error
    login_name,
    uid,
    sid,
    s3_access_keys,
    status,
    nfs3_required_permissions,
    nfs3_granted_permissions,
    nfs_link_name,
    nfs_link_path,
    rename_path,
    rename_name,
    nfs_rpc_sub_types,
    nfs_symlink,
    nfs4_required_access,
    nfs4_granted_access,
    nfs4_ftype,
    s3_used_access_key,
    s3_request_id,
    connection_type,
    s3_bucket_name,
    s3_multipart_upload_id,
    s3_version_id,
    s3_version_phandle,
    s3_source_object,
    smb_ads_name,
    smb_create_action,
    smb_create_option,
    smb_create_disposition,
    smb_delete_on_close,
    smb_info_type,
    smb_info_class,
    smb_rename_struct,
    num_ops,
    num_bytes,
    time_str
FROM 
    "vast-audit-log-bucket|vast_audit_log_schema".vast_audit_log_table
WHERE
    view_path like '/csnow%' 
    and protocol <> 'NDB'
ORDER BY
    time DESC
LIMIT 10

Running query in 'trino://admin@10.143.11.241:8443/vast'

cluster_name	cnode_name	time	client_ip	cluster_vip	tenant	protocol	rpc_type	view_path	login_name	uid	sid	s3_access_keys	status	nfs3_required_permissions	nfs3_granted_permissions	nfs_link_name	nfs_link_path	rename_path	rename_name	nfs_rpc_sub_types	nfs_symlink	nfs4_required_access	nfs4_granted_access	nfs4_ftype	s3_used_access_key	s3_request_id	connection_type	s3_bucket_name	s3_multipart_upload_id	s3_version_id	s3_version_phandle	s3_source_object	smb_ads_name	smb_create_action	smb_create_option	smb_create_disposition	smb_delete_on_close	smb_info_type	smb_info_class	smb_rename_struct	num_ops	num_bytes	time_str
selab-var-204	cnode-3-14	2024-12-09 16:46:00.743450	172.200.12.241	172.200.204.2	default	S3	GET_OBJECT	/csnow-bucket	chris.snow@selab.vastdata.com	5102	None	['Y5101AQQTB1PUAEKQXN5', '', '', '']	Success	None	None	None	None	None	None	None	None	None	None	None	Y5101AQQTB1PUAEKQXN5	405380490835875	HTTP	csnow-bucket	None	-1	None	None	None	None	None	None	None	None	None	None	1	11250	2024-12-09T16:46:00.743Z
selab-var-204	cnode-3-14	2024-12-09 16:46:00.742352	172.200.12.241	172.200.204.2	default	S3	GET_OBJECT	/csnow-bucket	chris.snow@selab.vastdata.com	5102	None	['Y5101AQQTB1PUAEKQXN5', '', '', '']	Success	None	None	None	None	None	None	None	None	None	None	None	Y5101AQQTB1PUAEKQXN5	405380490835873	HTTP	csnow-bucket	None	-1	None	None	None	None	None	None	None	None	None	None	1	10957	2024-12-09T16:46:00.742Z
selab-var-204	cnode-3-14	2024-12-09 16:46:00.739602	172.200.12.241	172.200.204.2	default	S3	GET_OBJECT	/csnow-bucket	chris.snow@selab.vastdata.com	5102	None	['Y5101AQQTB1PUAEKQXN5', '', '', '']	Success	None	None	None	None	None	None	None	None	None	None	None	Y5101AQQTB1PUAEKQXN5	405380490835871	HTTP	csnow-bucket	None	-1	None	None	None	None	None	None	None	None	None	None	1	12562	2024-12-09T16:46:00.739Z
selab-var-204	cnode-3-14	2024-12-09 16:46:00.738498	172.200.12.241	172.200.204.2	default	S3	GET_OBJECT	/csnow-bucket	chris.snow@selab.vastdata.com	5102	None	['Y5101AQQTB1PUAEKQXN5', '', '', '']	Success	None	None	None	None	None	None	None	None	None	None	None	Y5101AQQTB1PUAEKQXN5	405380490835869	HTTP	csnow-bucket	None	-1	None	None	None	None	None	None	None	None	None	None	1	12861	2024-12-09T16:46:00.738Z
selab-var-204	cnode-3-14	2024-12-09 16:46:00.738013	172.200.12.241	172.200.204.2	default	S3	GET_OBJECT	/csnow-bucket	chris.snow@selab.vastdata.com	5102	None	['Y5101AQQTB1PUAEKQXN5', '', '', '']	Success	None	None	None	None	None	None	None	None	None	None	None	Y5101AQQTB1PUAEKQXN5	405586649568943	HTTP	csnow-bucket	None	-1	None	None	None	None	None	None	None	None	None	None	1	10405	2024-12-09T16:46:00.738Z
selab-var-204	cnode-3-14	2024-12-09 16:46:00.737418	172.200.12.241	172.200.204.2	default	S3	GET_OBJECT	/csnow-bucket	chris.snow@selab.vastdata.com	5102	None	['Y5101AQQTB1PUAEKQXN5', '', '', '']	Success	None	None	None	None	None	None	None	None	None	None	None	Y5101AQQTB1PUAEKQXN5	405861528663718	HTTP	csnow-bucket	None	-1	None	None	None	None	None	None	None	None	None	None	1	49216	2024-12-09T16:46:00.737Z
selab-var-204	cnode-3-14	2024-12-09 16:46:00.737001	172.200.12.241	172.200.204.2	default	S3	GET_OBJECT	/csnow-bucket	chris.snow@selab.vastdata.com	5102	None	['Y5101AQQTB1PUAEKQXN5', '', '', '']	Success	None	None	None	None	None	None	None	None	None	None	None	Y5101AQQTB1PUAEKQXN5	405243051779696	HTTP	csnow-bucket	None	-1	None	None	None	None	None	None	None	None	None	None	1	49216	2024-12-09T16:46:00.737Z
selab-var-204	cnode-3-14	2024-12-09 16:46:00.736670	172.200.12.241	172.200.204.2	default	S3	GET_OBJECT	/csnow-bucket	chris.snow@selab.vastdata.com	5102	None	['Y5101AQQTB1PUAEKQXN5', '', '', '']	Success	None	None	None	None	None	None	None	None	None	None	None	Y5101AQQTB1PUAEKQXN5	405243051779698	HTTP	csnow-bucket	None	-1	None	None	None	None	None	None	None	None	None	None	1	49216	2024-12-09T16:46:00.736Z
selab-var-204	cnode-3-14	2024-12-09 16:46:00.736654	172.200.12.241	172.200.204.2	default	S3	GET_OBJECT	/csnow-bucket	chris.snow@selab.vastdata.com	5102	None	['Y5101AQQTB1PUAEKQXN5', '', '', '']	Success	None	None	None	None	None	None	None	None	None	None	None	Y5101AQQTB1PUAEKQXN5	405174332226281	HTTP	csnow-bucket	None	-1	None	None	None	None	None	None	None	None	None	None	1	49216	2024-12-09T16:46:00.736Z
selab-var-204	cnode-3-14	2024-12-09 16:46:00.736553	172.200.12.241	172.200.204.2	default	S3	GET_OBJECT	/csnow-bucket	chris.snow@selab.vastdata.com	5102	None	['Y5101AQQTB1PUAEKQXN5', '', '', '']	Success	None	None	None	None	None	None	None	None	None	None	None	Y5101AQQTB1PUAEKQXN5	405724088776494	HTTP	csnow-bucket	None	-1	None	None	None	None	None	None	None	None	None	None	1	49216	2024-12-09T16:46:00.736Z

Truncated to displaylimit of 10.

Using a Dataframe#

Sometime we just need a pandas dataframe

%config SqlMagic.autopandas = True
df = %sql SELECT * FROM "vast-audit-log-bucket|vast_audit_log_schema".vast_audit_log_table LIMIT 100
df

Running query in 'trino://admin@10.143.11.241:8443/vast'

	cluster_name	cnode_name	time	client_ip	cluster_vip	tenant	protocol	rpc_type	path	view_path	...	smb_create_action	smb_create_option	smb_create_disposition	smb_delete_on_close	smb_info_type	smb_info_class	smb_rename_struct	num_ops	num_bytes	time_str
0	selab-var-204	cnode-3-14	2024-12-09 10:36:21.180570	172.200.12.241	172.200.204.2	default	S3	PUT_OBJECT	(path: '/csnow-bucket/iceberg/twitter_data-24c...	/csnow-bucket	...	None	None	None	None	None	None	None	1	0	2024-12-09T10:36:21.180Z
1	selab-var-204	cnode-3-14	2024-12-09 10:36:21.235536	172.200.12.241	172.200.204.2	default	S3	GET_OBJECT	(path: '/csnow-bucket/iceberg/twitter_data-24c...	/csnow-bucket	...	None	None	None	None	None	None	None	1	4125	2024-12-09T10:36:21.235Z
2	selab-var-204	cnode-3-14	2024-12-09 10:36:21.328924	172.200.12.241	172.200.204.2	default	S3	PUT_OBJECT	(path: '/csnow-bucket/iceberg/twitter_data-24c...	/csnow-bucket	...	None	None	None	None	None	None	None	1	0	2024-12-09T10:36:21.328Z
3	selab-var-204	cnode-3-14	2024-12-09 10:36:21.975770	172.200.12.241	172.200.204.2	default	S3	GET_OBJECT	(path: '/csnow-bucket/iceberg/twitter_data-24c...	/csnow-bucket	...	None	None	None	None	None	None	None	1	2044	2024-12-09T10:36:21.975Z
4	selab-var-204	cnode-3-14	2024-12-09 10:36:23.221401	172.200.12.241	172.200.204.2	default	S3	GET_OBJECT	(path: '/csnow-bucket/iceberg/twitter_data-24c...	/csnow-bucket	...	None	None	None	None	None	None	None	1	2044	2024-12-09T10:36:23.221Z
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
95	selab-var-204	cnode-3-14	2024-12-09 11:28:17.698808	172.200.12.241	172.200.204.2	default	NDB	QUERY_DATA	(path: '/vast-big-catalog-bucket/vast_big_cata...	/vast-big-catalog-bucket	...	None	None	None	None	None	None	None	1	0	2024-12-09T11:28:17.698Z
96	selab-var-204	cnode-3-14	2024-12-09 11:28:17.722494	172.200.12.241	172.200.204.2	default	NDB	QUERY_DATA	(path: '/vast-big-catalog-bucket/vast_big_cata...	/vast-big-catalog-bucket	...	None	None	None	None	None	None	None	1	0	2024-12-09T11:28:17.722Z
97	selab-var-204	cnode-3-14	2024-12-09 11:28:17.740095	172.200.12.241	172.200.204.2	default	NDB	QUERY_DATA	(path: '/vast-big-catalog-bucket/vast_big_cata...	/vast-big-catalog-bucket	...	None	None	None	None	None	None	None	1	0	2024-12-09T11:28:17.740Z
98	selab-var-204	cnode-3-14	2024-12-09 11:28:17.789647	172.200.12.241	172.200.204.2	default	NDB	QUERY_DATA	(path: '/vast-big-catalog-bucket/vast_big_cata...	/vast-big-catalog-bucket	...	None	None	None	None	None	None	None	1	0	2024-12-09T11:28:17.789Z
99	selab-var-204	cnode-3-14	2024-12-09 11:28:17.828693	172.200.12.241	172.200.204.2	default	NDB	QUERY_DATA	(path: '/vast-big-catalog-bucket/vast_big_cata...	/vast-big-catalog-bucket	...	None	None	None	None	None	None	None	1	0	2024-12-09T11:28:17.828Z

100 rows × 46 columns

Query Audit Log via Jupyter and Trino

Contents

Query Audit Log via Jupyter and Trino#

Using a Dataframe#