Send API Analytics from Runtime Manager to an External Database | MuleSoft Documentation
# Send API Analytics from Runtime Manager to an External Database
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:
  * API gateway standalone version 2.1.0 or later
  * Runtime Manager agent version 1.3.0 or later
For information about updating the agent, see Install or Update the Runtime Manager Agent.


## 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  
---|---|---|---|---  
The JDBC driver to use to communicate with the database server | Required  
The JDBC URL for the database server | Required  
The username to use to connect to the database server | Required  
The password to use to connect to the database server | Required  
`apiAnalyticsTable` | 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;
```
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
  * Export Data to External Analytics Tools
  * Servers, Server Groups, and Clusters
  * Deploy to Your Servers
  * Manage Applications on Your Servers
  *