Esta extensão pode ser usada para consultar um banco de dados compatível com ANSI SQL e os valores resultantes podem ser usados como métricas no AppDynamics. A conexão com o banco de dados é estabelecida por meio de uma conexão JDBC e você terá que usar um arquivo jar do driver JDBC "conector" para ter o extensão conectar e consultar o banco de dados.
As métricas relatadas pela extensão podem ser modificadas de acordo com os requisitos do usuário. Essa extensão pode ser usada para consultar e extrair métricas de qualquer banco de dados baseado em SQL.
-
Antes da instalação da extensão, os pré-requisitos mencionados aqui precisam ser atendidos. Não prossiga com a instalação da extensão se os pré-requisitos especificados não forem atendidos.
-
Baixe e instale Apache Maven que está configurado com
Java 8para construir o artefato de extensão a partir da fonte. Você pode verificar a versão do java usada no maven usando o comandomvn -voumvn --version. Se o seu maven estiver usando alguma outra versão do java, faça o download do java 8 para sua plataforma e defina o parâmetro JAVA_HOME antes de iniciar o maven. -
Esta extensão requer que o usuário forneça seu próprio arquivo Jar para se conectar ao banco de dados. Isso é muito essencial para estabelecer uma conexão com o banco de dados para obter as métricas.
-
A extensão precisa ser capaz de se conectar ao banco de dados para coletar e enviar métricas. Para fazer isso, você terá que estabelecer uma conexão remota entre a extensão e o produto ou ter um agente na mesma máquina executando o produto para que a extensão colete e envie as métricas.
- Clone the "SQLMonitor" repo using
git clone <repoUrl>command. - Run
mvn clean installfrom "SQLMonitor" and find the SQLMonitor-VERSION.zip file in the "target" folder. - Unzip the "SQLMonitor-VERSION.zip" from
targetdirectory into the<MACHINE_AGENT_HOME>/monitorsfolder. - In
$MACHINE_AGENT_HOME/monitors/SQLMonitor, edit the fileconfig.ymland configure the extension. See configuration section for more details. - Restart the Machine Agent.
Note: Please place the extension in the "monitors" directory of your Machine Agent installation directory. Do not place the extension in the "extensions" directory of your Machine Agent installation directory.
Note: You will need to provide your own JDBC driver for the database you want to connect to. Put the driver JAR file in the same directory and add it to the classpath element in the monitor.xml file.!
<java-task>
<!-- Use regular classpath foo.jar;bar.jar -->
<!-- append JDBC driver jar -->
<classpath>sql-monitoring-extension.jar;Jar-File-For_Your-DB.jar</classpath>
<impl-class>com.appdynamics.extensions.sql.SQLMonitor</impl-class>
</java-task>
- Edit the config.yaml file. An example config.yaml file follows these installation instructions.
- Configure the path to the config.yaml file by editing the task-argments in the monitor.xml file.
<task-arguments>
<!-- config file-->
<argument name="config-file" is-required="true" default-value="monitors/SQLMonitor/config.yml" />
....
</task-arguments>
- Restart the Machine Agent.
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
You will have to Configure the SQL server instances by editing the config.yaml file in <MACHINE_AGENT_HOME>/monitors/SQLMonitor/.
The information provided in this file will be used to connect and query the database.
You can find a sample config.yaml file below.
# 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 <ComponentID> with TierID. For more details refer https://community.appdynamics.com/t5/Knowledge-Base/How-do-I-troubleshoot-missing-custom-metrics-or-extensions/ta-p/28695
metricPrefix: "Server|Component:<ComponentID>|Custom Metrics|SQL|"
dbServers:
- displayName: "Instance1"
connectionUrl: ""
driver: ""
connectionProperties:
user: ""
password: ""
encryptedPassword: "" #Needs to be used in conjunction with `encryptionKey`. Please read the extension documentation to generate encrypted password. https://community.appdynamics.com/t5/Knowledge-Base/How-do-I-use-Password-Encryption-with-Extensions/ta-p/29397
# Replaces characters in metric name with the specified characters.
# "replace" takes any regular expression
# "replaceWith" takes the string to replace the matched characters
metricCharacterReplacer:
- replace: "%"
replaceWith: ""
- replace: ","
replaceWith: "-"
queries:
- displayName: "Active Events"
queryStmt: "Select NODE_NAME, EVENT_CODE, EVENT_ID, EVENT_POSTED_COUNT from Active_events"
columns:
- name: "NODE_NAME"
type: "metricPathName"
- name: "EVENT_ID"
type: "metricPathName"
- name: "EVENT_CODE"
type: "metricValue"
- name: "EVENT_POSTED_COUNT"
type: "metricValue"
- displayName: "TRANSACTION DATABASE"
queryStmt: "SELECT TARGET_BOX, REACH_DURATION, ROUTER_DURATION FROM ASG_TRANSACTIONS WHERE TARGET_BOX IN ('target1','target2','target3','target4','target5')"
columns:
- name: "TARGET_BOX"
type: "metricPathName"
- name: "REACH_DURATION"
type: "metricValue"
- name: "ROUTER_DURATION"
type: "metricValue"
- displayName: "Node Status"
queryStmt: "Select NODE_NAME, NODE_STATE from NODE_STATES"
columns:
- name: "NODE_NAME"
type: "metricPathName"
- name: "NODE_STATE"
type: "metricValue"
properties:
convert:
"INITIALIZING" : 0
"UP" : 1
"DOWN" : 2
"READY" : 3
"UNSAFE" : 4
"SHUTDOWN" : 5
"RECOVERING" : 6
numberOfThreads: 5
#Run it as a scheduled task instead of running every minute.
#If you want to run this every minute, comment this out
#taskSchedule:
#numberOfThreads: 1
#taskDelaySeconds: 120
#Needs to be used in conjunction with `encryptedPassword`. Please read the extension documentation to generate encrypted password
encryptionKey: "welcome"
Lets take a look at some sample connection information:
dbServers:
- displayName: "Instance1"
connectionUrl: "jdbc:sqlserver://192.168.57.101:1433;user=bhuv;password=12345;databaseName=frb-test;"
driver: "com.microsoft.sqlserver.jdbc.SQLServerDriver"
# connectionProperties:
# user: ""
# password: ""
In order to connect to any database, you will have to provide a connectionUrl. In the example above we see that the extension is connected to the "sqlserver"(listed in the config) using the connectionUrl. In this case we are also providing the username, password and the databaseName in the same connectionUrl and therefore the "connectionProperties" and the fields under it, "user" and "password", are commented out. You have to make sure that if you are not sending any connectionProperties to create a connection, then you should comment the whole thing out just like in the example.
As this may not be the same for other types of SQL based systems, lets take a look at another way you can connect to the database. In this case we do need to provide properties such as a username and a password and therefore we uncomment those lines and update them with valid information.
dbServers:
- displayName: "Instance2"
connectionUrl: "jdbc:vertica://192.168.57.102:5433/VMart"
driver: "com.vertica.jdbc.Driver"
connectionProperties:
- user: "dbadmin"
- password: "password"
In this case we do add the Database Name as the last part of the connectionUrl (VMart) but all other properties like the username and password are provided as connectionProperties. You will have to confirm how your database takes in the login information and based on that provide the information in your config.yaml in order to successfully establish a connection.
Only queries that start with SELECT are allowed! Your query should only return one row at a time.
It is suggested that you only return one row at a time because if it returns a full table with enormous amount of data, it may overwhelm the system and it may take a very long time to fetch that data.
The extension does support getting values from multiple columns at once but it can only pull the metrics from the latest value from the row returned.
The name of the metric displayed on the Metric Browser will be the "name" value that you specify in the config.yml for that metric. Looking at the following sample query :
queries:
- displayName: "Active Events"
queryStmt: "Select NODE_NAME, EVENT_CODE, EVENT_ID, EVENT_POSTED_COUNT from Active_events"
columns:
- name: "NODE_NAME"
type: "metricPathName"
- name: "EVENT_ID"
type: "metricPathName"
- name: "EVENT_CODE"
type: "metricValue"
- name: "EVENT_POSTED_COUNT"
type: "metricValue"
- queries : You can add multiple queries under this field.
- displayName : The name you would like to give to the metrics produced by this query.
- queryStmt : This will be your SQL Query that will be used to query the database.
- columns: Under this field you will have to list all the columns that you are trying to get values from.
- name : The name of the column you would like to see on the metric browser.
- type : This value will define if the value returned from the column will be used for the name of the metric or if it is going to be the value of the metric.
- metricPathName : If you select this, this value will be added to the metric path for the metric.
- metricValue : If you select this, then the value returned will become your metric value that will correspond to the name you specified above.
For the query listed above, there will be two metrics returned as we have two columns of type "metricValue". The metric path for them will be :
- Custom Metrics|SQL|Instance1|Active Events|NODE_NAME|EVENT_ID|EVENT_CODE
- Custom Metrics|SQL|Instance1|Active Events|NODE_NAME|EVENT_ID|EVENT_POSTED_COUNT
Lets look at another query.
- displayName: "Node Status"
queryStmt: "Select NODE_NAME, NODE_STATE from NODE_STATES"
columns:
- name: "NODE_NAME"
type: "metricPathName"
- name: "NODE_STATE"
type: "metricValue"
properties:
convert:
"INITIALIZING" : 0
"UP" : 1
"DOWN" : 2
"READY" : 3
"UNSAFE" : 4
"SHUTDOWN" : 5
"RECOVERING" : 6
Lets say if your query returns a text value, but you would still like to see it in the metric browser. In order to make that happen, you could use the "convert" property and assign each value a number. The extension will automatically convert the text value to the corresponding number.
NOTE: In order to use this feature, please make sure that the value that is being returned is EXACTLY the same as you have listed in the config.yaml, otherwise the extension will throw an error.
Please visit this page to get detailed instructions on password encryption. The steps in this document will guide you through the whole process.
Workbench is an inbuilt feature provided with each extension in order to assist you to fine tune the extension setup before you actually deploy it on the controller. Please review the following document for how to use the Extensions WorkBench
Please follow the steps listed in the extensions troubleshooting document in order to troubleshoot your issue. These are a set of common issues that customers might have faced during the installation of the extension.
Always feel free to fork and contribute any changes directly via GitHub.
| Name | Version |
|---|---|
| Extension Version | 2.0.2 |
| Product Tested On | MySql, SQLServer 4.1,4.2, Vertica |
| Last Update | 08/06/2021 |
| Changes list | Change log |
Note: While extensions are maintained and supported by customers under the open-source licensing model, they interact with agents and Controllers that are subject to AppDynamics’ maintenance and support policy. Some extensions have been tested with AppDynamics 4.5.13+ artifacts, but you are strongly recommended against using versions that are no longer supported.