NCM: Determine storage space footprint for device configuration files stored in NCM controldb

Products

• VMware Smart Assurance

Environment

VMware Smart Assurance - NCM

Resolution

It is often necessary to make a decision on how much historical data to to retain in the NCM PostgreSQL Control Database (controldb) for a device that is presently or was previously managed by NCM for various reasons including controldb maintenance requirements, reporting performance. When questions such as these arise, it is useful to have a good estimate of how much storage space is occupied by configuration files NCM has collected from the device that are stored now stored in the controldb. To obtain such an estimate, do as follows:

1.  Identify the Device ID number or the device in question. If the device is visible in the NCM Client, this can be obtained by displaying the 'Device ID' column in any list view that shows device details:

    

    or from the 'General' tab of the 'Device Properties' window, accessible by right clicking any reference to the device, then choosing the 'Properties' menu option:
       

2.  Log into a command line shell under a user with 'root' privileges (Linux) or launch an elevated command prompt (Windows) on the host where the controldb is installed.
3.  Launch a controldb shell by running the following command (where {NCM home path} is the path where NCM was installed; current controldb password will be required):

    Linux:

    su - pgdba -s /bin/bash -c 'psql voyencedb voyence'

    Windows:

    {NCM home path}\db\controldb\bin\psql -p 5435 -h 127.0.0.1 --username=voyence -d voyencedb

4.  Run the following SQL command to make the embedded query available for multiple uses in the current controldb session:

    PREPARE device_config_history_footprint( int ) AS
    SELECT
      d.device_idx,
      d.device_name,
      d.management_ip_address,
      d.serial_number,
      d.vendor_name || ' ' || d.vendor_model AS vendor_model, 
      pg_size_pretty(sum(f.file_size)) AS "config_files_footprint"
    FROM cm_config_file f
    JOIN cm_config_unit_revision r
      ON f.revision_id = r.revision_id
    JOIN cm_device d
      ON r.device_id = d.device_id
    WHERE d.device_idx = $1
    GROUP BY
      d.device_idx,
      d.device_name,
      d.management_ip_address,
      d.serial_number,
      d.vendor_name,
      d.vendor_model;

5.  Execute the command by running it as follows (where {Device ID} is the Device ID value obtained in Step 1 above):
 

    EXECUTE device_config_history_footprint( {Device ID} );

    For example:
 

    voyencedb=# EXECUTE device_config_history_footprint( 1041 );
     device_idx | device_name  | management_ip_address | serial_number | vendor_model | config_files_footprint
    ------------+--------------+-----------------------+---------------+--------------+----------------------------
           1041 | r2621-3-test | 10.99.XXX.XXX         | JAD04290G7H   | CISCO 2621   | 1048 MB
    (1 row)