Using Grafana with CA APMSQL

Grafana is an open source reporting tool that is distributed and supported by Grafana Labs. With Grafana, CA APM customers can generate professional, easy-to-interpret, customized views of their APM data. The following screenshot shows a Grafana dashboard:
Grafana is an open source reporting tool that is distributed and supported by Grafana Labs. With Grafana, CA APM customers can generate professional, easy-to-interpret, customized views of their APM data. The following screenshot shows a Grafana dashboard:
image2017-10-26 14:2:25.png
Because of the intense disk activity that is associated with reporting using APM and its proprietary SmartStor data, it is recommended that Grafana users deploy their SmartStor data directory on a solid-state drive. Deploying the SmartStor data directory on a solid-state drive is not required, but it is distinctly valuable when running weekly or monthly reports.
This article explains the Grafana setup process using a new Grafana data source that connects to APMSQL. By the end of the article, you should be ready to use Grafana to create your own custom APM views. Grafana documentation is extensively available on the web. See the Grafana Getting Started Guide for a tutorial on building your first dashboard.
Before you start, check that you have the following components set up:
  • APM 10.5+ (APMSQL must be available)
  • APMSQL server running and connected, and
    of the following components:
    • Built-in Jetty JSON data source
      The Grafana data source is built into the APMSQL server by default in APM 10.7 SP2
    • Tomcat 8+ instance, or equivalent (must support JAX-RS), in which you deploy the new Grafana data source for APM.
  • Grafana 4.6.0+ (tested against 4.6.0)
See Prerequisites for further details.
APM Grafana Architecture Overview
The following diagram shows Grafana in the APM infrastructure:
The APM Enterprise Manager (EM) receives and stores data in an optimized database with self-archiving features called SmartStor. Install and Configure APMSQL Servers provides a relational interface to SmartStor, allowing complex SQL queries through a JDBC connection. You can query APM using SQL over a JDBC connection to APMSQL. Since Grafana does not support querying through a JDBC connection, an additional layer was added to the architecture.  Grafana issues queries and takes data responses in JSON. CA APM implemented a new REST service that can take the JSON queries from Grafana, relay them as SQL queries to APMSQL, and return the SQL result sets it receives as a stream of JSON to Grafana.  The REST service is deployed by default in the APMSQL server process with APM 10.7 SP2, but it can also be deployed as the
file in a stand-alone Tomcat instance. In either case, the REST service must be configured to connect to a running APMSQL server.
The REST service is configured by default when using the APSQML server in-process deployment.
In the Grafana Architecture, Tomcat exists as a host for the APMSQL JSON plug-in REST service.  The Tomcat deployment is an optional configuration.  By using the Jetty server that is built into APMSQL, the JDBC connection becomes local, and the Tomcat tier is no longer necessary.  Most deployments start with the embedded Jetty container, and move to Tomcat instances for scalability.
The JSON data source was designed for use with Grafana, but can also be deployed in Tomcat.
The data source can also be consumed by any JSON-savvy REST client, including JavaScript code running in a browser.  If you want to run your own RESTful integration with the APMSQL JSON data source, see the Grafana documentation about Simple JSON Data Sources.
Open the log files that the APMSQL JSON data source generates to see more information about queries and responses. JSON queries are visible in the
file. JSON responses are visible in the
    - Set up and run the APMSQL server as documented here.
    If you have trouble connecting to your APMSQL server, set the JDBC bind address to the IP of the APMSQL server host. Change the<apmsql server ip>
    setting in the
    config/ file.
  • Application Container
    - Deploy a Tomcat 8+ instance with the
    file, or use a different container that supports JAX-RS. Whichever container you choose, configure the container to contact the proper APMSQL host and port. See the following example of defining a resource for Tomcat.
    If you choose to deploy a Tomcat 8+ instance, remove the
    from the
    file name. See Setting up Grafana for CA APM for more information.
  • Data Source for the Application Container
    - For Tomcat, open the context.xml file from the conf folder and add the following lines to secure the connection to APMSQL:
    url="jdbc:teiid:[email protected]://"
    , and
    parameters are the same ones that you use to connect a JDBC client to APMSQL. If you do not want to embed a password, generate a permanent token in the APM Team Center interface in
    . Replace the username with the token and leave the password blank.
  • Installed or upgraded EM
    - When you successfully install or upgrade the EM, the
    folder in the
    directory contains the following folders:
    • repo
      contains the dependent JAR files and two WAR files:<version_number>.war
    • bin
      contains the
      apmsql script
      (Linux) and
      (Windows) files.
    • client
      contains the
      file that serves as a connection to APMSQL via JDBC.
    • webapps
      contains the WAR files that you deploy to the embedded Jetty container.
    • config
      contains the configuration for the port number, data source, and web app context.
    • logs
      contains the Grafana REST service and Jetty server log files.
The following images show the key components of a successful installation:
The Grafana Simple JSON source, which is located in the WAR file, supports JSON queries. You can use JavaScript libraries or other tools that support the RESTful POST as a query mechanism for receiving JSON. For more information, see Grafana Data Sources.
Setting up Grafana for CA APM
After you download the data source WAR files, deploy the files to your Jetty container or Tomcat server.
Follow these steps:
  1. In the
    folder, open the
    file and enter the following details:
    • APMSQL username and password or authentication token
      the default values are entered against these fields.
    • Grafana REST Service Port Number
      the Grafana dashboard accesses the REST service through the following URL:
      Default: 9080
  2. (Optional) Configure the following context deployment files for the APMSQL server and Grafana REST services:
    • grafana-jetty-app.xml
    • apmsql-jetty-app.xml
      The context deployment files are automatically deployed when you start the Jetty server.
  3. (Optional) To deploy Grafana on a Tomcat server, follow these steps:
    1. Copy the 
      file to the
    2. Delete
      from the
      file name
    3. Add the following lines to the
      file to define the data source connection pooling for APMSQL:
      url="<APMSQL connection URL used in squirrel client>"
      username="<apmsql user name>"
      password="<apmsql password>"
    If you encounter any exceptions, open the
    file to investigate. Find further JSON debugging information in the
    file and the
  4. Run the
    apmsql script
    (Linux) or
    (Windows) to start the APMSQL server and Grafana REST services.
    file opens.
  5. Monitor the server startup progress in the log file.
Connecting to the Grafana Dashboard
To connect to the Grafana Dashboard, download the Grafana server, install the JSON data source plug-in, deploy the APMSQL REST service, and establish the connection to APMSQL through a data source. See the previous
Tomcat context.xml
data source example to establish the connection.
Follow these steps:
  1. Download and Start the Grafana server
    Go to the Grafana download page and select the OS for running your Grafana server. After you download the server, follow the configuration steps.
    Ensure that you select the correct port number. Each OS uses a different port number. For more information, see Grafana Installation.
  2. Start the Grafana server and open the Grafana client
    Go to
    . The default credentials are
  3. Install the JSON data source Plug-in
    Grafana provides its own JSON data source plug-in. To install this plug-in, go to the
    folder of the server and open a separate terminal or command prompt here. While the server is running, execute the following command:
    grafana-cli plugins install grafana-simple-json-datasource
    The command confirms that the plug-in was installed. Restart Grafana server.
  4. Define a New JSON data source in Grafana
    After you deploy the REST service on an application server, create a JSON data source in Grafana.
    Follow these steps:
    1. Open
      in a browser to access the Grafana console (or whatever port you chose for Grafana)
      The default credentials are admin/admin.
    2. On the home page, click the Grafana icon and click
      Data Sources
      grafana menu datasources.png
    3. Click
      Add Data Source
    4. Enter the data source name and select
      as the type of data source:
    5. Fill in the URL details as follows:
      The default port number is 9080.
      If you deployed the APMSQL REST service in Tomcat, use the hostname and port number of the Tomcat server, for example:
      8080 is both the default port of Tomcat and WebView. If your Tomcat instance shares the WebView host, you may need to check or change the default port in the Tomcat
  5. Test the Connection
    After filling in the data source details, click
    Save & Test
    . A green confirmation message appears:
You successfully connected to the Grafana Dashboard!
If the connection fails, verify that Tomcat is running and ensure that the data source WAR file is deployed. The Tomcat logs show if the app was successfully deployed. If the app is deployed and running, look into the
Using Grafana
Grafana lets you directly query the APMSQL database. Organize, aggregate, and visualize the data in tables and graphs in Grafana.
Execute Time Series Queries in Grafana
Execute time series queries and visualize the results in dashboards.
Follow these steps:
  1. Navigate to
  2. Click
    to create a graph:
  3. Grafana shows static pre-loaded data in the graph. To show real APMSQL data, follow these steps:
    1. Click on the
      Panel Title
      and click
      panel edit.png
    2. In the
      tab, select
      as the
      Panel Data Source
      . Click
      Add Query
      and then click
      Toggle Edit Mode
      to enter edit mode:
      add query.png
    3. Click the eye icon next to
      Toggle Edit Mode
      to open the query editor:
      image2017-10-26 14:47:3.png
    4. Paste your query into the query editor. All queries have the following syntax:
      apmsql <agent specifier regex> <metric specifier regex> <column specifier> [exact]
      More Information:
      For more information about the query using <agent_specifier regex> and handling special characters in agent names, see CA APM Grafana APMSQL queries.
      The RegEx syntax should be familiar to CA APM administrators and analysts. If your regexp does not match an agent, or matches an agent, but not a metric, the graph shows blank. If your expression matches multiple agents, the query arbitrarily selects one agent to display. If you execute a time series query and more than one metric matches an agent, the query selects one metric to display. Table queries expect one agent, and one or more metrics.
      • apmsql
        Defines APMSQL as the query type.
      • agent
        specifier regex
        Matches a single agent using RegEx query.
      • metric
        s specifier regex
        Matches a single agent (for time series queries), or multiple metrics (for table queries) using a regular expression.
      • column
        Supplements time series queries. See APMSQL documentation for more details about these columns.
        agg_value, value_count, min_value, or max_value
      • exact
        Boosts query performance by expecting an exact match for the metric name.
        Always use the exact keyword for final queries. This keyword significantly improves Query performance.
Example 1:
Create a graph with multiple metrics.This example returns the changes for one metric path (a single CPU) over a specific time period:
apmsql '.*thieves.*' 'CPU|Processor 0:Utilization.*' agg_value
By adding a second metric path, you can have a graph with two lines of data:
apmsql '.*thieves.*' 'CPU|Processor 1:Utilization.*' agg_value
Grafana shows results for two metrics in the graph.
image2017-10-26 14:47:43.png
Example 2: Select response times for frontend thieves.
This example returns the average response times of all
of application frontends, without the
Called Backends
metrics. Use this query in the table and heatmap widget:
apmsql '.*thieves.*' '^Frontends.*thieves.*/(?!.*?Called Backends).*Time.*' agg_value
image2017-10-27 11:49:24.png
If you are performing load testing with CA BlazeMeter or using Browser Agent, you can query your business transactions instead.
apmsql '.*Tomcat Agent' '^Business Segment.*thieves.*(?!.*?Called Backends).*Time.*' agg_value
If regular expression for matching metrics is unnecessary, Grafana for APMSQL also has an "exact" keyword, which enhances query performance:
apmsql '.*Tomcat Agent' 'Business Segment|ssd-thieves|Alejandro|Health:Average Response Time (ms)' agg_value exact
apmsql '.*Tomcat Agent' 'Business Segment|ssd-thieves|Bala|Health:Average Response Time (ms)' agg_value exact
apmsql '.*Tomcat Agent' 'Business Segment|ssd-thieves|Escape|Health:Average Response Time (ms)' agg_value exact
apmsql '.*Tomcat Agent' 'Business Segment|ssd-thieves|Search|Health:Average Response Time (ms)' agg_value exact
apmsql '.*Tomcat Agent' 'Business Segment|ssd-thieves|Steal|Health:Average Response Time (ms)' agg_value exact
Specifying the target metric specifiers exactly results in extremely speedy reports, even over long time ranges.  Once you know what metrics you want to query, it's a good idea to start using the "exact" keyword.
Example 3:
Select response times for the purchase frontend and other frontends.
This example matches the average response time for the
frontend, if it is the only one enabled, and several other frontends as desired:
apmsql '.*thieves.*' '^Frontends.*thieves.*/purchase(?!.*?Called Backends).*Time.*' agg_value
image2017-10-27 11:50:10.png
Example 4: Test the connection using the GC Heap.
This query selects agents with "thieves" in their name. If a connected agent has "thieves" in its name, the
GC Heap:Bytes In Use
parameter should always be reporting. Use this query to test the connection.
apmsql '.*thieves.*' 'GC Heap.*Bytes.*Use' agg_value
image2017-10-27 11:54:54.png
You created a graph that shows APMSQL data!
Tables in Grafana
Configure tables in Grafana in the same way that you created a graph. Select
as the query type:
table type.png
This example uses multiple metrics to compile an overview of the performance of your app:
apmsql '.*thieves.*' '^Frontends.*thieves.*/(?!.*?Called Backends).*Time.*' agg_value
image2017-10-26 15:6:35.png
View Error Messages in Grafana
Some error messages from APMSQL queries can be viewed from the Grafana UI. An error message notification appears in red in the panel:
image2017-10-26 15:11:57.png
To validate your agent and metric expressions directly, paste the query into the SQL client that is connected to APMSQL, for example, SQuirrel.
In many cases the single quote character, ', has been almost invisibly substituted with a slanted or italicized single quote
Connection Errors
Grafana reports the following error messages in the tomcat log file:
  • Error Message:
    Error in acquiring APMSQL connection:Cannot create PoolableConnectionFactory (TEIID20020 Error establishing socket to host and port: hostname:54321. Reason: Connection refused: connect)
    You started up tomcat or deployed the Grafana REST Service, and the service is unable to connect to APMSQL.
  • Error Message:
    HTTP Error Bad gateway
    You set up a new connection and the Tomcat WAR REST service is down.
  • Error Message:
    HTTP Error Internal Server Error, Error in SQL Connection:Cannot create PoolableConnectionFactory (TEIID20020 Error establishing socket to host and port: hostname:54321. Reason: Connection refused: connect)
    You set up the JDBC connection, the Tomcat WAR service is up, but APMSQL is down or not reachable.
  • Error Message:
    Cannot create PoolableConnectionFactory (TEIID20020 Error establishing socket to host and port: hostname:54321. Reason: Connection refused: connect)
    APMSQL is unavailable when executing the query.
Syntax Errors
Go to the Grafana query inspector to check for syntax issues in your RegEx query:
Grafana reports the error message in green text:
image2017-11-2 11:30:41.png
Error Message:
TEIID30448 Error : An error in processing your regular expression
APMSQL was unavailable when you executed the query.
Troubleshooting Connection Issues
Troubleshoot Grafana connection issues.
Follow these steps:
  1. Execute an APMSQL query. If the query fails to return results, check if the connection to APMSQL through the Squirrel client is successful.
  2. Test the Grafana REST services:
    1. Check for error messages in the log files in the
    2. Test the Grafana REST service root URL.
      The query should return the following JSON Response:
      {"message":"Connection established successfully"}
      To change the port number, edit the
      l in the
    3. Check for REST service errors in the
      file. Both files are stored in the