Auditing resource pool changes

Rupendra

Hello all,

In our test environment, we noticed that some of the resource pools setting were modified by some user. But, we could not identify when this happened and which user changed it. Is there any way to find it out?


Thanks,
Rupendra

[Deleted User]

Hi Rupendra,

You can get that detail from the dc_requests_issued table as in the example below. Note that there is also a dc_requests_completed table and you might want to join so you can filter out failed executions (in the example below there was a fail and a success but you can't tell unless you join with the completed table.

=> alter resource pool wosdata memorysize 100;
ERROR 4856:  Syntax error at or near "100" at character 40
LINE 1: alter resource pool wosdata memorysize 100;      
                                               ^         
=> alter resource pool wosdata memorysize '100M';
ALTER RESOURCE POOL

=> select time,user_name,request from dc_requests_issued where request ilike '%alter resource pool%';
             time              | user_name |                                              request
-------------------------------+-----------+----------------------------------------------------------------------------------------------------
 2014-04-02 14:32:58.1697-04   | dbadmin   | alter resource pool wosdata memorysize '100';
 2014-04-02 14:33:03.502241-04 | dbadmin   | alter resource pool wosdata memorysize '100M';

Also note these tables have a finite amount of data they retain based on size, or in version 7 based on size or time. So depending on how long it is before you identify a pool got adjusted the records may or may not still be in these tables. There is a retention policy you can adjust on each dc table but might not be worth it for a one time event.

In the case where it has been purged from the dc tables you'd have to revert to grepping the vertica logs and archives in the catalog dir to see if locatable there.

zgrep "alter resource pool" PRD/*catalog/vertica.log* | grep "Starting Commit"
PRD/v_vrt_edw_prd_node0001_catalog/vertica.log:2014-04-02 14:33:03.504 Init Session:0x7fb3b000eb30-a0000000015966 [Txn] <INFO> Starting Commit: Txn: a0000000015966 'alter resource pool wosdata memorysize '100M';'

.I hope it helps.

[Deleted User]

Hi, 
You can see the dc_resource_pool_history. That keep records of all the changes that you did in the pools. 

Hope this helps, 
Eugenia

Rupendra

Thanks this helps.

[Deleted User]

your  are welcome