SQL Monitoring Extension

The purpose of this monitor is to use arbitrary queries against a SQL database as metrics for AppDynamics. The connection to the database is via JDBC.

 

Installation

 

1. Download and unzip SQLMonitor.zip

2. Copy the SQLMonitor directory to `<MACHINE_AGENT_HOME>/monitors`.

 

You will need to provide your own JDBC driver for the database you want to connect to.

Put the driver JAR file in the SQLMonitor directory and add it to the classpath element in the monitor.xml file.

Example:

<java-task>
    <!-- Use regular classpath foo.jar;bar.jar -->
    <!-- append JDBC driver jar -->
    <classpath>sql-monitoring-extension.jar:ojdbc6-11.2.0.3.jar</classpath>
    <impl-class>com.appdynamics.monitors.sql.SQLMonitor</impl-class>
</java-task>

 

Configuration

 

Note-

Please make sure to not use tab (\t) while editing yaml files. You may want to validate the yaml file using a yaml validator http://yamllint.com/

 

1. Configure the SQL server instances by editing the config.yaml file in `<MACHINE_AGENT_HOME>/monitors/SQLMonitor/`. Below is the format

# SQLMonitor Configuration

# Provide password OR encryptedPassword,encryptionKey. See the documentation to find about password encryption.

# isolationLevel:Transaction isolation level to apply on the connection
#Supported values are TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ and TRANSACTION_SERIALIZABLE
#Default is TRANSACTION_READ_UNCOMMITTED

dbServers:
    - displayName: "Instance1"
      driver: "com.mysql.jdbc.Driver"
      connectionString: "jdbc:mysql://localhost:3388/test"
      user: "root"
      password:
      encryptedPassword: "IGVtC9eudmgG8RDjmRjGPQ=="
      isolationLevel: "TRANSACTION_READ_UNCOMMITTED"
      commands:
        - command: "select age from test1 where age = 29"
          displayPrefix: "Expedia"
        - command: "select age from test1 where age = 32"
          displayPrefix: "DerbySoft"

    - displayName: "Instance2"
      driver: "com.mysql.jdbc.Driver"
      connectionString: "jdbc:mysql://localhost:3388/database2"
      user: "root"
      password: "root"
      encryptedPassword:
      isolationLevel: "TRANSACTION_READ_UNCOMMITTED"
      commands:
        - command: "select value from monitortest where id = 1"
          displayPrefix: "Expedia"
        - command: "select value from monitortest where id = 2"
          displayPrefix: "DerbySoft"

numberOfThreads: 5
encryptionKey: "welcome"

#Runs the queries every taskDelaySeconds configured
taskSchedule:
    numberOfThreads: 1
    taskDelaySeconds: 60



# Make sure the metric prefix ends with a |
#This will create this metric in all the tiers, under this path.
#metricPrefix: "Custom Metrics|SQL|"
#This will create it in specific Tier. Replace  with TierID
metricPrefix: "Server|Component:|Custom Metrics|SQL|"

 

2. Configure the path to the config.yaml file by editing the <task-arguments> in the monitor.xml file. Below is the sample

 

     

<task-arguments>
         <!-- config file-->
           <argument name="config-file" is-required="true" default-value="monitors/SQLMonitor/config.yml"     />
         ....
</task-arguments>
 

 

Credentials Encryption

To avoid setting the clear text password in the config.yml, please follow the process to encrypt the password and set the encrypted password and the encryptionKey in the config.yml

• To encrypt password from the commandline go to/monitors/SQLMonitor dir and run the below command java -cp "sql-monitoring-extension.jar" com.appdynamics.extensions.crypto.Encryptor myKey myPassword

 

Metric Queries

Only queries that start with SELECT are allowed.!

The queries to get the metric values from the database should only return one row and one column, additional rows and columns will be ignored. The name of the metric will be the first column name return by the query.

 

Example-

commands:
   - command: "select foo as foobar from bar where id = 10"
     displayPrefix: "Expedia"

 

displayPrefix helps to give better visualization on schema name or table name for particular sql metric.

 

commands:

   - command: "select foo as foobar from bar where id = 10"

     displayPrefix: "Expedia"

   - command: "select foo as foobar from bar where id = 5"

     displayPrefix: "DerbySoft"

 

 

Contributing

 

Always feel free to fork and contribute any changes directly via GitHub.

 

Support

 

For any support questions, please contact help@appdynamics.com.