How to get a timestamp of when a user last accessed any Greenplum data

posted Apr 28, 2017, 12:21 PM by Sachchida Ojha
You can  get this data from gp_toolkit._gp_log_master_ext table. This is  an external table table and is owned by gp_toolkit schema. This external table reads data $MASTER_DATA_DIRECTORY/pg_log/*.csv'.  If you want the segment log  then there there is another table in the toolkit schema called _gp_log_segment_ext.


2. Make en external table like 
CREATE READABLE EXTERNAL WEB TABLE public.gp_log_master_ext
(
event_time timestamp without time zone,
user_name CHARACTER VARYING(100),
database_name CHARACTER VARYING(100),
process_id CHARACTER VARYING(10),
thread_id CHARACTER VARYING(50),
remote_host CHARACTER VARYING(100),
remote_port CHARACTER VARYING(10),
session_start_time timestamp without time zone,
transaction_id INTEGER,
gp_session_id TEXT,
gp_command_count TEXT,
gp_segment TEXT,
slice_id TEXT,
distr_tranx_id TEXT,
local_tranx_id TEXT,
sub_tranx_id TEXT,
event_severity CHARACTER VARYING(10),
sql_state_code CHARACTER VARYING(10),
event_message TEXT,
event_detail TEXT,
event_hint TEXT,
internal_query TEXT,
internal_query_pos INTEGER,
event_context TEXT,
debug_query_string TEXT,
error_cursor_pos INTEGER,
func_name TEXT,
file_name TEXT,
file_line INTEGER,
stack_trace TEXT
)
EXECUTE E'cat $MASTER_DATA_DIRECTORY/pg_log/*.csv' ON MASTER
FORMAT 'CSV' (delimiter ',' null '' escape '"' quote '"')
ENCODING 'WIN1251';
Comments