Monitoring Key Performance Indicators

10 Monitoring Key Performance Indicators

Learn how to monitor key performance indicators (KPIs) in your Oracle Communications Billing and Revenue Management (BRM) system.

Topics in this document:

Using the pin_db_alert Utility to Monitor Key Performance Indicators

Key performance indicators (KPIs) are metrics you use to quantify the health of your database and to alert you when potential issues exist. They identify database tables that must be archived or purged and indexes, triggers, and stored procedures that are missing or invalid.

KPIs are monitored when you run the pin_db_alert.pl utility. Generally you set up a cron job to run the utility periodically to monitor the health of your database. For more information, see "Running the pin_db_alert.pl Utility".

Each KPI is identified by an ID that associates a component being monitored to a corresponding validation value. For example, you can monitor the size of an audit table with a size threshold that monitors the number of rows in that audit table. When the threshold value is reached, the results are returned and an alert notification can be sent, warning you of the component's condition.

The component and validation functionality for each KPI comprises:

A data extraction module, which queries the database for the KPI data and writes the results to an output file.
A validation module, which compares the query results to validation parameters defined in a configuration file and writes the validation status to an output file.

After the validation results are written to the output files, a decision module (DecisionUtility.pm) evaluates each KPI result and determines whether to generate email alert notifications based on the KPI result status. For more information, see "About KPI Status and Alert Notifications".

KPI Default Behavior

Table 10-1 contains a list of supported KPIs and provides the default behavior of their data and validation modules.

Table 10-1 Supported KPIs and Default Behavior

KPI ID	Default Behavior
AuditHistoryAge	The auditAge module calculates the age of the audit tables listed in the pin_db_alert.conf file's DATA_PLUGINS entry and DEFAULT_AUDIT_TABLES entry. It writes the results to the auditAge_AuditHistoryAge.out file. The auditAge_validation module uses threshold values in the auditAge validation configuration file to determine which audit tables in the results file are at the threshold, and writes them to the auditAge_validation_AuditHistoryAge.out file. For information on changing the default age thresholds, see "Monitoring the Age of Audit Tables".
AuditTableSize	The auditSize module calculates the number of rows present in the audit tables listed in the pin_db_alert.conf file's DATA_PLUGINS entry and DEFAULT_AUDIT_TABLES entry and writes the results to the auditSize_AuditTableSize.out file. The auditSize_validation module uses the threshold values in the auditSize validation configuration file to determine which audit tables in the results file are at the threshold and writes them to the auditSize_validation_AuditTableSize.out file. For information on changing the default size thresholds, see "Monitoring the Size of Audit Tables".
OldestEventAge	The eventData module calculates the age of the oldest event in the event_t table, as well as the records in the tables defined in the pin_db_alert.conf file's DATA_PLUGINS entry, and writes the results to the eventData_OldestEventAge.out file. The eventData_validation module uses the threshold values in the eventData validation configuration file to determine which entries in the results file are at the threshold, and writes them to the eventData_validation_OldestEventAge.out file. For information on changing the default event age, see "Monitoring the Age of Events".
ACTIVETRIGGERS	The triggersList module retrieves a list of active triggers in the BRM system and writes their names and status (ENABLED or DISABLED) to the triggersList_ACTIVETRIGGERS.out file. The triggersList_validation module compares the list of active triggers in the triggersList validation configuration file to the triggers in the results file and writes missing triggers to the triggersList_validation_ACTIVETRIGGERS.out file. Important: If you installed optional managers that use unique triggers or if you created custom triggers, you must add them to the triggersList validation configuration file to monitor their status. See "Monitoring Active Triggers".
INDEXES	The indexList module retrieves a list of unique indexes in the BRM system and writes the index names and uniqueness values to the indexList_INDEXES.out file. The table name and column name for each index is also listed. The indexList_validation module compares the list of indexes in the indexList validation configuration file to the indexes in the results file and writes missing or invalid indexes to the indexList_validation_INDEXES.out file. Important: If you installed optional managers that use unique indexes or if you created custom indexes, you must add them to the indexList validation configuration file to monitor their status. See "Monitoring Indexes".
PROCEDURES	The proceduresList module retrieves a list of stored procedures in the BRM system and writes the stored procedure names and status (VALID or INVALID) to the proceduresList_PROCEDURES.out file. The proceduresList_validation module compares the list of stored procedures in the proceduresList validation configuration file to the procedures in the results file and writes missing procedures to the proceduresList_validation_PROCEDURES.out file. Important: If you installed optional managers that use unique stored procedures or if you created custom stored procedures, you must add them to the proceduresList validation configuration file to monitor their status. See "Monitoring Stored Procedures".

You can enable email alerts to notify a list of people about the validation results. For more information, see "Setting Up Email Alert Notifications".

About KPI Status and Alert Notifications

When the pin_db_alert.pl utility runs, it returns a PASS or FAILURE status for each configured KPI, which includes a severity level for the status. The following severity levels listed in Table 10-2 are possible for any KPI:

Table 10-2 KPI Status Severity Levels

Severity	Description
CRITICAL	Performance, functionality, or both are heavily impacted and require immediate attention. Critical failures generally involve data corruption (for example, when an event table is missing data after a system upgrade). Set up alert notifications for critical failures so you can correct such problems immediately and avoid further corruption.
MAJOR	Performance, functionality, or both are impacted and require immediate attention. Major failures generally involve potentially serious performance degradations (for example, when an index is missing or an index contains columns that are out of order). These problems can occur when you customize your BRM software. Major failures also include issues where functionality can be impacted. For example, if the TRIG_CYCLE_DEFERRED_TAX trigger is missing and billing runs, cycle taxes will not be calculated. Set up alert notifications for major failures so you can correct problems immediately and avoid further degradation or data corruption.
MINOR	Performance might be impacted and will need attention in the future. Minor failures involve large audit tables.
WARNING	Performance and functionality both work as expected, but performance may be impacted in the future. For example, depending on your hardware and software resources, you can set up an alert notification when an event table reaches an age threshold or an audit table reaches a size threshold, so they can be archived or purged. Warning failures generally do not impact performance and never impact functionality.
NORMAL	No data or performance risks were found. This status is valid only for PASS results.

You can configure the pin_db_alert.pl utility to send email notifications to alert a list of people when a KPI is at a specified severity level. For more information, see "Setting Up Email Alert Notifications".

Setting Up KPI Monitoring

To monitor KPIs, first you configure the KPI data entries in the pin_db_alert.pl utility's configuration file, and then you set up the validation thresholds in each validation module's configuration file.

The pin_db_alert.pl utility's configuration file contains entries for all KPIs; therefore, Oracle recommends that you configure this file for all KPIs before you set up the validation thresholds for each individual KPI.

Note:

If you do not define KPI validation thresholds, the validation process will not occur; therefore, any alert notifications you configured will not be sent.

Setting up KPI monitoring requires the following:

The default configuration for monitoring KPIs is defined in the pin_db_alert.pl utility's configuration file (BRM_home/diagnostics/pin_db_alert/pin_db_alert.conf).

To edit this file, open it with a text editor and perform the following tasks as necessary. For more information, see the comments in the pin_db_alert.conf file.

In the KPI_IDS entry, specify the KPI ID for each KPI to monitor.

By default, all KPIs are listed; therefore, if you do not want to monitor one, remove it from the default list. For a list of KPI IDs, see "KPI Default Behavior".
In the DATA_PLUGINS entry, specify the data module and desired values for each KPI listed in the KPI_IDS entry. See "Specifying Which KPI Data Is Extracted".

Note:

In the sample pin_db_alert.conf file, values are provided for the AuditHistoryAge and AuditTableSize KPIs; however, the OldestEventAge KPI does not contain any values. You must provide your own values. See "Monitoring the Age of Events".
In the VALIDATION_PLUGINS entry, specify the validation module for each KPI listed in the KPI_IDS entry.

Note:

Make sure the validation modules are listed in the same order as their associated data modules in the DATA_PLUGINS entry.
In the STATUS entry, configure the alert notifications. Specify the status and severity, and list the email addresses that get notified by the status/severity combination. For more information, see "Setting Up Email Alert Notifications".
In the DEFAULT_AUDIT_TABLES entry, specify which audit tables to monitor by default. These audit tables are monitored in addition to any tables you list as values in the DATA_PLUGINS entry for the auditAge and auditSize modules.
In the DB_USER and DB_PASSWD entries, specify the database user ID and encrypted password that are listed in the sm_id and sm_pw entries in the Data Manager (DM) pin.conf file. For more information, see "Enabling Database Access".

Specifying Which KPI Data Is Extracted

To specify which data is extracted from the database during KPI monitoring:

Open the pin_db_alert.pl utility's configuration file (BRM_home/diagnostics/pin_db_alert/pin_db_alert.conf) with a text editor.
In the DATA_PLUGINS entry, specify the data module and desired values for each KPI in the KPI_IDS entry:
- To extract data for the auditAge data module:
  
  Specify the audit table names to monitor using the following syntax, separating each audit table name by a space:
```
@DATA_PLUGINS =("auditAge Audit_table_name  Audit_table_name");
```
  Note:
  
  These tables are in addition to audit tables you have listed in the DEFAULT_AUDIT_TABLES entry.
- To extract data for the auditSize data module:
  
  Specify the audit table names to monitor using the following syntax, separating each audit table name by a space.
```
@DATA_PLUGINS =("auditSize Audit_table_name  Audit_table_name");
```
  Note:
  
  These tables are in addition to audit tables you have listed in the DEFAULT_AUDIT_TABLES entry.
- To extract data for the eventData module:
  
  Specify the events to monitor using the following syntax:
```
@DATA_PLUGINS =("eventData Table_name:Column_name:Operator:Column_value");
```
  where:
  - Table_name is the name of the table that contains the event data.
  - Column_name is the name of the table column that contains the event data.
  - Operator is any standard SQL operator.
  - Column_value is the POID of the event.
  For example:
```
@DATA_PLUGINS =("eventData event_t:account_obj_id0:=:21950");
```
  Note:
  
  You can add any number of values for the eventData module, separated by spaces; however, you can specify only one operator per table. If the operator or syntax is incorrect, the table is not validated, and an error is written to the data extraction output file.
- To extract data for the triggersList, proceduresList, and indexList modules:
  
  The triggersList, proceduresList, and indexList modules take no values. To extract data for these modules, list them in the DATA_PLUGINS entry using the following syntax:
```
@DATA_PLUGINS =("triggersList","proceduresList","indexList");
```
  Enclose the entire DATA_PLUGINS value string with parentheses ( ) and separate each data value string with commas. For example:
```
@DATA_PLUGINS =("auditSize au_service_t au_product_t au_account_t au_rate_t",
"eventData event_t:account_obj_id0:=:21956 account_t:poid_id0:=:21956:",
"auditAge au_service_t au_product_t",
"triggersList","proceduresList","indexList");
```
Save and close the file.

Setting Up Email Alert Notifications

To configure the pin_db_alert.pl utility to send email notifications when a KPI validation returns a specified result/severity combination:

Open the pin_db_alert.pl utility's configuration file (BRM_home/diagnostics/pin_db_alert.conf) with a text editor.
Edit the STATUS entry using the following syntax:
```
'Error:MAIL_ALERT:Notification_list'
```
where:
- Error is a combination of the status and severity, separated by a dot (.). The following values are valid:
  
  FAIL.CRITICAL
  
  FAIL.MAJOR
  
  FAIL.MINOR
  
  FAIL.WARNING
  
  PASS.WARNING
  
  PASS.NORMAL
- Notification_list is a comma-separated list of email addresses to which the validation results are sent. You can have any number of email addresses for any error.
Be sure to enclose each status string in single quotation marks (' ').

For example:
```
@STATUS=('FAIL.CRITICAL:MAIL_ALERT:IT@example.com', 'FAIL.MINOR:MAIL_ALERT: example@example.com, sysadm@example.com');
```
Note:

You cannot configure email alerts for a specific KPI.
Save and close the file.

Enabling Database Access

The pin_db_alert.pl utility requires the database user name and password to query the database for KPIs.

Open the pin_db_alert.pl utility's configuration file (BRM_home/diagnostics/pin_db_alert/pin_db_alert.conf).
In the DB_USER and DB_PASSWD entries, specify the database user ID and encrypted password, respectively.

Note:

These must be the same database user ID and password specified in the sm_id and sm_pw entries in the DM pin.conf file.

Use the following syntax:
```
DB_USER="User_ID";
DB_PASSWD="Encrypted_passwd";
```
Save and close the file.

For more information about encrypting passwords, see "About Encrypting Data" in BRM Developer's Guide.

Monitoring the Size of Audit Tables

To monitor the size of audit tables:

If necessary, specify the auditSize module values in the DATA_PLUGINS entry of the pin_db_alert.pl utility's configuration file. See "Setting Up KPI Monitoring".
Open the auditSize validation configuration file (BRM_home/diagnostics/pin_db_alert/auditSize_validation_AuditTableSize.conf) with a text editor.
- To change a size threshold for an existing table, change the number of rows specified in the AUDIT_SIZE_THRESHOLD value for that table.
- To add an audit table, add a new AUDIT_SIZE_THRESHOLD entry for that table.
- To omit an audit table from the validation process, either delete the AUDIT_SIZE_THRESHOLD entry for that table or comment out the entry.
For details on how to configure the AUDIT_SIZE_THRESHOLD entry, see the comments in the AuditTableSize configuration file.
Save the file.

Monitoring the Age of Audit Tables

To monitor the age of audit tables:

If necessary, specify the auditAge module values in the DATA_PLUGINS entry of the pin_db_alert.pl utility's configuration file. See "Setting Up KPI Monitoring".
Open the auditAge validation configuration file (BRM_home/diagnostics/pin_db_alert/auditAge_validation_AuditHistoryAge.conf) with a text editor.
- To change an age threshold for a table, change the number of days specified in the AUDIT_AGE_THRESHOLD value for that table.
- To add an audit table, add a new AUDIT_AGE_THRESHOLD entry.
- To omit an audit table from the validation process, either delete the AUDIT_AGE_THRESHOLD entry for that table or comment out the entry.
For details on how to configure the AUDIT_AGE_THRESHOLD entry, see the comments in the AudiHistoryAge configuration file.
Save the file.

Monitoring the Age of Events

To monitor the age of events:

If necessary, configure the eventData module values in the DATA_PLUGINS entry of the pin_db_alert.pl configuration file (BRM_home/diagnostics/pin_db_alert.conf). See "Specifying Which KPI Data Is Extracted".

Note:

You can add any number of arguments for the eventData module; however, you can specify only one operator per table. If the operator or syntax is incorrect, the table is not validated, and an error is written to the data extraction output file.
Open the eventData validation configuration file (BRM_home/diagnostics/pin_db_alert/eventData_validation_OldestEventAge.conf) with a text editor.
- To change an age threshold, change the number of days specified in the OLDEST_THRESHOLD value for the table.
- To add a table to monitor, add a new OLDEST_THRESHOLD entry for the table.
- To omit a table from the validation process, either delete the OLDEST_THRESHOLD entry for that table or comment it out.
For details on how to configure the OLDEST_THRESHOLD entry, see the comments in the OldestEventAge configuration file.
Save the file.

Monitoring Active Triggers

To monitor a trigger for an optional manager or customization that is not part of BRM:

If necessary, specify the triggersList module in the DATA_PLUGINS entry in the pin_db_alert.pl utility's configuration file. See "Setting Up KPI Monitoring".
Open the ACTIVETRIGGERS validation configuration file (BRM_home/diagnostics/pin_db_alert/triggersList_validation_ACTIVETRIGGERS.conf) with a text editor.
Add a new entry for the trigger using the following syntax:
```
ENABLED trigger_name
```
Save the file.
Restart the Connection Manager (CM).

Monitoring Indexes

To monitor an index for an optional manager or customization that is not part of BRM:

If necessary, specify the indexList module in the DATA_PLUGINS entry in the pin_db_alert.pl utility's configuration file. See "Setting Up KPI Monitoring".
Open the BRM_home/diagnostics/pin_db_alert/indexList_validation_INDEXES.conf file.

Add a new entry for the index using the following syntax:

table_name   column_name   index_name   UNIQUE

To add a composite index, add each column name as a separate entry, in the order of the columns in the index. For example:

ACCOUNT_NAMEINFO_T    OBJ_ID0      I_ACCOUNT_NAMEINFO__I    UNIQUE
ACCOUNT_NAMEINFO_T    REC_ID       I_ACCOUNT_NAMEINFO__I    UNIQUE
ACCOUNT_T             ACCOUNT_NO   I_ACCOUNT_NO__ID         UNIQUE

Save the file.

Monitoring Stored Procedures

To monitor a stored procedure for an optional manager or customization that is not part of BRM:

If necessary, specify the proceduresList module in the DATA_PLUGINS entry in the pin_db_alert.pl utility's configuration file. See "Setting Up KPI Monitoring".
Open the PROCEDURES validation configuration file (BRM_home/diagnostics/pin_db_alert/proceduresList_validation_PROCEDURES.conf file) with a text editor.
Add a new entry for the stored procedure using the following syntax:
```
procedure_name VALID
```
Save the file.

Running the pin_db_alert.pl Utility

Run the pin_db_alert.pl utility periodically to monitor the health of your database. The cron command is the typical way to do this.

Note:

You can also run the pin_db_alert.pl utility manually at the command line (for example, after system upgrades).

Use a cron job with a crontab entry to run the pin_db_alert.pl utility at a specified time. The following crontab entry runs the utility at 1:00 a.m. on a quarterly basis:

0 1 * */3 * BRM_home/bin/pin_db_alert.pl &

Defining Custom KPIs

You can define custom KPIs (for example, to monitor the integrity of customer subscriber information after system upgrades):

Define a new KPI called SubscriberInformation to monitor the consistency of subscriber data over a period of time. This KPI must include a data module that retrieves the subscriber information and a validation module that verifies this data.
Create a configuration file for the KPI validation module and specify the relevant threshold information.
Add the new KPI information to the pin_db_alert.conf file. For information on the entries in this file, see "Setting Up KPI Monitoring".