Smarts NCM: Config Diff data missing from reports is not resolved by redistributing lockbox
Article ID: 330986
Updated On:
Products
Issue/Introduction
In most cases, config diff data missing from reports is the result of an inability to properly decrypt the configuration data. This can happen when the lockbox on the Report Advisor host is either not accessible, or does not contain the necessary e-keys to decrypt the configuration file content associated with configuration changes to devices. This sort of issue is typically resolved by redistributing the master lockbox from the Application Server to the Report Advisor host. One case where this will not successfully resolve the trouble is when the NCM Data Transformer is failing to process configuration change data into tables in the PostgreSQL Control Database that are accessible to the NCM Report Advisor. This will typically cause a backlog of records to accumulate awaiting processing by the NCM Data Transfomer service.
A stuck data transformer can be detected by performing the following steps to obtain a count of records awaiting processing by the NCM Data Transformer Service over a span of days to see if the backlog of records is growing or shrinking. If it is growing, then the Data Transformer may be stuck.
- Log into a command line shell on the NCM PostgreSQL Control Database (controldb) host as root (Linux) or under an account with full local administrator privileges (Windows).
- Run the following command to launch a controldb shell (Note: the current controldb password will be required):
su - pgdba -c 'psql voyencedb voyence'
%VOYENCE_HOME%\db\controldb\bin\psql -p 5435 -h 127.0.0.1 --username=voyence -d voyencedb
- Run the following three queries to get a count of all records that are in the data transformer backlog awaiting processing:
/* Determine data transformation backlog (audit history)
*/
SELECT count(*)
FROM cm_device_audit_history
JOIN (
SELECT
config_state_id,
max(compliance_audit_time) AS enforcement_time
FROM cm_device_audit_history
GROUP BY config_state_id
) A
ON (
cm_device_audit_history.compliance_audit_time = A.enforcement_time AND
cm_device_audit_history.config_state_id = A.config_state_id
)
LEFT JOIN cm_rpt_enforcement_trail
ON cm_device_audit_history.history_id = cm_rpt_enforcement_trail.history_id
WHERE cm_rpt_enforcement_trail.history_id IS NULL;
/* Determine data transformation backlog (device config diffs & revisions)
*/
SELECT count(*)
FROM
cm_device,
cm_config_unit_revision
LEFT JOIN cm_config_diff
ON (cm_config_diff.revision_id = cm_config_unit_revision.revision_id),
cm_config_file
WHERE
cm_config_unit_revision.revision_id = cm_config_file.revision_id AND
cm_config_diff.revision_id IS NULL AND
cm_config_unit_revision.revision_number > 1 AND
cm_config_unit_revision.device_id = cm_device.device_id;
/* Determine data transformation backlog (device states)
*/
SELECT count(*)
FROM cm_device_revisionable_state
LEFT JOIN cm_rpt_dcs_diff
ON (cm_device_revisionable_state.device_state_id = cm_rpt_dcs_diff.device_state_id)
WHERE
cm_rpt_dcs_diff.device_state_id IS NULL AND
cm_device_revisionable_state.state_number > 1;
Environment
Cause
On cause that can prevent the NCM Data Transformer from processing configuration change data into appropriate tables accessible to the Report Advisor is if the Data Transformer is unable to log into the NCM PostgreSQL Control Database. This will typically show up as an exception in the transformer.log file right after NCM services startup with a Java exception similar to the following example:
INFO : 2016-08-01 08:08:01,607: Main: Started Main...
DEBUG: 2016-08-01 08:08:01,898: DataTransformerRegistry: Registering the datatransformer - DCS Diff Data Transformer
INFO : 2016-08-01 08:08:01,901: VoyenceDataSource: Reading datasource.properties from classpath
DEBUG: 2016-08-01 08:08:01,903: VoyenceDataSource: Datasource file is: /app/smarts-ncm/Transformation/conf/datasource.properties
DEBUG: 2016-08-01 08:08:07,404: VoyenceDataSource: Exception occured while reading datasource properties: java.lang.NumberFormatException: For input string: "Unsupp"
In such cases, the encrypted password contained in the $VOYENCE_HOME/Transformation/conf/datasource.properties file may need to be refreshed with a currently valid encrypted password string.
Resolution
To correct the problem, do as follows:
- Log into a Linux shell on the AS host as root .
- Set NCM related shell session variables by running the following command:
source /etc/voyence.conf
- Display the content of the jdbc.properties file by running the following command:
cat $VOYENCE_HOME/ncmcore/webapps/ncm-webapp/WEB-INF/classes/jdbc.properties
- Locate the jdbc.password class variable contained in that file and copy the encrypted password between the XML style <ENC> and </ENC> tags. The content of the file will appear similar to the following:
jdbc.driverClassName=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:5435/voyencedb
jdbc.username=voyence
jdbc.password=<ENC>-26e183264a2ad3b0857d020879c78ea</ENC>
jdbc.minPoolSize=5
jdbc.maxPoolSize=200
jdbc.maxIdleTime=900
jdbc.idleConnectionTestPeriod=300
- Open the $VOYENCE_HOME/Transformation/conf/datasource.properties file for editing (ex.: using the Linux vi text file editor).
- Locate the jdbc.pass class variable and replace the erroneous Unsupported special character $ for encryption. Supported special characters are ~,!,@,#,% string with the encrypted password string obtained from the jdbc.properties file in Step 4 above.
- Save and exit the file.
- Restart all NCM services on the AS host by running the following command during an appropriate maintenance window:
service vcmaster restart
This should allow the data transformer to begin processing backlog records again and you should see your backlog begin to drop relatively quickly. Please perform the preceding step, then utilize the queries I provided to you earlier to determine the effect on the data transformer backlog record count.
Feedback
