Contact Us 1-800-596-4880
  1. Homepage
  2. Runtime Manager
  3. Troubleshooting
  4. Export Data to External Analytics Tools

  5. Send API Analytics to an External Database

Send API Analytics from Runtime Manager to an External Database

logo hybrid active Hybrid logo server active Private Cloud Edition

You can configure the Runtime Manager agent to store in a database HTTP API analytics data from Mule apps running on the API gateway runtime.

Sending data to third-party software is available only for applications deployed to local servers. It is not available for applications deployed to CloudHub.

For information about sending this data to Splunk, ELK, or other third-party software, see Export Data to External Analytics Tools.

Prerequisites

Ensure that the following software is installed:

Install API Analytics-Tracking Database Internal Handlers

Depending on your version of the Runtime Manager agent, you might need to install the API Analytics-Tracking Database Internal Handlers ZIP file.

For the following agent versions, you don’t need to install the ZIP file because the required JAR files are included in the agent plugin:

  • 1.14.3 and later

  • 2.4.5 and later

For the following agent versions, you must download and install the ZIP file manually:

  • 1.14.2 and earlier

  • 2.4.4 and earlier

To install the ZIP file:

  1. Request the API Analytics-Tracking Database Internal Handlers ZIP file (mule-agent-internal-handlers-db-1.5.1.zip) from Support.

  2. Extract the ZIP file to the {MULE_HOME}/plugins/mule-agent-plugin/lib/modules folder.

Agent-Configurable Fields

After you have the latest Runtime Manager agent and the API analytics tracking database internal handlers installed on your API gateway standalone server, enable the internal handler by modifying your {MULE_HOME}/conf/mule-agent.yml configuration file.

In the following example configurations for MySQL Server, Oracle, and Microsoft SQL Server, the required fields are driver, jdbcUrl, user, and pass. You can also configure the apiAnalyticsTable field to specify where the analytics are stored.

Field Data Type Description Type Default Value

driver

String

The JDBC driver to use to communicate with the database server

Required

jdbcUrl

String

The JDBC URL for the database server

Required

user

String

The username to use to connect to the database server

Required

pass

String

The password to use to connect to the database server

Required

apiAnalyticsTable

String

The name of the table in which the agent stores the events

Optional

MULE_API_ANALYTICS

MySQL Example Configuration

The following example shows how to set up the Runtime Manager agent to work with MySQL databases.

Schema

CREATE TABLE MULE_API_ANALYTICS (
  id                           CHAR(36)     NOT NULL,
  api_id                       INT          NOT NULL,
  api_name                     VARCHAR(64)  NULL,
  api_version                  VARCHAR(150) NULL,
  api_version_id               INT          NOT NULL,
  application_name             VARCHAR(42)  NULL,
  client_id                    VARCHAR(255) NULL,
  client_ip                    VARCHAR(45)  NOT NULL,
  event_id                     VARCHAR(36)  NOT NULL,
  host_id                      VARCHAR(255) NULL,
  org_id                       VARCHAR(36)  NULL,
  path                         VARCHAR(500) NOT NULL,
  policy_violation_policy_id   VARCHAR(50)  NULL,
  policy_violation_policy_name VARCHAR(150) NULL,
  policy_violation_outcome     VARCHAR(10)  NULL,
  received_ts                  VARCHAR(30)  NOT NULL,
  replied_ts                   VARCHAR(30)  NOT NULL,
  request_bytes                INT          NOT NULL,
  request_disposition          VARCHAR(10)  NOT NULL,
  response_bytes               INT          NOT NULL,
  status_code                  INT          NOT NULL,
  transaction_id               VARCHAR(36)  NULL,
  user_agent                   VARCHAR(500) NULL,
  verb                         VARCHAR(8)   NOT NULL,
  PRIMARY KEY (id)
);

If you are migrating from a Runtime Manager agent version earlier than 2.3.0, you must update the schema. You can use the following migration script to update the policy_violation_policy_id data type in MySQL:

ALTER TABLE MULE_API_ANALYTICS MODIFY policy_violation_policy_id VARCHAR(50) NULL;

Internal Handler Configuration

  1. Download the MySQL JDBC driver from:

    http://dev.mysql.com/downloads/connector/j/

  2. Extract the ZIP file.

  3. Copy the mysql-connector-java-{VERSION}.jar file to {MULE_HOME}/plugins/mule-agent-plugin/lib/modules.

  4. Modify the file {MULE_HOME}/conf/mule-agent.yml to include the following:

      mule.agent.gw.http.handler.database:
    enabled: true
    driver: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.61.128/mule
    user: root
    pass: test

Oracle Example Configuration

The following example shows how to set up the Runtime Manager agent to work with Oracle databases.

Schema

CREATE TABLE MULE_API_ANALYTICS (
  id                           CHAR(36)     NOT NULL,
  api_id                       INT          NOT NULL,
  api_name                     VARCHAR(64)  NULL,
  api_version                  VARCHAR(150) NULL,
  api_version_id               INT          NOT NULL,
  application_name             VARCHAR(42)  NULL,
  client_id                    VARCHAR(255) NULL,
  client_ip                    VARCHAR(45)  NOT NULL,
  event_id                     VARCHAR(36)  NOT NULL,
  host_id                      VARCHAR(255) NULL,
  org_id                       VARCHAR(36)  NULL,
  path                         VARCHAR(500) NOT NULL,
  policy_violation_policy_id   VARCHAR(50)  NULL,
  policy_violation_policy_name VARCHAR(150) NULL,
  policy_violation_outcome     VARCHAR(10)  NULL,
  received_ts                  VARCHAR(30)  NOT NULL,
  replied_ts                   VARCHAR(30)  NOT NULL,
  request_bytes                INT          NOT NULL,
  request_disposition          VARCHAR(10)  NOT NULL,
  response_bytes               INT          NOT NULL,
  status_code                  INT          NOT NULL,
  transaction_id               VARCHAR(36)  NULL,
  user_agent                   VARCHAR(500) NULL,
  verb                         VARCHAR(8)   NOT NULL,
  PRIMARY KEY (id)
);

If you are migrating from a Runtime Manager agent version earlier than 2.3.0, you must update the schema. For example, you can use the following migration script to update the policy_violation_policy_id data type in Oracle 11g XE:

ALTER TABLE MULE_API_ANALYTICS ADD temp VARCHAR(50);
update MULE_API_ANALYTICS set temp=cast(policy_violation_policy_id as VARCHAR(50));
ALTER TABLE MULE_API_ANALYTICS DROP COLUMN policy_violation_policy_id;
ALTER TABLE MULE_API_ANALYTICS RENAME COLUMN temp TO policy_violation_policy_id;

Internal Handler Configuration

  1. Download the Oracle JDBC driver from:

    https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html

  2. Extract the GZ file.

  3. Copy the JAR file to {MULE_HOME}/plugins/mule-agent-plugin/lib/modules.

  4. Modify the file {MULE_HOME}/conf/mule-agent.yml to include the following:

      mule.agent.gw.http.handler.database:
    enabled: true
    driver: oracle.jdbc.OracleDriver
    jdbcUrl: jdbc:oracle:thin:@192.168.61.128/XE
    user: root
    pass: test

Microsoft SQL Server Example Configuration

The following example shows how to set up the Runtime Manager agent to work with Microsoft SQL Server databases.

Schema

CREATE TABLE MULE_API_ANALYTICS (
  id                           CHAR(36)     NOT NULL,
  api_id                       INT          NOT NULL,
  api_name                     VARCHAR(64)  NULL,
  api_version                  VARCHAR(150) NULL,
  api_version_id               INT          NOT NULL,
  application_name             VARCHAR(42)  NULL,
  client_id                    VARCHAR(255) NULL,
  client_ip                    VARCHAR(45)  NOT NULL,
  event_id                     VARCHAR(36)  NOT NULL,
  host_id                      VARCHAR(255) NULL,
  org_id                       VARCHAR(36)  NULL,
  path                         VARCHAR(500) NOT NULL,
  policy_violation_policy_id   VARCHAR(50)  NULL,
  policy_violation_policy_name VARCHAR(150) NULL,
  policy_violation_outcome     VARCHAR(10)  NULL,
  received_ts                  VARCHAR(30)  NOT NULL,
  replied_ts                   VARCHAR(30)  NOT NULL,
  request_bytes                INT          NOT NULL,
  request_disposition          VARCHAR(10)  NOT NULL,
  response_bytes               INT          NOT NULL,
  status_code                  INT          NOT NULL,
  transaction_id               VARCHAR(36)  NULL,
  user_agent                   VARCHAR(500) NULL,
  verb                         VARCHAR(8)   NOT NULL,
  PRIMARY KEY (id)
);

If you are migrating from a Runtime Manager agent version earlier than 2.3.0, you must update the schema. You can use the following migration script to update the policy_violation_policy_id data type in Microsoft SQL Server 2014:

ALTER TABLE MULE_API_ANALYTICS ALTER COLUMN policy_violation_policy_id VARCHAR(50) NULL;

Internal Handler Configuration

  1. Download the Microsoft JDBC driver for SQL Server from:

    https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774

  2. Extract the sqljdbc%version%.tar.gz file.

  3. Copy the appropriate sqljdbc%version%.jar file to {MULE_HOME}/plugins/mule-agent-plugin/lib/modules.

  4. Modify the file {MULE_HOME}/conf/mule-agent.yml to include the following:

      mule.agent.gw.http.handler.database:
    enabled: true
    driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
    jdbcUrl: jdbc:sqlserver://192.168.61.128:1433;databaseName=Mule;
    user: root
    pass: test

See Also