Auditing resource pool changes

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?



  • Options
    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';

    => 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.
  • Options
    You can see the dc_resource_pool_history. That keep records of all the changes that you did in the pools. 

    Hope this helps, 
  • Options
    Thanks this helps.
  • Options
    your  are welcome :) 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file