Cisco Service Control Application for Broadband Reference Guide, Rel 3.1.6

Database Tables: Formats and Field Contents

---

Each Raw Data Record (RDR) is sent to the Cisco Service Control Management Suite (SCMS) Collection Manager (CM). On the CM, adapters convert the RDRs and store them in database tables. There is a separate table for each RDR type. This chapter presents these tables and their columns (field names and types).

For additional information, such as RDR structure, RDR column and field descriptions, and how the RDRs are generated, see Raw Data Records: Formats and Field Contents, page 2-1.

•Database Tables Overview

•Table RPT_NUR

•Table RPT_SUR

•Table RPT_PUR

•Table RPT_LUR

•Table RPT_TR

•Table RPT_MEDIA

•Table RPT_MALUR

•Table RPT_TOPS_PERIOD0

•Table RPT_TOPS_PERIOD1

•Table INI_VALUES

•Table VLINK_INI

•Table CONF_SE_TZ_OFFSET

Database Tables Overview

Each RDR is routed to the appropriate adapter—the JDBC Adapter or the Topper/Aggregator (TA) Adapter—converted, and written into a database table row. There is a separate table for each RDR type, with a column designated for each RDR field.

In addition to the RDR fields that are specific to each RDR type, the tables RPT_NUR, RPT_SUR, RPT_PUR, RPT_LUR, and RPT_TR contain two universal columns: TIME_STAMP and RECORD_SOURCE. The following values are placed in these two universal columns (field numbers 1 and 2, respectively):

•TIME_STAMP—The RDR time stamp assigned by the SCMS-CM. The field is in UNIX time_t format, which is the number of seconds since midnight of 1 January 1970.

•RECORD_SOURCE—Contains the IP address of the Service Control Engine (SCE) platform that generated the RDR.

The IP address is in 32-bit binary format (displayed as a 4-byte integer).

Table RPT_NUR

Database table RPT_NUR stores data from SUBSCRIBER_USAGE_RDRs.

---

Note This table is not part of the default configuration.

---

These RDRs have the tag 4042321920 .

Table 4-1 Columns for Table RPT_NUR 

Field Name	Type
TIME_STAMP	Date_Time
RECORD_SOURCE	Number
SUBSCRIBER_ID	String
PACKAGE_ID	Number
SUBS_USG_CNT_ID	Number
BREACH_STATE	Number
REASON	Number
CONFIGURED_DURATION	Number
DURATION	Number
END_TIME	Number
UPSTREAM_VOLUME	Number
DOWNSTREAM_VOLUME	Number
SESSIONS	Number
SECONDS	Number

Table RPT_SUR

Database table RPT_SUR stores data from REALTIME_SUBSCRIBER_USAGE_RDRs.

These RDRs have the tag 4042321922 .

Table 4-2 Columns for Table RPT_SUR 

Field Name	Type
TIME_STAMP	Date_Time
RECORD_SOURCE	Number
SUBSCRIBER_ID	String
PACKAGE_ID	Number
SUBS_USG_CNT_ID	Number
MONITORED_OBJECT_ID	Number
BREACH_STATE	Number
REASON	Number
CONFIGURED_DURATION	Number
DURATION	Number
END_TIME	Number
UPSTREAM_VOLUME	Number
DOWNSTREAM_VOLUME	Number
SESSIONS	Number
SECONDS	Number

Table RPT_PUR

Database table RPT_PUR stores data from PACKAGE_USAGE_RDRs.

These RDRs have the tag 4042321924 .

Table 4-3 Columns for Table RPT_PUR 

Field Name	Type
TIME_STAMP	Date_Time
RECORD_SOURCE	Number
PKG_USG_CNT_ID	Number
GENERATOR_ID	Number
GLBL_USG_CNT_ID	Number
CONFIGURED_DURATION	Number
DURATION	Number
END_TIME	Number
UPSTREAM_VOLUME	Number
DOWNSTREAM_VOLUME	Number
SESSIONS	Number
SECONDS	Number
CONCURRENT_SESSIONS	Number
ACTIVE_SUBSCRIBERS	Number
TOTAL_ACTIVE_SUBSCRIBERS	Number

Table RPT_LUR

Database table RPT_LUR stores data from LINK_USAGE_RDRs.

These RDRs have the tag 4042321925 .

Table 4-4 Columns for Table RPT_LUR 

Field Name	Type
TIME_STAMP	Date_Time
RECORD_SOURCE	Number
LINK_ID	Number
GENERATOR_ID	Number
GLBL_USG_CNT_ID	Number
CONFIGURED_DURATION	Number
DURATION	Number
END_TIME	Number
UPSTREAM_VOLUME	Number
DOWNSTREAM_VOLUME	Number
SESSIONS	Number
SECONDS	Number
CONCURRENT_SESSIONS	Number
ACTIVE_SUBSCRIBERS	Number
TOTAL_ACTIVE_SUBSCRIBERS	Number

Table RPT_TR

Database table RPT_TR stores data from TRANSACTION_RDRs.

These RDRs have the tag 4042321936 .

Table 4-5 Columns for Table RPT_TR 

Field Name	Type
TIME_STAMP	Date_Time
RECORD_SOURCE	Number
SUBSCRIBER_ID	String
PACKAGE_ID	Number
SERVICE_ID	Number
PROTOCOL_ID	Number
SAMPLE_SIZE	Number
PEER_IP	Number
PEER_PORT	Number
ACCESS_String	String
INFO_String	String
SOURCE_IP	Number
SOURCE_PORT	Number
INITIATING_SIDE	Number
END_TIME	Number
MILISEC_DURATION	Number
TIME_FRAME	Number
UPSTREAM_VOLUME	Number
DOWNSTREAM_VOLUME	Number
SUBS_CNT_ID	Number
GLBL_CNT_ID	Number
PKG_USG_CNT_ID	Number
IP_PROTOCOL	Number
PROTOCOL_SIGNATURE	Number
ZONE_ID	Number
FLAVOR_ID	Number
FLOW_CLOSE_MODE	Number

Table RPT_MEDIA

Database table RPT_MEDIA stores data from MEDIA_FLOW_RDRs.

These RDRs have the tag 4042323052 .

Table 4-6 Columns for Table RPT_MEDIA  

Field Name	Type
TIME_STAMP	DateTime
RECORD_SOURCE	Number
SUBSCRIBER_ID	String
PACKAGE_ID	Number
SERVICE_ID	Number
PROTOCOL_ID	Number
PEER_IP	Number
PEER_PORT	Number
SOURCE_IP	Number
SOURCE_PORT	Number
INITIATING_SIDE	Number
ZONE_ID	Number
FLAVOR_ID	Number
SIP_DOMAIN	String
SIP_USER_AGENT	String
START_TIME	Number
END_TIME	Number
SEC_DURATION	Number
UPSTREAM_VOLUME	Number
DOWNSTREAM_VOLUME	Number
IP_PROTOCOL	Number
FLOW_TYPE	Number
SESSION_ID	Number
UPSTREAM_AVERAGE_JITTER	Number
DOWNSTREAM_AVERAGE_JITTER	Number
UPSTREAM_PACKET_LOSS	Number
DOWNSTREAM_PACKET_LOSS	Number
UPSTREAM_PAYLOAD_TYPE	Number
DOWNSTREAM_PAYLOAD_TYPE	Number

Table RPT_MALUR

Database table RPT_MALUR stores data from MALICIOUS_TRAFFIC_PERIODIC_RDRs.

These RDRs have the tag 4042322000 .

Table 4-7 Columns for Table RPT_MALUR  

Field Name	Type
TIME_STAMP	DateTime
RECORD_SOURCE	Number
ATTACK_ID	Number
SUBSCRIBER_ID	String
ATTACK_IP	Number
OTHER_IP	Number
PORT_NUMBER	Number
ATTACK_TYPE	Number
SIDE	Number
IP_PROTOCOL	Number
CONFIGURED_DURATION	Number
DURATION	Number
END_TIME	Number
ATTACKS	Number
MALICIOUS_SESSIONS	Number

Table RPT_TOPS_PERIOD0

The Topper/Aggregator (TA) Adapter generates database table RPT_TOPS_PERIOD0 for its shorter aggregation interval (by default, one hour).

Table 4-8 Columns for Table RPT_TOPS_PERIOD0 

Field Name	Type
RECORD_SOURCE	Number
METRIC_ID	Number
SUBS_USG_CNT_ID	Number
TIME_STAMP	DateTime
AGG_PERIOD	Number
SUBSCRIBER_ID	String
CONSUMPTION	Number

For each Top Report, the TA Adapter sorts the subscriber/consumption pairs from the highest consumption to lowest. At the end of each report is a statistic giving the sum of all subscribers for this metric.

If the report is empty, typically when no traffic was reported for the designated service/metric pair during the aggregation period, the DB will still be updated, but the only row in the report will be the final row showing a total consumption of zero. The DB is updated to avoid the perception in the Cisco Service Control Application (SCA) Reporter that the report is not there because of a malfunction.

The possible values for the field METRIC_ID are presented in Table 4-9.

Table 4-9 Metric_ID Values 

Metric_ID	Metric
0	Up Volume
1	Down Volume
2	Combined Volume
3	Sessions
4	Seconds

Table RPT_TOPS_PERIOD1

The Topper/Aggregator (TA) Adapter generates database table RPT_TOPS_PERIOD1 for its longer aggregation interval (by default, 24 hour).

Table 4-10 Columns for Table RPT_TOPS_PERIOD1 

Field Name	Type
RECORD_SOURCE	Number
METRIC_ID	Number
SUBS_USG_CNT_ID	Number
TIME_STAMP	DateTime
AGG_PERIOD	Number
SUBSCRIBER_ID	String
CONSUMPTION	Number

For each Top Report, the TA Adapter sorts the subscriber/consumption pairs from the highest consumption to lowest. At the end of each report is a statistic giving the sum of all subscribers for this metric.

If the report is empty, typically when no traffic was reported for the designated service/metric pair during the aggregation period, the DB will still be updated, but the only row in the report will be the final row showing a total consumption of zero. The DB is updated to avoid the perception in the SCA Reporter that the report is not there because of a malfunction.

The possible values for the field METRIC_ID are presented in Table 4-11.

Table 4-11 Metric_ID Values 

Metric_ID	Metric
0	Up Volume
1	Down Volume
2	Combined Volume
3	Sessions
4	Seconds

Table INI_VALUES

Database table INI_VALUES is updated whenever the service configuration is applied to the SCE platform. This table contains, for each SCE IP address, mappings between numeric identifiers and textual representation for services, packages, and other service configuration components. The mapping is represented as a standard properties file in string form, where each mapping file is stored in one row. The SCA Reporter uses the mappings contained in this table.

Table 4-12 Columns for Table INI_VALUES 

Field Name	Type	Description
TIME_STAMP	DateTime
SE_IP	String	Identification of the SCE platform where these values were applied.
VALUE_TYPE	Number	Key/Value family type. The possible values are: 1—Service ID / service name 2—Package ID / package name 3—TCP port number / port name 4—Time frame ID / time frame name 5—SCE address 32-bit / dotted notation 6—IP protocol number / IP protocol name 7—Signature protocol ID / protocol name 8—P2P signature protocol ID / protocol name 11—Global service usage counter ID / counter name 12—Subscriber service usage counter ID / counter name 13—Package usage counter ID / counter name 15—UDP port number / port name 1002—VoIP signature protocol ID / protocol name 2001—P2P subscriber service usage counter ID / counter 2002—VoIP subscriber service usage counter ID / counter 3001—P2P global service usage counter ID / counter 3002—VoIP global service usage counter ID / counter
VALUE_KEY	String	Key name. For example: Gold, Silver, or Adult Browsing.
VALUE	Number	Numeric reference.

Table VLINK_INI

Database table VLINK_INI is updated when the CM utility update_vlinks.sh is run. This table contains the name and id of each virtual link defined in the SCE platform. The SCA Reporter uses the mappings contained in this table for the Virtual Links reports.

Table 4-13 Columns for Table VLINK_INI 

Field Name	Type	Description
TIME_STAMP	DateTime
SCE_IP	String	Identification of the SCE platform where these values were applied
VLINK_ID	INT16	Virtual link ID
VLINK_DIRECTION	INT8	Virtual link direction
VLINK_NAME	String	Virtual link name

Table CONF_SE_TZ_OFFSET

Database table CONF_SE_TZ_OFFSET contains the time-zone offset in minutes for each SCE platform's clock as configured by the select-sce-tz.sh script.

Table 4-14 Columns for Table CONF_SE_TZ_OFFSET 

Field Name	Type
TIME_STAMP	DateTime
OFFSET_MIN	Number