Generic Table Entry Counter

Purpose

The generic Table Entry Counter data collector provides a possibility to run dynamic database queries to count the number of entries (or the number of distinct values) in any database table of a managed ABAP system. It supports a flexible filtering based on select-options on five fields of a table.

Prerequisites

The Table Entry Counter data collector is initially delivered as of ST-PI 740 SP12.

Hint: To be able to use all described features and to avoid the known bugs, it is highly recommended to use at least ST-PI 740 SP19 or to apply the latest version of SAP Note 2984060 (Collective corrections as of ST-PI 2008_1 SP24 and ST-PI 7.40 SP14).

Usage

Following selection parameters are mandatory:

  • Table Name for the entries to be counted (it can also be a database view)
  • Key Figure - Number of Total Entries (with given filter criteria)/Number of Distinct Entries (on single field name and with given filter criteria)

Additional/optional parameters:

  • Field Name for filtering and corresponding select-options for the actual Filter Values (this is available five times and will be used to generate a WHERE clause)
    Remark
    : always use a pair of Field Name plus Filter Value(s)
  • Alert Text - the standard key figure alert text returns a technical alert message like Number of entries in table <table> = <counter>- Number of distinct values in field <field> of table <table> = <counter>. This is automatically proposed if you leave the parameter Alert Text empty. Alternatively, any free text can be used along with the placeholders like $COUNT, $TABLE, $FIELD to overwrite the standard text.
  • Client – per default the data is collected cross-client, means if a certain table has a client specific data, all clients are considered. To be able to collect the data from a dedicated client only, one of the five available filters must be used, whereby a filter field name should contain a name of the field representing a client (e.g., MANDT) as it's defined in the dictionary. A filter field value the actual client number (e.g., 100). An example can be found in the section “Examples”.
    Remark: client-specific read is protected by an authorization check

Metric Definition

To be able to use the Generic Table Entry Counter, a custom metric has to be created in a custom ABAP monitoring template. The corresponding collector can be found under data collector type "RFC (Push)". 

 

Authorization

The usage of the Table Entry Counter requires additional authorization which is not included in the standard roles for the technical user SDAGENT responsible for the data collection on the managed system. Please assign at least S_TABU_DIS to technical user SDAGENT to be able to collect data from the relevant tables.

If a cross-client read is planned (it's the case per default, if the CLIENT is not explicitly passed in the SELECT statement), S_TABU_CLI is required.

The existing SAP table authorization concept is mainly based on the group assignment of tables and the authorization object S_TABU_DIS. But authorization object S_TABU_DIS might not always be sufficient. Therefore, it might be necessary to maintain the S_TABU_NAM.

Examples

Below some examples how this collector can be used in the context of System Monitoring.
  1. Number of total entries of a table

    Metric parameterization:
    Table Name = USR02
    Key Figure  = ENTRIES

    Corresponding SQL query:
    SELECT COUNT (*) FROM USR02
  2. Number of entries of a table with a specific field value

    Metric parameterization:
    Table Name = USR02
    Key Figure = ENTRIES
    1. Field Name = USTYP
    1. Field Value = A

    Corresponding SQL query:
    SELECT COUNT (*) FROM USR02

    Remark: collects a value over all clients

  3. Number of entries of a table with a specific field value from a single client

    Metric parameterization:
    Table Name = USR02
    Key Figure = ENTRIES
    1. Field Name = USTYP
    1. Field Value = A
    2. Field Name = MANDT
    2. Field Value = 100

    Corresponding SQL query:
    SELECT COUNT(*) FROM USR02 WHERE USTYP = ‘A' AND MANDT = '100'

  4. Number of distinct entries

    Metric parameterization:
    Table Name = USR02
    Key Figure = DISTINCT
    Field = CLASS

    Corresponding SQL query:
    SELECT COUNT (DISTINCT CLASS) FROM USR02