DB Networked setup

Notes before installation

Oplon Application Delivery Controller is a product intended for mission-critical environments therefore only personnel who carried out the course and passed the exam is authorized to certify the installation and maintenance of the products in operation. All the certified people have an identification license issued by Oplon.

Oplon Application Delivery Controller manages the persistence of Statistics on Database Relationship in a Transactional Way. In default configuration uses the JavaDB Embedded / images engine provided directly in the Java distribution (JDK). In addition to JavaDB Embedded Oplon Application Delivery Controller was designed for use the most popular Transactional Relational Databases. Below the complete list of supported databases for storing statistics:

  1. Oracle

  2. MS SQLServer

  3. MySQL (InnoDB engine)

  4. Postgres

  5. JavaDB embedded (default)

  6. JavaDB Networked

The statistics persistence module was designed to be able to be decoupled from the balancing system. This characteristic it allows the realization of different architectures according to the needs of historicization.

In the default installation these features are not immediately evident having been deliberately made so as to create minimal impact in the base installation.

Types of statistics historicization

There are basically 2 types of historicization of statistics:

  1. Embedded

  2. Networked

Embedded means the exclusive use of the Oplon Application Delivery Controller without the ability to interact during run-time with custom SQL queries. (N.B. It is possible switch from embedded to networked mode at any time without loss of information)

Networked means the use of a DBMS among those supported over the network with the use of drivers (JDBC) set to provision by individual manufacturers. With this type of historicization it is possible to query the Database with your own queries SQL during run-time.

Architecture

The architecture of statistical historicization was studied for have the least impact on core processing (the balance of load) while giving maximum configuration flexibility e very high scalability.

It is based on 3 logic levels

image1

These three logical levels from a system "Process" point of view operating mode can be collapsed into Embedded mode on 2 "Process". In Networked mode, however, there will be at least 3 "Process" distinct.

Operating System Process in Embedded Mode

image2

Since these services are reachable through the network, the architectures possible are many.

The preset architecture has a very low impact either in installation and maintenance and is well suited to those realities which only need some impromptu data to verify operations and that do not need further processing statistics or consolidated.

image3

With the Embedded architecture, the statistics remain within the individual Instances and are managed automatically by the broker system ( Oplon Application Delivery Controller WebCacheBroker ).

In the pre-set parameters the Oplon Application Delivery process WebCacheBroker controller maintains data in Embedded Database for 2 days while verifying the physical occupations in support of mass storage. In case these dimensions exceed GB (1GB) will automatically regenerate the DB avoiding the exhaustion of resources. Both the 2 day limit and the limit of 1GB can be modified by parameters (see Oplon S.A.A.I. Reference Guides).

Obviously being separate processes Oplon Application Delivery Controller (the producer of the statistics) and Oplon WebCacheBroker (the broker, Broker) and connected through the network would be anyway it is possible to delocalize the historicization on third machines, both in the same DMZ is in the backend. This possibility is certainly better exploited in the way of using a Networked Database where it will be SQL queries can be run on data from traffic.

In the image below a 3-level configuration completely separated between the production of statistics, brokerage and historicization on DBMS.

image4

It is possible to achieve this result for two distinct reasons, the first is derived from the architectural design that is born out of three distinct ones levels, the second is derived from the logical separation of data within the same DBMS. With Oplon Application Delivery Controller Standard HA and Oplon Application Delivery Controller Enterprise HA can in fact be consolidated within it DB and in the same tables the data from multiple Instances

  • Oplon Application Delivery Controller *.

Database structure and tables creation

The persistence of traffic data in the statistical database comes realized through the following tables:

  • SESSION_ACTIVITY

    • analysis of session usage
  • L7_HTTP_HTTPS

    • persistence of HTTP and HTTPS traffic information.
  • L4_TCP_TCPSSL

    • persistence of TCP and TCP traffic information such as SSL terminator
  • L4_DATAGRAM

    • persistence of UDP and MULTICAST traffic information
  • POOL_QUEUES_ACTIVITY

    • analysis of the use of request resolution pools of service
  • INCOMING_QHIGHWATER_LEVEL

    • analysis of the use of queue filling incoming connections
  • WAF_EXEC

    • log of the rules executed successfully by the Web Application Firewal
  • DDOS_EXEC

    • DoS and DDoS attacks log
  • DAILY_ALERT

    • log of notifications from Attack Prophecy
  • GUIRT_MASTER

    • master log of the execution of the GUI Reliability Tool traces
  • GUIRT_DETAIL

    • detail log of the execution of the steps of the GUI tracks Reliability Tool

The table schemas for each supported database are contained in the (LBL_HOME) / legacyBin / DatabasesScript directory.

In this directory we can find respectively:

  • ORACLE_LBLDBTables.sql

    • ORACLE database table creation
  • SQLSERVER_LBLDBTables.sql

    • Microsoft SQL Server database table creation
  • MySQL_LBLDBTables.sql

    • Sun MySQL database table creation
  • POSTGRES_LBLDBTablesCreation.sql

    • Postgres database table creation
  • JavaDB_LBLDBTables

    • Java DB derby

Apart from some differences related to the typing of the fields, tables, on different databases, contain the same data. The following pages will describe the per field in detail field the meanings of the structures in order to easily create reports, statistics, traffic-based billing systems.

SESSION_ACTIVITY table

Table containing snaps at 10 "intervals of the state of the routing sessions.

RECORD_TYPE Int 6 Record type
VRRP_HOST_NAME varchar (4000) Host name of the VRRP service. This value associated with the VRRP_PORT_NUMBER, define the balancing instance for the traffic data consolidation. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_PORT_NUMBER int Port number of the VRRP service. This value associated with the VRRP_HOST_NAME, define the balancing instance for the traffic data consolidation
END_POINTS_GROUPING varchar (4000) This is the name of the endpoint group
DOMAIN_REQUEST varchar (4000) This is the domain associated with the session
COMMAND varchar (4000) EMPTY
URI_PATH_REQUEST varchar (4000) EMPTY
RESPONSE_CODE int 0
END_POINT_HOST_NAME varchar (4000) Host name of the endpoint associated with the session
END_POINT_PORT_NUMBER int Port number of the host of the endpoint associated with the session
END_POINT_URI_PATH varchar (4000) This is the URIPath of the context being processed
USER_ID varchar (4000) Future use
CLIENT_ADDRESS varchar (4000)

The client address reports the address of the client that requested the service.

e.g. 192.168.43.150

If transmission to layer 7 HTTP / S and the management of the X-Forwarded-For entity (HTTP HEADER) has been set through the parameter xForwardedFor = "true" in the

listener in iproxy.xml the value of the entire IP chain will be transferred.

Obviously OPLON can only ensure the last element of the chain as the other elements are only useful for statistical purposes being populated by other tools

infrastructure such as proxies.

e.g .: 192.168.32.115,192.168.41.10,192.168.43.150

THIS_DATE date Date standardized at 00:00:00
THIS_TIME time Time with date uniform 01-01-1970
NUMBER_OF_ACTIVE_SESSIONS int It is the number of sessions related to the grouping key (in red)

The keys used are:

`` index K1_L7_HTTP_HTTPS ON L7_HTTP_HTTPS (THIS_DATE);

index K2_L7_HTTP_HTTPS ON L7_HTTP_HTTPS (THIS_TIME); ``

Table L7_HTTP_HTTPS

This table contains HTTP and HTTPS (SSL) protocol traffic.

RECORD_TYPE Int 0 = HTTP
1 = HTTPS
Record type
VRRP_HOST_NAME varchar (4000) Host name of the VRRP service. This value associated with the VRRP_PORT_NUMBER, define the balancing instance for the traffic data consolidation. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_PORT_NUMBER int Port number of the VRRP service. This value associated to the VRRP_HOST_NAME, define the balancing instance for the traffic data consolidation
END_POINTS_GROUPING varchar (4000) This is the name of the endpoint group
DOMAIN_REQUEST varchar (4000) This is the domain requested by the client
COMMAND varchar (4000) It is the HTTP command requested by the client (GET, POST, etc)
URI_PATH_REQUEST varchar (4000) Path referring to the domain requested by the client. This value reports the result of the request after the rewriting action if it exists. It is the actual request that is made in the backend.
URI_PATH_REQUEST_ORG varchar (4000) Original path referring to the domain requested by the client before any rewriting
CONTENT_TYPE_REQUEST varchar (4000) content type in request
CONTENT_TYPE_RESPONSE varchar (4000) content type in response
RESPONSE_CODE int Response CODE HTTP sent by the server to the client in response to its request
END_POINT_HOST_NAME varchar (4000) Name of the host on which the service request was processed
END_POINT_PORT_NUMBER int Port number of the host on which the service request was processed
END_POINT_URI_PATH varchar (4000) This is the context URL path on which the request was processed
USER_ID varchar (4000)

The column contains the information of the profiled and authenticated user. The information is a sum of the characteristic elements of a BASIC authentication (Basic Authentication) and of the authorization deriving from a digital certificate. This column contains respectively:

If authorization with digital certificate, the Subject with an additional value bearing the Serial Number of the certificate eg:

"CN = clientname, OU = clientlob, O = clientcompany, L = clientcountry, ST = clientdistrict, C = IT, SERIAL = 1282479557"

If basic authentication

“BASIC = usr1”

If both credentials are present, the resulting value will be:

"BASIC = usr1, CN = clientname, OU = clientlob, O = clientcompany, L = clientcountry, ST = clientdistrict, C = IT, SERIAL = 1282479557"

CLIENT_ADDRESS varchar (4000)

The client address reports the address of the client that requested the service.

e.g. 192.168.43.150

If transmission to layer 7 HTTP / S and the management of the X-Forwarded-For entity (HTTP HEADER) has been set through the parameter xForwardedFor = "true" in the

listener in iproxy.xml the value of the entire IP chain will be transferred.

Obviously OPLON can only ensure the last element of the chain as the other elements are only useful for statistical purposes being populated by other tools

infrastructure such as proxies.

eg .: 192.168.32.115,192.168.41.10,192.168.43.150

USER_AGENT varchar (4000) User-agent value in the HTTP HEADER
COOKIES varchar (4000) Cookie value in the HTTP HEADER
REFERER varchar (4000) Referer value in the HTTP HEADER
URI_PARAMETERS varchar (4000)

Parameterized value and / or query string.
The parameters, defined in the URL by the character; and the query strings defined give character? are normalized to a single value separated by & amp ;. eg .:

/ aaa / bbb; c1 = aaa & amp; c2 = bbb? q1 = ccc & amp; q2 = dddd

VALUE IN URI_PARAMETERS

c1=aaa&c2=bbb&q1=ccc&q2=dddd

PROTOCOL_VERSION varchar (4000) HTTP / 1.0
HTTP / 1.1
INCOMING_ADDRESS varchar (4000) Local address where the connection was accepted
INCOMING_PORT_NUMBER int Local port where the connection was accepted
THIS_DATE date Date standardized at 00:00:00
THIS_TIME time Time with date uniform 01-01-1970
COUNTER bigint Number of operations summed in this record (if the defaults have not been changed it is the number of operations performed in its time window in the last 10 ")
RESPONSE_TIME bigint It is the response time of the requested service (This value is relative to the traffic of the last 10 ". To have an average value it is necessary to divide it by the COUNTER). Value expressed in nanoseconds.
LAP_TIME_A bigint Time between the end of the reading of the client HEADER and the beginning of the connection towards the endpoint (This value is relative to the traffic of the last 10 ". To have an average value it is necessary to divide it by the COUNTER). Value expressed in nanoseconds.
LAP_TIME_B bigint Connection time to the endpoint (This value is relative to the traffic of the last 10 ". To have an average value it is necessary to divide it by the COUNTER). Value expressed in nanoseconds.
LAP_TIME_C bigint Time between the connection made to the endpoint and the beginning of the reading of the response HEADER. If the client sends the body it is the transmission time of the body from the client to the endpoint. (This value is relative to the traffic of the last 10 ". To have an average value it is necessary to divide it by the COUNTER). Value expressed in nanoseconds.
LAP_TIME_D bigint Read time of the endpoint Header (This value is relative to the traffic of the last 10 ". To get an average value it is necessary to divide it by the COUNTER). Value expressed in nanoseconds.
LAP_TIME_E bigint Time between the end of the Header reading and the end of the data in response (This value is relative to the traffic of the last 10 ". To have an average value it is necessary to divide it by the COUNTER). Value expressed in nanoseconds.
HEADER_LENGTH_FROM_CLIENT bigint It is the length of the client request HEADER to the endpoints (This value is relative to the traffic of the last 10 ". To get an average value it is necessary to divide it by the COUNTER)
BYTES_SENT_FROM_CLIENT bigint Total transferred including the HEADER from the client to the endpoints (This value is relative to the traffic of the last 10 ". To have an average value it is necessary to divide it by the COUNTER)
HEADER_LENGTH_FROM_END_POINT bigint It is the length of the response HEADER of the endpoints towards the clients (This value is relative to the traffic of the last 10 ". To have an average value it is necessary to divide it by the COUNTER)
BYTES_SENT_FROM_END_POINT bigint Total transferred including the HEADER from the endpoints to the clients (This value is relative to the traffic of the last 10 ". To have an average value it is necessary to divide it by the COUNTER) relative to the grouping key (in red)
SESSIONID varchar (100) It is a unique identifier (hash) generated by the set of client address + referer
REFERRING_DOMAIN varchar (300) Host name from which the request originated
BROWSER varchar (100) Name of the browser or client that made the request
BROWSER_REL varchar (100) Release of the browser or client that made the request
OS varchar (100) Operating system from which the request was received
OS_REL varchar (100) Release of the operating system from which the request was received
DEVICE varchar (100) Manufacturer of the device from which the request was received
COUNTRY_CODE varchar (100) Initials of the state from which the request was received
COUNTRY_DES varchar (350) Description of the state from which the request came
INTERNET_ADDRESS varchar (350) Is the socket-derived client address if XFF does not exist. If XFF exists, it is the second in the XFF chain.

The keys used are:

index K1_L7_HTTP_HTTPS ON L7_HTTP_HTTPS (THIS_DATE);

index K2_L7_HTTP_HTTPS ON L7_HTTP_HTTPS (THIS_TIME);

Table L4_TCP_TCPSSL

As for the previous table, this table contains the data of traffic related to TCP and TCP connector activities such as SSL terminator. The initial structure is very similar to the HTTP / S one to then differentiate on traffic data

RECORD_TYPE Int 2 = TCP
3 = SSL
Record type
VRRP_HOST_NAME varchar (4000) Host name of the VRRP service. This value associated with the VRRP_PORT_NUMBER, define the balancing instance for the traffic data consolidation. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_PORT_NUMBER int Port number of the VRRP service. This value associated to the VRRP_HOST_NAME, define the balancing instance for the traffic data consolidation
END_POINTS_GROUPING varchar (4000) This is the name of the endpoint group
DOMAIN_REQUEST varchar (4000) If WebSocket is the required host at layer 7
COMMAND varchar (4000)

Identifies the data flow:
CLIENT_FLOW from client to
endpoints ENDPOINT_FLOW from endpoints to clients

WebSocket:
WSCLIENT_FLOW from client to
endpoints WSENDPOINT_FLOW from endpoints to clients

URI_PATH_REQUEST varchar (4000) If WebSocket is the requested URL Path at layer 7
RESPONSE_CODE int 0
END_POINT_HOST_NAME varchar (4000) Name of the host on which the service request was processed
END_POINT_PORT_NUMBER int Port number of the host on which the service request was processed
END_POINT_URI_PATH varchar (4000) If WebSocket is the requested URL Path at layer 7
USER_ID varchar (4000)

If a SSL client authentication contains the Subject and the additional values ​​of the certificate bearing the Serial Number eg .:

"CN = clientname, OU = clientlob, O = clientcompany, L = clientcountry, ST = clientdistrict, C = IT, SERIAL = 1282479557"

CLIENT_ADDRESS varchar (4000)

The client address reports the address of the client that requested the service.

e.g. 192.168.43.150

COOKIES varchar (4000)

Unique value associated with the connection. This value is populated with the LBLCOLOR value to identify the single connection at layer 4. To activate the population of this value it is necessary to set the parameter distinguishSingleConnection = " true " in the & lt; bind & gt; paragraph of the iproxy.xml parameter file

If WebSocket contains HTTP / S layer 7 connection cookies

INCOMING_ADDRESS varchar (4000) Local address where the connection was accepted
INCOMING_PORT_NUMBER int Local port where the connection was accepted
THIS_DATE date Date standardized at 00:00:00
THIS_TIME time Time with date uniform 01-01-1970
COUNTER bigint Number of operations summed in this record (if the defaults have not been changed it is the number of operations performed in its time window in the last 10 ")
BYTES_FORWARDED bigint It is the number of bytes exchanged based on the flow (client & gt; endpoint or endpoint & gt; client) related to the grouping key (in red)
START_ADV_TIME bigint This is the moment when the first buffer / character expressed in nanoseconds is detected
END_ADV_TIME bigint This is when the last buffer of the stream is flushed in nanoseconds
TOTAL_ADV_TIME bigint It is the total information forwarding time (END_ADV_TIME - START_ADV_TIME) expressed in nanoseconds

The keys used are:

`` index K1_L4_TCP_TCPSSL ON L4_TCP_TCPSSL (THIS_DATE);

index K2_L4_TCP_TCPSSL ON L4_TCP_TCPSSL (THIS_TIME); ``

Table L4_DATAGRAM

As for the previous table, this table contains the data of traffic related to TCP and TCP connector activities such as SSL terminator. The initial structure is very similar to the HTTP / S one to then differentiate on traffic data

RECORD_TYPE Int 10 = UDP
11 = MULTICAST
Record type
VRRP_HOST_NAME varchar (4000) Host name of the VRRP service. This value associated with the VRRP_PORT_NUMBER, define the balancing instance for the traffic data consolidation. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_PORT_NUMBER int Port number of the VRRP service. This value associated with the VRRP_HOST_NAME, define the balancing instance for the traffic data consolidation
END_POINTS_GROUPING varchar (4000) This is the name of the endpoint group
DOMAIN_REQUEST varchar (4000) EMPTY
COMMAND varchar (4000)

Identifies the flow of data

CLIENT_FLOW from client to
endpoints ENDPOINT_FLOW from endpoints to clients

URI_PATH_REQUEST varchar (4000) EMPTY
RESPONSE_CODE int 0
END_POINT_HOST_NAME varchar (4000) Name of the host on which the service request was processed
END_POINT_PORT_NUMBER int Port number of the host on which the service request was processed
END_POINT_URI_PATH varchar (4000) EMPTY
USER_ID varchar (4000) Future use
CLIENT_ADDRESS varchar (4000)

The client address reports the address of the client that requested the service.

e.g. 192.168.43.150

INCOMING_ADDRESS varchar (4000) Local address where the connection was accepted
INCOMING_PORT_NUMBER int Local port where the connection was accepted
THIS_DATE date Date standardized at 00:00:00
THIS_TIME time Time with date uniform 01-01-1970
COUNTER bigint Number of operations summed in this record (if the defaults have not been changed it is the number of operations performed in its time window in the last 10 ")
BYTES_FORWARDED bigint This is the number of bytes exchanged

The keys used are:

`` index K1_L4_DATAGRAM ON L4_DATAGRAM (THIS_DATE);

index K2_L4_DATAGRAM ON L4_DATAGRAM (THIS_TIME); ``

POOL_QUEUES_ACTIVITY table

This table contains a 10 "snap of the activity status protocol solvers. Its interpretation cannot be usable as a summation of the day's activities but as data instant statistic in the moment relative to the date and time reported on the records. With these snaps you can stake out on an axis the state of activity and therefore the utilization peaks with a slice of 10 ".

This table contains two types of snaps, one related to the state of activity of protocol solvers and the other related to the state of "committed" protocol resolvers.

RECORD_TYPE Int 4 = ACTIVITY
5 = COMMITTED
Record type
VRRP_HOST_NAME varchar (4000) Host name of the VRRP service. This value associated with the VRRP_PORT_NUMBER, define the balancing instance for the traffic data consolidation. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_PORT_NUMBER int Port number of the VRRP service. This value associated with the VRRP_HOST_NAME, define the balancing instance for the traffic data consolidation
END_POINTS_GROUPING varchar (4000) This is the name of the endpoint group
DOMAIN_REQUEST varchar (4000) This is the domain being processed on the solver
COMMAND varchar (4000) EMPTY
URI_PATH_REQUEST varchar (4000) EMPTY
RESPONSE_CODE int 0
END_POINT_HOST_NAME varchar (4000) Name of the host on which the service request was processed
END_POINT_PORT_NUMBER int Port number of the host on which the service request was processed
END_POINT_URI_PATH varchar (4000) This is the URIPath of the context being processed
USER_ID varchar (4000) Future use
CLIENT_ADDRESS varchar (4000)

The client address reports the address of the client that requested the service.

e.g. 192.168.43.150

If transmission to layer 7 HTTP / S and the management of the X-Forwarded-For entity (HTTP HEADER) has been set through the parameter xForwardedFor = "true" in the

listener in iproxy.xml the value of the entire IP chain will be transferred.

Obviously OPLON can only ensure the last element of the chain as the other elements are only useful for statistical purposes being populated by other tools

infrastructure such as proxies.

eg .: 192.168.32.115,192.168.41.10,192.168.43.150

THIS_DATE date Date standardized at 00:00:00
THIS_TIME time Time with date uniform 01-01-1970
NUMBER_OF_BUSY_CONSUMER int It is the number of active consumers at the instant THIS_DATE and THIS_TIME relative to the grouping key (in red)

The keys used are:

`` index K1_POOL_QUEUES_ACTIVITY ON POOL_QUEUES_ACTIVITY (THIS_DATE);

index K2_POOL_QUEUES_ACTIVITY ON POOL_QUEUES_ACTIVITY (THIS_TIME); ``

INCOMING_QHIGHWATER_LEVEL table

It is the table that records the filling level at a 10 "snap queue of incoming connection requests. This table is important because in relation to the number of protocol solvers it is the indicator of a possible DoS attack or insufficient resources for process the load of requests.

RECORD_TYPE Int 7 Record type
VRRP_HOST_NAME varchar (4000) Host name of the VRRP service. This value associated with the VRRP_PORT_NUMBER, define the balancing instance for the traffic data consolidation. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_PORT_NUMBER int Port number of the VRRP service. This value associated with the VRRP_HOST_NAME, define the balancing instance for the traffic data consolidation
THIS_DATE date Date standardized at 00:00:00
THIS_TIME time Time with date uniform 01-01-1970
HIGH_WATER int This is the number of connection requests in the queue before waiting to be processed
HIGH_WATER_LEVEL float

It is the calculation factor derived from:

(100 * HIGH_WATER) / ACT_SESSIONS

HIGH_WATER_WARNING_LEVEL float It is the threshold in% exceeded which a warning message is sent
HIGH_WATER_DANGER_LEVEL float It is the threshold in% exceeded which a danger message is sent
ACT_SESSIONS int It is the current availability of protocol resolvers
MAX_CONCURRENT_SESSIONS int This is the maximum number of protocol resolvers

The keys used are:

`` index K1_INCOMING_QUEUE_HL ON INCOMING_QHIGHWATER_LEVEL (THIS_DATE);

index K2_INCOMING_QUEUE_HL ON INCOMING_QHIGHWATER_LEVEL (THIS_TIME); ``

SYSLOG_EVENT table

This table collects all messages from all Oplon S.A.A.I. processes. In complex environments it is therefore a useful tool that, if centralized, can be used as a system for detect any operating anomalies.

RECORD_TYPE Int 12 Record type
VRRP_HOST_NAME varchar (4000) It is an @ separated string containing:
host name @ monitor management url @ server process name @ absolute log dir @ date YYYYMMDD @ hostname_logfileSuffix
VRRP_PORT_NUMBER int
COOKIES varchar (4000) It is an identification value of the message that is always different and unique for each system eg: ID = "16231623"
INCOMING_ADDRESS varchar (4000) Host name
THIS_DATE date Date standardized at 00:00:00
THIS_TIME time Time with date uniform 01-01-1970
REPETITIONS bigint Number of repetitions of the same message
TIME_SEQUENCE bigint Event timeline
SEVERITY varchar (4000) ERROR | WARNING | DEBUG | FATAL
JAVA_REL varchar (4000) Release java
LBL_REL varchar (4000) Relelase LBL
MESSAGE_GRP varchar (4000) Name of the processing unit that generated the message
HOST_IDvarchar (4000)Name of the host that generated the message
MESSAGEvarchar (4000)Message
MONITOR_MNG_URLvarchar (4000)
MONITOR_PROCESS_NAMEvarchar (4000)

The keys used are:

index K1_SYSLOG_EVENT ON SYSLOG_EVENT (THIS_DATE);

index K2_SYSLOG_EVENT ON SYSLOG_EVENT (THIS_TIME);

NOTE: At the start of the Monitor, the column is set with MONITOR_MNG_URL * UNDEFINED * because he still has not been started in-house theater management. Immediately after the start this column will correctly report the value of management eg.: https://192.168.46.109:54443/WebRegister

NOTE1: For the Monitor process the MONITOR_PROCESS_NAME is set with MONITOR

WAF_EXEC table

If Web Application Firewall is installed, this table collects the log of all rules successfully executed during the passage of data.

RECORD_TYPE Int 14 Record type
VRRP_HOST_NAME varchar (350) Host name of the VRRP service. This value associated with the VRRP_PORT_NUMBER, define the balancing instance for the traffic data consolidation. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_PORT_NUMBER int Port number of the VRRP service. This value associated with the VRRP_HOST_NAME, define the balancing instance for the traffic data consolidation
DOMAIN_REQUEST varchar (3500) If WebSocket is the required host at layer 7
CLIENT_ADDRESS varchar (3500)

The client address reports the address of the client that requested the service.

e.g. 192.168.43.150

THIS_DATE date Date standardized at 00:00:00
THIS_TIME time Time with date uniform 01-01-1970
COUNTER int Number of operations summed in this record (if the defaults have not been changed it is the number of operations performed in its time window in the last 10 ")
RULE_ID varchar (100) unique name of the rule
REQUEST_ID varchar (100)
ENABLE varchar (100)

Type of enabling the rule:

on, off, detect

SCORE int Weight of the rule
ACTION varchar (100)

Type of action taken:

drop, blacklist_ip ...

DESCRIPTION varchar (3500) Description of the rule
EXECUTION_QUEUE varchar (100) runtime / near time
CATEGORY varchar (100)

Rule category:

Input; Bruteforce; Probing; Info leakage; Authentication; Linked site…

The keys used are:

create index K1_WAF_EXEC ON WAF_EXEC (THIS_DATE);

create index K2_WAF_EXEC ON WAF_EXEC (THIS_TIME);

DDOS_EXEC table

If Web Application Firewall is installed, this table collects the log of all rules successfully executed during the passage of data.

RECORD_TYPE Int 15 Record type
VRRP_HOST_NAME varchar (350) Host name of the VRRP service. This value associated with the VRRP_PORT_NUMBER, define the balancing instance for the traffic data consolidation. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_PORT_NUMBER int Port number of the VRRP service. This value associated with the VRRP_HOST_NAME, define the balancing instance for the traffic data consolidation
DOMAIN_REQUEST varchar (3500) If WebSocket is the required host at layer 7
INCOMING_ADDRESS varchar (3500) Local address where the connection was accepted
CLIENT_ADDRESS varchar (3500)

The client address reports the address of the client that requested the service.

e.g. 192.168.43.150

THIS_DATE date Date standardized at 00:00:00
THIS_TIME time Time with date uniform 01-01-1970
COUNTER int Number of operations summed in this record (if the defaults have not been changed it is the number of operations performed in its time window in the last 10 ")
TUNNELS int unique name of the rule
QUARANTINE_TIME bigint Quarantine time
QUARANTINE_START_TIME datetime Quarantine start
QUARANTINE_END_TIME datetime End of quarantine
COUNTRY_CODE varchar (100) Country code of the attack
COUNTRYvarchar (100)Extended Country
SUBNETint1 if attack from a subnet; 0 if coming from single IP
DETECTint1 if detected without action (detect only); 0 if mitigation action performed

The keys used are:

create index K1_DDOS_EXEC ON DDOS_EXEC (THIS_DATE);

create index K2_DDOS_EXEC ON DDOS_EXEC (THIS_TIME);

DAILY_ALERT table

If Web Application Firewall and Attack Prophecy are installed, this table collects the daily notifications of Attack Prophecy.

THIS_DATE date Date standardized at 00:00:00
CLIENT_ADDRESS varchar (100)

The client address reports the address of the client that requested the service.

e.g. 192.168.43.150

COUNTRY_CODE varchar (100) Country code of the attack
COUNTRY_DES varchar (350) Country extended
AS_CODE varchar (100) Autonomous system code
AS_DESC varchar (350) Description of autonomous system
CATEGORY varchar (100)

Rule category:

Input; Bruteforce; Probing; Info leakage; Authentication; Linked site…

ALERT_NUM int Number of alerts

The keys used are:

create index K1_DAILY_ALERT ON DAILY_ALERT (THIS_DATE);

Tabella GUIRT_MASTER GUIRT_DETAIL

The two tables GUIRT_MASTER and GUIRT_DETAIL are populated by the module GUI Reliability Tool while running. These two tables are correlated in one to many (<->>) through the PLAY-ID join.

GUIRT_MASTER
PLAY_IDbigintUnique id of execution of the trace (Join with PLAY_ID of GUIRT_DETAIL)
HOST_IDvarchar (512)OPLON GDG (Where) node identifier
PROCESS_NAMEvarchar (512)Module name (e.g .: G10_LBLGUIPlayer)
TRACK_IDvarchar (512)Track name assigned in the player loading form
START_TIMEdatetimeTrace execution start date time
END_TIMEdatetimeEnd date time for trace execution
ERRORint0 the trace did not end with an error, 1 the trace ended with an error
GUIRT_DETAIL
PLAY_ID bigint Unique id of execution of the trace (Join with PLAY_ID of GUIRT_MASTER)
TIME_SEQ bigint This is the timeline of the step. NOTE: The steps can be executed conditionally and therefore the history of the step number can be different from the sequence.
STYPE int

Type of step:

0 = process step, 1 = text test 2 = image test, 3 = end step

ERROR int 0 the trace did not end in error, 1 the trace ended in error. NOTE: It may happen that a step detects an error but it is corrected by a conditional jump. In this case the step will have as ERROR 1 while the track will have as ERROR 0
ORIGINAL_SEQ int Trace start date time
ROW_NUM int If a replacement "csv" list has been assigned to the running track, this is the row number of the running list.
ROW_COMPUTETIME bigint Time taken by the system to perform the operation (example time to appear image or to search for a text)
ROW_TIME bigint Total gross time taken to perform the step in milliseconds. This time includes the ROW_COMPUTETIME calculation time which must be separated if you want to estimate the net waiting time
MESSAGE varchar (2000) Total message
RECORDED varchar (2000) Loaded only if error. If TEXT type test contains the registered value to compare. If image type test contains the name of the file with the recorded image for comparison.
PICKED varchar (2000)

Only loaded if error. If TEXT type test contains the detected value. If image type test contains the name of the file with the detected image.

NOTE: Upon detection of the error, 3 images are sent to the system: recorded image, detected image and desktop snap.

Selection example with Join GUIRT_MASTER and GUIRT_DETAIL to detect all the image comparison steps that have encountered an error of comparison.

SELECT  * from GUIRT_MASTER m

LEFT JOIN GUIRT_DETAIL d ON m.PLAY_ID = d.PLAY_ID

WHERE d.STYPE < >3 and m.ERROR=1 and d.ERROR=1 and STYPE=2

ORDER BY m.START_TIME, m.PLAY_ID, d.TIME_SEQ;

Le immagini vengono trasferite verso il broker che provvede a memorizzarle nella diurectory: (LBL_HOME)/ GUIRTImages/

Il nome del file è composto dal PLAY_ID, sequenza temporale, tipo di immagine: picked, recorded, desktop.

882208199_1534915217334_picked.png

882208199_1534915217334_recorded.png

882208199_1534915217380_desktop.png

Form stop

Before modifying the parameters, stop the module concerned.

Modules> Statistic brokers> Choose the module> Edit

image7

Expand the General start parameters panel> Set Module start from automatic to manual

image8

image9 Save the ed parameters wait for the module to be in Stopped state:

Upload database connection libraries

To load the database libraries run:

Files> External libraries> Import> Choose the form> Browse> Confirm

image10

Upon confirmation, the library will be loaded into the selected node. Self more library files needed repeat the operation for all files.

Associating the library with the module

To associate the database library with the module that connects to the database execute:

Modules> Statistic brokers> Choose the module> Edit

image7

Choose the affected Operating System panel and change the class path with the database library (s). In this example with the MySQL library.

image11

Connection to the DB

To set the DB connection parameters, depending on the type of DB execute:

image7 Modules> Statistic brokers> Choose the module> Edit

image12 Expand the Webcache basic panel parameter and set the parameters according to the DB used.

The list of parameters depending on the DB is shown below.

DERBY EMBEDDED(es. library: derby.jar derbyclient.jar derbytools.jar)

DBDriver= "org.apache.derby.jdbc.EmbeddedDriver "
DBProtocol= "jdbc:derby: "
DBName= "lib/LBLDBStatistics "
DBDateFormat= "yyyy-MM-dd "
DBTimeFormat= "HH:mm:ss "

DERBY NETWORKED (es. library: derby.jar derbyclient.jar derbytools.jar)

DBDriver="org.apache.derby.jdbc.ClientDriver"
DBProtocol="jdbc:derby:"
DBName="//\_\_\_hostname\_\_\_:\_\_\_portnumber\_\_\_/\_\_\_\_directoryDBName\_\_\_"
DBDateFormat="yyyy-MM-dd"
DBTimeFormat="HH:mm:ss"

MySQL (es. library: mysql-connector-java-5.1.10-bin.jar)

DBDriver="com.mysql.jdbc.Driver"
DBProtocol="jdbc:mysql:"
DBName="//\_\_\_hostname\_\_\_:\_\_\_portnumber\_\_\_/\_\_\_dbname\_\_\_"
DBLogin="root"
DBPassword="adminadmin"
DBDateFormat="yyyy-MM-dd 00:00:00"
DBTimeFormat="1970-01-01 HH:mm:ss"
DBTimeIsDate="true"
DBVarcharLimit="3500"

SQLSERVER (es. library: sqljdbc.jar)

DBDriver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
DBProtocol="jdbc:sqlserver:"
DBName="//\_\_\_hostname\_\_\_:\_\_\_portnumber\_\_\_;Database=\_\_\_dbname\_\_\_"
DBLogin="sa"
DBPassword="adminadmin"
DBDateFormat="yyyy-dd-MM 00:00:00"
DBTimeFormat="1970-01-01 HH:mm:ss"

ORACLE (es. library: ojdbc6)

DBDriver="oracle.jdbc.driver.OracleDriver"
DBProtocol="jdbc:oracle:"
DBName="thin:@\_\_\_hostname\_\_\_:\_\_\_portnumber\_\_\_:\_\_\_dbname\_\_\_"
DBLogin="system"
DBPassword="adminadmin"
DBDateFormat="yyyy-MM-dd 00:00:00"
DBTimeFormat="1970-01-01 HH:mm:ss"
DBSetDateFormat="ALTER SESSION set NLS_DATE_FORMAT = &apos;YYYY-MM-DD HH24:MI:SS&apos;"

POSTGRES (es. library: postgresql-8.4-701.jdbc4.jar)

DBDriver="org.postgresql.Driver"
DBProtocol="jdbc:postgresql:"
DBName="//\_\_\_hostname\_\_\_:\_\_\_portnumber\_\_\_/\_\_\_dbname\_\_\_"
DBLogin="postgres"
DBPassword="adminadmin"
DBDateFormat="yyyy-MM-dd"
DBTimeFormat="HH:mm:ss"

Modify historicization time depth

Oplon Application Delivery Controller for default parameterization historicizes the data with a depth 2-day storm. To change this behavior is enough set the DBMaxHistoryDays parameter. If this parameter comes set with a value less than or equal to 0 (zero) the statistics do not will never be deleted. Depth can also be set different time frames for the different database tables.

image13

Checking the parameters

After executing the save try to start the module manually

Modules >Statistic brokers >Choose the module >Start

image14

Modules >Statistic brokers >Choose the module >See details

image15

image16 Modules >Statistic brokers >Choose the module >Actions >View logs

image17 Modules >Statistic brokers >Choose the module >Actions >View logs >View log

image18 Search in the logs "DB Statistics initialization done!"

Ripristinare l'esecuzione automatica ...

Modules >Statistic brokers >Choose the module >Edit

image7

Expand the General start parameters> Set Module start from panel manual to automatic

image19

image8

image19

Save the parameters and wait for the module to be in the Running state

ORACLE RAC connection string

For Oracle RAC the statistical database connection string must be set so that it can reach listeners on different instances. In this regard, below is an example of a connection string with listeners attested on two hosts respectively: db1 and db2.

**DBName**= "thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST= <db1>)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST= <db2>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME= <nome_servizio >))) "