Collect Business Data From SQL Calls

Transaction snapshots capture SQL database calls. The SQL calls can contain useful business data. Analytics SQL data collectors are a way to collect the business data from SQL parameters for use in transaction analytics.

To configure an Analytics SQL data collector you need to know:

  • Analytics-enabled application where the SQL call is executed.
  • Database target of the SQL call.
  • Specific SQL parameterized statement that contains the data of interest as a query parameter.
  • Analytics-enabled business transactions making the database call.
  • Parameters to be collected.

This feature is supported as:

  • The SQL data collector list shows the slowest database calls over the last 30 days. You can also create a SQL data collector directly from theDB & Remote Service Callstab of a snapshot.
  • Requires 4.3 Java Agent or 4.3 .NET Agent.
  • Only prepared statements containing data of interest as binding variables can be used. Literal strings that are passed in can not be collected.
  • There is a 500-character limit on the length of the SQL statement. Do not use any truncated queries that might appear in the SQL Statements list or a snapshot.
  • The overall number of executions of SQL queries configured to collect Analytics data is limited to 10K. This is configurable using the analytics-sql-cpm-limit node property. See App Agent Node Properties.

Configure SQL Data Collectors From Analytics

  1. In the Controller UI, from the top navigation bar, select Analytics > Configuration.
  2. From the Transaction Analytics tab, select the application from the Configure Analytics for Application dropdown and confirm that analytics data collection is enabled.
  3. Scroll down to expand the SQL Data Collectorssection, and click Add.
  4. Name your data collector and indicate if the collector should apply to new business transactions.
  5. Select the appropriate database. A list of available SQL statements displays showing the slowest database calls over the last 30 days. This timeframe is not configurable.
  6. Define the data to collect.
    1. Select the SQL prepared statement containing the parameter that you want to capture for analytics.
    2. Click Add to specify the data to be collected.
    3. Type a display name for the data you are collecting.This name appears in the Analytics UI Fields list when the data is collected and passed to Analytics.
    4. Specify the data type and method parameter index.
    5. Click Save.
    6. Click Create SQL Data Collector.
  7. Specify the business transactions that will use this collector and click Save.

Configure SQL Data Collectors From Snapshots

This procedure provides a shortcut way to set up the configuration for an Analytics SQL data collector for a database call captured in an application transaction snapshot. You need to find the transaction snapshot of interest and drill down to the node that contains the database call of interest.

  1. From the DB & Remote Service Calls tab of a snapshot containing the SQL call of interest, select the prepared statement that contains the data you want to collect for analytics.
  2. Right-click the query and select Configure Data Collector from the context menu. The Data Collection panel displays.
  3. Enter a display name for the data you are collecting.
  4. Specify the type and the parameter to collect and click Save.
  5. Select the business transactions from which to collect the data and click Save.

View SQL Data in Analytics

After the data is successfully collected for analytics, you see the fields in the Fields list in a section labeled SQL Data. This can take a few minutes depending on your application and the frequency of transactions executing the relevant SQL query.