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

Post date: May 05, 2014 7:11:42 PM

1 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';