Move changelog Data to Enterprise Database with a Cron Job

Description

LogicNets uses the changelog_ctx table to store system data that includes user information, session details, package information, actions taken my users, and the dates on which actions happened. This database houses system meta data, including what changed. This is the data table LogicNets use for reporting, and for that, it's good to put it in a centrally accessed location. 

For most applications, LogicNets stores the changelog_ctx as part of the SQLite data tables that are part of each application. However, to provide reporting that is faster or combines data from different servers, you can create a cron job that moves the data stored in the changelog from the SQLite database format to the MySQL enterprise database format. 

This topic contains the technical information and actions necessary to set up the MySQL database as well as the connection to allow the cron job to run.

Create the Schema

To use the cron job that moves the data in the changelog_ctx to the MySQL enterprise database, you must first create the schema. This involves creating the database and then running the table-create statement to create the required table. Once that is done, you create the required views of the data found in the changelog_ctx table for the reporting tool. 

Automatically Create the Schema

For convenience, LogicNets created a batch file you can use to have the system automatically perform the steps necessary to create the schema. To use the batch file, do the following:

1. Navigate to the LogicNets installation.
2. Navigate to "system/scripts/Remote databases".
3. Use "create_changelog_schema.bat" to set up the changelog schema.

After following the steps outlined in the batch file, go to Update Configuration Settings below, to ensure the communication between the schema and LogicNets is established.

Manually Create the Database

Create the MySQL database to house the tables and corresponding views using the following statement:

CREATE DATABASE `changelog` /*!40100 DEFAULT CHARACTER SET latin1 */;

Manually Create the changelog_ctx Table

Create the changelog_ctx table using the following table-create statement:

1   CREATE TABLE `changelog_ctx` (
2  `id` int(11) NOT NULL AUTO_INCREMENT,
3  `db_stamp` datetime DEFAULT NULL,
4  `user_stamp` datetime DEFAULT NULL,
5  `key` varchar(45) DEFAULT NULL,
6  `value` longtext,
7  `action` varchar(45) DEFAULT NULL,
8  `application_key` varchar(45) DEFAULT NULL,
9  `metadata` longtext,
10  `session_data` longtext,
11  `device` varchar(45) DEFAULT NULL,
12  `user` longtext,
13  `package` varchar(45) DEFAULT NULL,
14  `net` varchar(45) DEFAULT NULL,
15  `node` varchar(45) DEFAULT NULL,
16  PRIMARY KEY (`id`)
17) ENGINE=InnoDB AUTO_INCREMENT=308 DEFAULT CHARSET=latin1

Manually Create the reporting_changelog_ctx View

Create the reporting_changelog_ctx view using the following create statement: 

1CREATE VIEW REPORTING_CHANGELOG_CTX AS
2    SELECT
3        ID AS S_ID,
4        DB_STAMP AS S_DB_STAMP,
5        USER_STAMP AS S_USER_STAMP,
6        `KEY` AS S_KEY,
7        VALUE AS S_VALUE,
8        ACTION AS S_ACTION,
9        APPLICATION_KEY AS S_APPLICATION_KEY,
10        METADATA AS S_METADATA,
11        SESSION_DATA AS S_SESSION_DATA,
12        DEVICE AS S_DEVICE,
13        USER AS S_USERNAME,
14        PACKAGE AS S_PACKAGE,
15        NET AS S_NET,
16        NODE AS S_NODE,
17        session_data || '-'
18            || IFNULL(device, '') AS S_SESSION
19    FROM
20        CHANGELOG_CTX;

Manually Create the session_summary View

Create the session_summary view using the following create statement::

1CREATE VIEW SESSION_SUMMARY AS
2    SELECT
3        MIN(DATE_FORMAT(db_stamp, '%Y-%m-%d %H:%M:%S')) AS 'S_START_TIME',
4        MAX(DATE_FORMAT(db_stamp, '%Y-%m-%d %H:%M:%S')) AS 'S_END_TIME',
5        (MAX(DATE_FORMAT(db_stamp, '%s')) - MIN(DATE_FORMAT(db_stamp, '%s'))) AS 'S_DURATION',
6        MIN(DATE_FORMAT(db_stamp, '%Y-%m-%d')) AS 'S_DATE',
7        DATE_FORMAT(MIN(db_stamp), '%H') AS S_HOUR,
8        DATE_FORMAT( MIN(db_stamp), '%m') AS S_MONTH,
9        DATE_FORMAT(MIN(db_stamp), '%Y') AS S_YEAR,
10        IFNULL(MAX(user), '(unknown)') AS 'S_USERNAME',
11        package AS 'S_PACKAGE',
12        IFNULL(MAX(CASE `key`
13                    WHEN 'application.project_guid' THEN value
14                    ELSE NULL
15                END),
16                IFNULL(MAX(CASE `key`
17                            WHEN 'application.start' THEN value
18                            ELSE NULL
19                        END),
20                        '(unknown)')) AS S_PROJECT_GUID,
21        COUNT(CASE action
22            WHEN 'update' THEN 1
23            ELSE NULL
24        END) AS 'S_UPDATES',
25        COUNT(CASE action
26            WHEN 'close' THEN 1
27            ELSE NULL
28        END) AS 'S_CLOSED',
29        COUNT(CASE `key`
30            WHEN 'guideline.finished' THEN 1
31            ELSE NULL
32        END) AS 'S_ENDPOINTS_REACHED',
33        session_data || '-'
34            || IFNULL(device, '') AS 'S_SESSION',
35        IFNULL(MAX(CASE `key`
36                    WHEN 'application.http_headers.accountId' THEN value
37                    ELSE NULL
38                END),
39                '(unknown)') AS S_ORGANIZATION,
40        IFNULL(MAX(CASE `key`
41                    WHEN 'application.userSpecialty' THEN value
42                    ELSE NULL
43                END),
44                '(unknown)') AS S_USERSPECIALTY,
45        IFNULL(MAX(CASE `key`
46                    WHEN 'application.userRole' THEN value
47                    ELSE NULL
48                END),
49                '(unknown)') AS S_USERROLE,
50        IFNULL(MAX(CASE `key`
51                    WHEN 'application.start' THEN value
52                    ELSE NULL
53                END),
54                '(unknown)') AS S_TITLE
55    FROM
56        changelog_ctx
57    GROUP BY session_data || '-'
58        || IFNULL(device, '');

Update Configuration Settings

The final step is setting up the reporting cron job in MySQL is to establish the connection between LogicNets and the MySQL schema created.

1. Navigate to "System Configuration" on your LogicNets dashboard.
    
2. Navigate to the "Database" tab.
    
3. In the Alias field under "External databases", add "__changelog_ctx".
    
4. In the "Database name/ ODBC string" field, add the following ODBC string:
   
   1odbc:DRIVER={MySQL ODBC 8.0 ANSI Driver}; SERVER=localhost;
   DATABASE=changelog; UID=root;
   PASSWORD=pass123;NO_TRANSACTIONS=1;MULTI_STATEMENTS=1

Make sure to update the ODBC string to include the correct server settings, including the server name and password. 

Add Cron Job to Scheduler Monitor

Once you have created the schema and set up the reporting cron job, navigate to the Scheduler Monitor on your LogicNets dashboard. 

  

Add the cron job to the Scheduler Monitor. The steps required to do that are available here: Scheduler Monitor Overview.

Cron Job Configurable Settings

The cron job contains the following configurable settings that all that you should check:

• changelog_limit: By configuring the changelog limit you can set how many rows of the database the system copies over each time it runs the cron job.
• device: This allows you to set the name of the server from which the cron job runs.