Statistics Networked Database setup

Back

LBL Application Delivery Controller is a product destined for mission-critical environments therefore only staff who made the course and has passed the examination is authorized to certify the installation and maintenance of products in operation. All Certified People are equipped with temporary license issued by us.

LBL Application Delivery Controller manages the persistence of statistics on Database Report so transactional. In the default configuration uses the engine our JavaDB Embedded provided directly in the distribution (Java JDK). In addition to our JavaDB Embedded LBL Application Delivery Controller is designed to use the most popular relational database transactional. Below is the complete list of supported databases for the historicizing statistics:

  1. Oracle
  2. MS SQLServer
  3. MySQL (InnoDB engine)
  4. Postgres
  5. Our JavaDB embedded (default)
  6. Our JavaDB Networked

The persistence module of statistics has been realized in order to be uncoupled from the balancing system. This feature allows the realization of different architectures based on the needs of historicizing.

In the default installation of these features are not immediately obvious being deliberately been realized in such a way as to create minimal impact on the basic installation.

Types of historicizing statistics

There are basically 2 types of historicizing statistics:

  1. Embedded
  2. Networked

For¬†Embedded¬†is meant the exclusive use of the database of¬†LBL¬ģApplication Delivery Controller¬†without the possibility to interact during the¬†run-time with questions custom SQL. (Note: It is possible to change the mode from embedded¬†to¬†networked¬†at any time without loss of information)

For Networked is meant the use of a DBMS among those supported through the network with the use of the driver (JDBC) provided by the individual manufacturers. With this type of historicizing is possible to interrogate the database with its own  SQL query during run-time.

Architecture

The architecture of the statistical historicizing was designed to have minimal impact on processing core (Load Balancing), giving at the same time a maximum configuration flexibility and high scalability.

It is based on 3 logic levels

These three logic levels from a point of view of “Process” operating system can be collapsed in embedded mode on 2 “Process”. In the mode Networked¬†instead will be at minimum of 3 “Process” distinct.

Process of mode Operating System Embedded

Since these services can be reached through the network possible architectures are many.

The preset architecture has a very low impact both in the installation both in maintenance and is well suited for those realities that need only have some spontaneous data to verify operability and who do not have a need for further elaboration of statistics or consolidated.

With the Embedded Architecture statistics remain within the individual instances and are managed automatically by the system the Ombudsman (LBL Application Delivery Controller WebCacheBroker).

In the preset parameters the process LBL Application Delivery WebCacheBroker Controller maintains the data in the embedded database for 2 days by checking at the same time the physical occupations in mass storage support. In the event these dimensions exceed the GB (1GB) will automatically to the regeneration of the DB avoiding depletion of resources. Both the limit of 2 days that the limit of 1GB are editable by parameters (see LBL S.A.A.I. Reference Guide).

Obviously being of separate processes LBL Application Delivery Controller (the manufacturer of statistics) and LBL WebCacheBroker (the ombudsman, brokers) and connected through the network would still be possible to relocate the historicizing on third machines, both in the same DMZ is in the backend. This possibility is certainly better exploited in the mode of use of a Database Networked where it will be possible to perform during the run-time of the SQL query on data traffic.

In the image below a configuration on 3 levels completely separated between the production of statistics, mediation (broker) and the historicizing on DBMS.

It is possible to reach this result for two distinct motivations, the first is derived from the architectural design that is born on three distinct levels, the second is derived from the logical separation of data within the same DBMS. With LBL Application Delivery Standard Controller has and  LBL Application Delivery Controller Enterprise has it is in fact possible to consolidate within the same DB and in the same tables data from multiple instances LBL Application Delivery Controller.

The structure of the database and creation of tables

The persistence of traffic data in the statistical database is performed through the following tables:

  • SESSION_ACTIVITY
    • Analysis¬†of the use of the Sessions
  • L7_HTTP_HTTPS
    • Persistence¬†of information of HTTP and HTTPS traffic.
  • L4_TCP_TCPSSL
    • Persistence¬†of information of TCP traffic and TCP as SSL terminator
  • L4_Datagram
    • Persistence¬†of information of UDP traffic and multicast
  • POOL_QUEUES_ACTIVITY
    • Analysis¬†of the use of the pool of resolution of service requests
  • INCOMING_QHIGHWATER_LEVEL
    • Analysis¬†of the use of filling of the queue of incoming connections
  • WAF_EXEC
    • Log¬†rules¬†carried out with positive outcome¬†from the Web Application¬†Firewal
  • DDOS_EXEC
    • Log¬†Attacks¬†DoS¬†and¬†DDoS
  • DAILY_ALERT
    • Log¬†of notifications from Attack¬†Prophecy
  • GUIRT_MASTER
    • Log master of the Traces running GUI Reliability Tool
  • GUIRT_Detail
    • Log detail in the execution of the step¬†of the TRACES GUI Reliability¬†Tool

The diagrams of tables for single database supported are contained in the directory (LBL_HOME)/legacyBin/DatabasesScript.

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
  • Our JavaDB_LBLDBTables
    • Java DB derby

Apart from a few differences relating to the typification of the size of the fields, tables , on various databases contain the same data. In the following pages will be described in detail for each field the meanings of the structures to be able to easily create reports, statistics, billing systems on the basis of the traffic.

ATTENZIONELa creation of database and tables our JavaDB

 (aka IBM Cloudscape, Apache Derby) occurs in a completely automatic manner both for Embedded solution is networked. For all other database refer to scripts to create tables that are available for each separate databases in the directory (LBL_HOME)/legacyBin/DatabasesScript.

Table SESSION_ACTIVITY

The table containing the¬†snap¬†at intervals of 10″ of the status of the sessions of routing.

RECORD_TYPE Int 6 Record Type
VRRP_host_name Varchar(4000) Name of the host running daemons VRRP. This value associated to VRRP_port_number, define the instance of balance for the consolidation of data traffic. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_port_number Int Port number of the service VRRP. This value associated to VRRP_HOST_NAME, define the instance of balance for the consolidation of traffic data
END_POINTS_grouping Varchar(4000) It is the name of the group of endpoints
DOMAIN_REQUEST Varchar(4000) It 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)  The host name of the endpoint is associated to 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) It is the URIPath of context in processing
USER_ID Varchar(4000) Future Use
CLIENT_ADDRESS Varchar(4000) The client address returns the address of the client that has requested the service.

Es.: 192.168.43.150

If a transmission at¬†layer¬†7 HTTP/S and¬†it¬†has been set to the management of the entity¬†X-Forwarded-For (HTTP headers) through parameter xForwardedFor=”true” in

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

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

Of infrastructure such as the proxy.

Ex: 192.168.32.115,192.168.41.10,192.168.43.150

THIS_dates Dates Date uniformed to time at 00:00
THIS_TIME Time Now with date standardised on 01-01-1970
NUMBER_OF_ACTIVE_SESSIONS Int It is the number of sessions relating to the grouping key (red)

The keys used are:

Index K1_SESSION_ACTIVITY ON SESSION_ACTIVITY (THIS_DATE);

Index K2_SESSION_ACTIVITY ON SESSION_ACTIVITY (THIS_TIME);

Table L7_HTTP_HTTPS

This table contains the traffic of HTTP and HTTPS (SSL).

RECORD_TYPE Int 0=HTTP

1=HTTPS

Record Type
VRRP_host_name Varchar(4000) Name of the host running daemons VRRP. This value associated to VRRP_port_number, define the instance of balance for the consolidation of data traffic. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_port_number Int Port number of the service VRRP. This value associated to VRRP_HOST_NAME, define the instance of balance for the consolidation of traffic data
END_POINTS_grouping Varchar(4000) It is the name of the group of endpoints
DOMAIN_REQUEST Varchar(4000) It 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 related to the domain requested by the client. This value shows the result of the request after the action of rewriting if it exists. It is the actual demand that is carried out in the backend.
URI_PATH_REQUEST_ORG Varchar(4000)  The original path referred to the requested domain from 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 HTTP CODE sent from the server to the client in response to its request
END_POINT_host_name Varchar(4000) Name of the host on which it was drawn up at the request of service.
END_POINT_port_number Int Port number of the host on which it was drawn up at the request of service.
END_POINT_URI_PATH Varchar(4000) It is the URIPath of context on which has been processed the request
USER_ID Varchar(4000) The column contains the information of the user profile and authenticated. The information is a sum of the characteristic elements of a basic authentication (Basic Authentication) and authorization deriving from digital certificate. This column contains respectively:

If authorisation with digital certificate the subject with an additional value showing the serial number of the certificate es.:

“CN=clientname, OU=clientlob, O=clientcompany, L=clientcountry, ST=clientdistrict, C=en, SERIAL=1282479557”

If basic authentication

“BASIC=usr1”

In the case there are present both the credentials the resulting value will be :

“BASIC=usr1, CN=clientname, OU=clientlob, O=clientcompany, L=clientcountry, ST=clientdistrict, C=en, SERIAL=1282479557”

CLIENT_ADDRESS Varchar(4000) The client address returns the address of the client that has requested the service.

Es.: 192.168.43.150

If a transmission at¬†layer¬†7 HTTP/S and¬†it¬†has been set to the management of the entity¬†X-Forwarded-For (HTTP headers) through parameter xForwardedFor=”true” in

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

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

Of infrastructure such as the proxy.

Ex: 192.168.32.115,192.168.41.10,192.168.43.150

USER_AGENT Varchar(4000) Value user-agent in the HTTP header
COOKIES Varchar(4000) Cookie value in the HTTP header
REFERER Varchar(4000)  The Referer value in HTTP Header
URI_PARAMETERS Varchar(4000) Value with parameters and/or query string.

The parameters defined in the URL from the ; character and the query string defined daò character ? Are normalized in a single value separated by &. e.g.:

/Aaa/bbb;c1=aaa&C2=bbb?Q1=ccc&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 in which has been accepted the connection
INCOMING_port_number Int Local port in which has been accepted the connection
THIS_dates Dates Date uniformed to time at 00:00
THIS_TIME Time Now with date standardised on 01-01-1970
COUNTER Bigint Number of operations summed in this record (if you have changed the default is the number of operations carried out in its temporal window in recent 10″)
RESPONSE_TIME Bigint It is the¬†response¬†time of the requested service (this value is related to the traffic of the last 10″. To have an average value it is necessary to divide by the counter). Value expressed in nanoseconds.
LAP_time_to Bigint The time between the end of the reading of the header of the client and the beginning of the connection toward the endpoint¬†(this value is related to the traffic of the last 10″. To have an average value it is necessary to divide by the counter). Value expressed in nanoseconds.
LAP_TIME_B Bigint The time of connection to the endpoint¬†(this value is related to the traffic of the last 10″. To have an average value it is necessary to divide by the counter). Value expressed in nanoseconds.
LAP_TIME_C Bigint The time between the connection occurred at the endpoint¬†and the beginning of the reading of the header of the response. If the client sends the body is the time of transmission of the body from the client¬†to the endpoint.(This value is related to the traffic of the last 10″. To have an average value it is necessary to divide by the counter) .¬†value expressed in nanoseconds.
LAP_TIME_D Bigint Read time of the header¬†of the endpoint¬†(this value is related to the traffic of the last 10″. To have an average value it is necessary to divide by the counter). Value expressed in nanoseconds.
LAP_time_and Bigint The time between the end of the reading of the header¬†and the end of the data in response (this value is related to the traffic of the last 10″. To have an average value it is necessary to divide by the counter). Value expressed in nanoseconds.
HEADER_LENGTH_FROM_CLIENT Bigint It is the length of the header of the request of the client to the¬†endpoints¬†(this value is related to the traffic of the last 10″. To have an average value it is necessary to divide by the counter)
BYTES_SENT_FROM_CLIENT Bigint Total Transferred including the headers from the client to the¬†endpoints¬†(this value is related to the traffic of the last 10″. To have an average value it is necessary to divide by the counter)
HEADER_LENGTH_FROM_END_POINT Bigint It is the length of the header of the response of the¬†endpoint¬†toward the client (this value is related to the traffic of the last 10″. To have an average value it is necessary to divide by the counter)
BYTES_SENT_FROM_END_POINT Bigint Total Transferred including the header from¬†endpoints¬†toward the client (this value is related to the traffic of the last 10″. To have an average value it is necessary to divide by the counter) relating to the grouping key (red)
SESSIONID Varchar(100) It is a unique identifier (hash) generated by the set address of the client + referer
REFERRING_DOMAIN Varchar(300) Host name from which you sent the request
BROWSER Varchar(100) The name of the browser or client that has made the request
BROWSER_REL Varchar(100) Release of the browser or client that has made the request
OS Varchar(100) Operating system from which a request has been received
OS_REL Varchar(100) Release of the operating system by which a request has been received
DEVICE Varchar(100) The manufacturer of the device from which a request has been received
COUNTRY_CODE Varchar(100) Distinguishing sign of the State from which a request has been received
COUNTRY_DES Varchar(350) Description of the State from which a request has been received
INTERNET_ADDRESS Varchar(350) Is the client address derived from the socket if it does not exist xff. If there is XFF is the second of the chain xff.

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 traffic data relating to the activities of the connector TCP and TCP as SSL terminator. The initial structure is very similar to the HTTP/S to differentiate then on traffic data

RECORD_TYPE Int 2=TCP

3=SSL

Record Type
VRRP_host_name Varchar(4000) Name of the host running daemons VRRP. This value associated to VRRP_port_number, define the instance of balance for the consolidation of data traffic. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_port_number Int Port number of the service VRRP. This value associated to VRRP_HOST_NAME, define the instance of balance for the consolidation of traffic data
END_POINTS_grouping Varchar(4000) It is the name of the group of endpoints
DOMAIN_REQUEST Varchar(4000) If WebSocket is the host requested at layer 7
COMMAND Varchar(4000) Identifies the data stream :

CLIENT_flow from the client to the endpoints

ENDPOINT_FLOW FROM endpoints to the client

WebSocket:

WSCLIENT_flow from the client to the endpoints

WSENDPOINT_FLOW FROM endpoints to the client

URI_PATH_REQUEST Varchar(4000) If WebSocket is the URIPath required at layer 7
RESPONSE_CODE Int 0
END_POINT_host_name Varchar(4000) Name of the host on which it was drawn up at the request of service.
END_POINT_port_number Int Port number of the host on which it was drawn up at the request of service.
END_POINT_URI_PATH Varchar(4000) If WebSocket is the URIPath required 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=en, SERIAL=1282479557”

CLIENT_ADDRESS Varchar(4000) The client address returns the address of the client that has requested the service.

Es.: 192.168.43.150

COOKIES Varchar(4000) Unique value associated with the connection. This value is populated with the value LBLCOLOR to identify the single connection to the¬†layer¬†4. For¬†the activation of the populating of this value it is necessary to set the parameter¬†distinguishSingleConnection= “true” in paragraph¬†<bind>¬†file iproxy parameters.xml

If WebSocket contains cookies of the connection  layer 7 HTTP/S

INCOMING_ADDRESS Varchar(4000) Local address in which has been accepted the connection
INCOMING_port_number Int Local port in which has been accepted the connection
THIS_dates Dates Date uniformed to time at 00:00
THIS_TIME Time Now with date standardised on 01-01-1970
COUNTER Bigint Number of operations summed in this record (if you have changed the default is the number of operations carried out in its temporal window in recent 10″)
BYTES_FORWARDED Bigint It is the number of bytes exchanged in accordance with the flow (client>endpoint or endpoint>client) relating to the grouping key (red)
START_ADV_TIME Bigint It is the moment in which it is detected the first buffer/character expressed in nanoseconds
END_ADV_TIME Bigint It is the moment in which it runs the flush of the last buffer of the stream expressed in nanoseconds
TOTAL_ADV_TIME Bigint It is the total time of the forwarding of information (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 traffic data relating to the activities of the connector TCP and TCP as SSL terminator. The initial structure is very similar to the HTTP/S to differentiate then on traffic data

RECORD_TYPE Int 10=UDP

11=MULTICAST

Record Type
VRRP_host_name Varchar(4000) Name of the host running daemons VRRP. This value associated to VRRP_port_number, define the instance of balance for the consolidation of data traffic. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_port_number Int Port number of the service VRRP. This value associated to VRRP_HOST_NAME, define the instance of balance for the consolidation of traffic data
END_POINTS_grouping Varchar(4000) It is the name of the group of endpoints
DOMAIN_REQUEST Varchar(4000) EMPTY
COMMAND Varchar(4000) Identifies the data flow

CLIENT_flow from the client to the endpoints

ENDPOINT_FLOW FROM endpoints to the client

URI_PATH_REQUEST Varchar(4000) EMPTY
RESPONSE_CODE Int 0
END_POINT_host_name Varchar(4000) Name of the host on which it was drawn up at the request of service.
END_POINT_port_number Int Port number of the host on which it was drawn up at the request of service.
END_POINT_URI_PATH Varchar(4000) EMPTY
USER_ID Varchar(4000) Future Use
CLIENT_ADDRESS Varchar(4000) The client address returns the address of the client that has requested the service.

Es.: 192.168.43.150

INCOMING_ADDRESS Varchar(4000) Local address in which has been accepted the connection
INCOMING_port_number Int Local port in which has been accepted the connection
THIS_dates Dates Date uniformed to time at 00:00
THIS_TIME Time Now with date standardised on 01-01-1970
COUNTER Bigint Number of operations summed in this record (if you have changed the default is the number of operations carried out in its¬†temporal window in recent 10″)
BYTES_FORWARDED Bigint It 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);

Table POOL_QUEUES_ACTIVITY

This table contains a¬†snap¬†of 10″ activity status of solvers of protocol. Its interpretation cannot be usable as the sum of the activities of the day but as a statistic instant in time relative to the date and time shown on the¬†records. With these snap¬†you can draw on a time axis activity status and then peaks of use with one¬†slice¬†of 10″.

This table contains two types of¬†snap, one concerning the status of activity of solvers protocol and the other concerning the status of “committed” of solvers of protocol.

RECORD_TYPE Int 4=ACTIVITY

5=COMMITTED

Record Type
VRRP_host_name Varchar(4000) Name of the host running daemons VRRP. This value associated to VRRP_port_number, define the instance of balance for the consolidation of data traffic. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_port_number Int Port number of the service VRRP. This value associated to VRRP_HOST_NAME, define the instance of balance for the consolidation of traffic data
END_POINTS_grouping Varchar(4000) It is the name of the group of endpoints
DOMAIN_REQUEST Varchar(4000) It is the domain in processing on the resolver
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 it was drawn up at the request of service.
END_POINT_port_number Int Port number of the host on which it was drawn up at the request of service.
END_POINT_URI_PATH Varchar(4000) It is the URIPath of context in processing
USER_ID Varchar(4000) Future Use
CLIENT_ADDRESS Varchar(4000) The client address returns the address of the client that has requested the service.

Es.: 192.168.43.150

If a transmission at¬†layer¬†7 HTTP/S and¬†it¬†has been set to the management of the entity¬†X-Forwarded-For (HTTP headers) through parameter xForwardedFor=”true” in

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

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

Of infrastructure such as the proxy.

Ex: 192.168.32.115,192.168.41.10,192.168.43.150

THIS_dates Dates Date uniformed to time at 00:00
THIS_TIME Time Now with date standardised on 01-01-1970
NUMBER_OF_Busy_CONSUMER Int It is the number of consumers in activities in the instant THIS_DATES AND THIS_TIME relating to the grouping key (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);

Table INCOMING_QHIGHWATER_LEVEL

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

RECORD_TYPE Int 7 Record Type
VRRP_host_name Varchar(4000) Name of the host running daemons VRRP. This value associated to VRRP_port_number, define the instance of balance for the consolidation of data traffic. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_port_number Int Port number of the service VRRP. This value associated to VRRP_HOST_NAME, define the instance of balance for the consolidation of traffic data
THIS_dates Dates Date uniformed to time at 00:00
THIS_TIME Time Now with date standardised on 01-01-1970
HIGH_WATER Int It is the number of requests for connection to the inside of the tail before waiting to be processed
HIGH_water_level Float It is the factor of calculation derived from:

(100*HIGH_WATER)/ACT_SESSIONS

HIGH_water_warning_LEVEL Float It is the threshold in % exceeded which is sent a warning message
HIGH_water_danger_LEVEL Float It is the threshold in % exceeded which is sent a message of danger
ACT_SESSIONS Int It is the current availability of solvers protocol
MAX_CONCURRENT_SESSIONS Int It is the maximum number of solvers protocol

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);

Table SYSLOG_EVENT

This table collects all messages from all processes¬†LBL¬ģS.A.A.I..¬†In complex environments is thus a useful instrument which, if centralized, can be used as a system to detect any abnormalities in the functioning.

RECORD_TYPE Int 12 Record Type
VRRP_host_name Varchar(4000) It is a separate string from @ 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¬†a value¬†identifying the message always different and unique for the ES system.: ID=”16231623″
INCOMING_ADDRESS Varchar(4000) Host name
THIS_dates Dates Date uniformed to time at 00:00
THIS_TIME Time Now with date standardised on 01-01-1970
REPETITIONS Bigint The number of repetitions of the same message
TIME_SEQUENCE Bigint Timeline event
SEVERITY Varchar(4000) ERROR | WARNING | Debugging | FATAL
JAVA_REL Varchar(4000) Release java
LBL_REL Varchar(4000) Relelase LBL
MESSAGE_GRP Varchar(4000) The name of the processing unit that has generated the message
HOST_ID Varchar(4000) Name of the host that has generated the message
MESSAGE Varchar(4000) Message
MONITOR_MNG_URL Varchar(4000)
MONITOR_PROCESS_NAME Varchar(4000)

The keys used are:

Index K1_SYSLOG_EVENT ON SYSLOG_EVENT (THIS_DATE);

Index K2_SYSLOG_EVENT ON SYSLOG_EVENT (THIS_TIME);

Notes: 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**

Table WAF_EXEC

If the Web Application Firewall installed, this table collects the log of all rules performed with positive results during the passage of the data.

RECORD_TYPE Int 14 Record Type
VRRP_host_name Varchar(350) Name of the host running daemons VRRP. This value associated to VRRP_port_number, define the instance of balance for the consolidation of data traffic. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_port_number Int Port number of the service VRRP. This value associated to VRRP_HOST_NAME, define the instance of balance for the consolidation of traffic data
DOMAIN_REQUEST Varchar(3500) If WebSocket is the host requested at layer 7
CLIENT_ADDRESS Varchar(3500) The client address returns the address of the client that has requested the service.

Es.: 192.168.43.150

THIS_dates Dates Date uniformed to time at 00:00
THIS_TIME Time Now with date standardised on 01-01-1970
COUNTER Int Number of operations summed in this record (if you have changed the default is the number of operations carried out in its temporal window in recent 10″)
RULE_ID Varchar(100)  Unique name of the rule
REQUEST_ID Varchar(100)
ENABLE Varchar(100) Type of enabling rule:

On, Off, detect

SCORE Int The weight of the rule
ACTION Varchar(100) Type of action:

Drop, blacklist_ip¬†…

DESCRIPTION Varchar(3500) Rule Description
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 table_EXEC

If the Web Application Firewall installed, this table collects the log of all rules performed with positive results during the passage of the data.

RECORD_TYPE Int 15 Record Type
VRRP_host_name Varchar(350) Name of the host running daemons VRRP. This value associated to VRRP_port_number, define the instance of balance for the consolidation of data traffic. If Platform Edition see Reference Guide uniqueContextID parameter in iproxy.xml
VRRP_port_number Int Port number of the service VRRP. This value associated to VRRP_HOST_NAME, define the instance of balance for the consolidation of traffic data
DOMAIN_REQUEST Varchar(3500) If WebSocket is the host requested at layer 7
INCOMING_ADDRESS Varchar(3500) Local address in which has been accepted the connection
CLIENT_ADDRESS Varchar(3500) The client address returns the address of the client that has requested the service.

Es.: 192.168.43.150

THIS_dates Dates Date uniformed to time at 00:00
THIS_TIME Time Now with date standardised on 01-01-1970
COUNTER Int Number of operations summed in this record (if you have changed the default is the number of operations carried out in its temporal window in recent 10″)
TUNNELS Int  Unique name of the rule
QUARANTINE_TIME Bigint Time of quarantine
QUARANTINE_START_TIME Datetime Start Quarantine
QUARANTINE_END_TIME Datetime End quarantine
COUNTRY_CODE Varchar(100) Country code of the attack
COUNTRY Varchar(100) Country extended
SUBNET Int 1 If attack from a subnet; 0 if coming from single IP
DETECT Int 1 if detected without action (Detect Only); 0 if performed action of mitigation

The keys used are:

Create index K1_DDOS_EXEC ON DDOS_EXEC (THIS_DATE);

Create index K2_DDOS_EXEC ON DDOS_EXEC (THIS_TIME);

Table daily_ALERT

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

THIS_dates Dates Date uniformed to time at 00:00
CLIENT_ADDRESS Varchar(100) The client address returns the address of the client that has requested the service.

Es.: 192.168.43.150

COUNTRY_CODE Varchar(100) Country code of the attack
COUNTRY_DES Varchar(350) Country extended
AS_CODE Varchar(100) Code autonomous system
AS_DESC Varchar(350) Description 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);

Table GUIRT_MASTER GUIRT_DETAIL<->>

The two tables GUIRT_MASTER AND GUIRT_detail are populated by the module GUI Reliability Tool during execution. These two tables are related in one-to-many () through the join PLAY-ID.<->>

GUIRT_MASTER
PLAY_ID Bigint The unique id of execution of the trace (Join with PLAY_ID of GUIRT_DETAIL)
HOST_ID Varchar(512) Identifying the node LBL GDG (Where)
PROCESS_NAME Varchar(512) Name of the form (es.: G10_LBLGUIPlayer)
TRACK_ID Varchar(512) Track name assigned in the form of loading the player
START_TIME Datetime Date Execution start time of the track
END_TIME Datetime Date run end time of the track
ERROR Int 0 The track is not finished with error, 1 the track is finished with error
GUIRT_Detail
PLAY_ID Bigint The unique id of execution of the trace (Join with PLAY_ID of GUIRT_MASTER)
TIME_SEQ Bigint Is the temporal sequence of step. Note: The step may be executed conditionally and then the history of the number of the step can be different from the sequence.
STYPE Int Type of steps:

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

ERROR Int 0 The track is not finished with error, 1 the track is finished with error. Note: It may happen that one step detects an error which however 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 Date Execution start time of the track
ROW_num Int Date run end time of the track
ROW_COMPUTETIME Bigint The time used by the system to perform the operation (example time appears image or text search)
ROW_TIME Bigint  Total Time Gross used to perform step in milliseconds. This time includes the time for calculating  ROW_COMPUTETIME that must be unwrapped if you wants to estimate the net time waiting
MESSAGE Varchar(2000) Total message
RECORDED Varchar(2000) Only loaded if error. If test type text contains the value recorded by comparing. If test type image contains the name of the file with the recorded image for comparison.
PICKED Varchar(2000) Only loaded if error. If test type text contains the value detected. If test type image contains the name of the file with the image detected.

Note: The error detection, are sent to the system 3 images: recorded image, image detected and snap the desktop.

Example of selection with Join GUIRT_MASTER AND GUIRT_detail to detect all the steps of comparing image that have found a comparison error.

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;

The images are transferred to the broker that provides to store them in the diurectory: (LBL_HOME) GUIRTImages/

The file name consists of the PLAY_ID, timeline, image type: picked, recorded, desktop.

882208199_1534915217334_picked.png

882208199_1534915217334_recorded.png

882208199 1534915217380_Desktop_.png

ATTENZIONEGUIRT requires activation of Monetizer traffic

 with the use of the interceptor for handling data in real time.The module is released for the following database: MySQL, Oracle, MS SQL Server.

Stop of the module

Before changing the parameters perform the stop of the module concerned.

Modules>Statistic brokers>Choose module>Edit

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


Run the save parameters and wait for that module is in a state of stopped:

Upload libraries of database connection

To load the libraries of the database execute:

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

To confirm the library will be loaded in the selected node. If you need more library files repeat the operation for all files.

Association of the library to the module

To associate the database library to the module that connects to the database to perform:

Modules>Statistic brokers>Choose the module >Edit

Choose the panel of the affected operating system and change the class path with the/and database library. In this example with the MySQL library.

Connection to the DB

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


Modules>Statistic brokers>Choose the module>Edit


Expand the panel Webcache basic parameter and set parameters in dependence to the DB used.

The list of parameters in dependence of the DB is given 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 (e.g. 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 (e.g. 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 (e.g. library: sqljdbc.jar)
=====================================================================
DBDriver=”com.microsoft.sqlserver.jdbc.SQLServerDriver”
DBProtocol=”jdbc:sqlserver:”
DBName=”//___hostname______portnumber___;Database=___dbname___”
DBLogin=”HS”
DBPassword=”adminadmin”
DBDateFormat=”yyyy-dd-MM 00:00:00″
DBTimeFormat=”1970-01-01 HH:mm:ss”
ORACLE (e.g. 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:I:SS&apos;”
POSTGRES (e.g. library: postgresql-8.4-701.jdbc4.jar)
=====================================================================
DBDriver=”org.postgresql.Driver”
DBProtocol=”dbc:postgresql:”
DBName=”//___hostname______portnumber___/___dbname___”
DBLogin=”postgres”
DBPassword=”adminadmin”
DBDateFormat=”yyyy-MM-dd”
DBTimeFormat=”HH:mm:ss”

Change Depth temporal historicizing


LBL¬ģApplication Delivery Controller¬†for parameterization historicizes default data with a temporal depth of 2 days. To change this behavior is sufficient to set the parameter¬†DBMaxHistoryDays. If this parameter is set to a value less than or equal to 0 (zero) statistics will never be erased. You¬†can also set the temporal depth diversified for the different tables in the database.

Verification of the parameters

After the save try to start the module manually

Modules>Statistic brokers>Choose the module>Start

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


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


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


Look in the log¬†“DB¬†Statistics initialization done!”

Restore the autorun…

Modules>Statistic brokers>Choose the module>Edit

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




Run the save parameters and wait for that module is in a state of running

ORACLE RAC: connection string

For Oracle RAC the connection string to the statistical database must be set so that it can reach the listeners on several instances. In this regard the following is an example of a connection string with listeners certificates 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_DATE=(SERVICE_NAME=<name_service>)))”