How to monitor all the query executed by each individual vertica user ?

UJJWAL_RANA

Hi,

I have almost nine user in vertica database. I wanted to see the activities that each user performed and the query that each individual user type. I wonder what is the query for watching the activities for each user according to day or monthly wise ?

 

Thanks

 

Ujjwal

Adrian_Oprea_1

Look into the query_requests table:

select request from query_requests where user_name='username';

UJJWAL_RANA

Hi Adrian,

Thanks for the response.

 

Ujjwal

UJJWAL_RANA

Hi Adrian,

I forget to mentioned one more thing. We have two different team say like one is at USA and another one is at Nepal. Both the team from USA and Nepal get login with same user and work on it. I wonder how could we differentiate to find out the query operation performed by US team and by Nepal Team accordingly.

 

Please advise

 

Ujjwal

Adrian_Oprea_1

 Start using query labels. it is a best practice to do so , on your queryes and your loads. 

If you use BI tools like Microstrategy or Pentaho etc,, even Informatica or any etl tool you will be able to add this "hint"/"query label " ot each oof your reports requests. 

 This way you can track same user used by different analists in diferent zones.

You need ot invest time in convincing your team to start use those labels.BI analists are sometimes "really smart and dont need help or advice" hehehehhehe #not_true

 

 

Start small(only main requests) and then monitor all queryes without labels , make a sql to report you daily requests without labels.

 

UJJWAL_RANA

I forget to mentioned this point. Every user uses VPN inorder to get login.

Adrian_Oprea_1

  It dosen`t reallt matter, is the user accont in the database that will run the query and not the VPN account to the network your vertica nodes are on.

 I guess you login to the VPN and then you have a db account to login into the database ! is this correct ? 

UJJWAL_RANA

yes thats right