Sample Queries

  • This document provides example queries for the APMMetrics dataset.
  • These examples demonstrate basic querying, filtering, aggregations, time analysis, and advanced metrics comparison.

1. Basic Queries

Retrieve Specific Columns

SELECT service_id, service_cpm
FROM default.APMMetrics
LIMIT 10;

Use aliases for column names

SELECT service_id AS Service, service_cpm AS CallsPerMinute FROM default.APMMetrics LIMIT 10;

2. Filtering with WHERE Clause

Filter by a single condition

SELECT service_id, service_resp_time FROM default.APMMetrics WHERE service_resp_time > 500 LIMIT 10;

Combine multiple conditions

SELECT service_id, service_error_count FROM default.APMMetrics WHERE service_error_code__500 > 50 AND service_error_code__404 > 20;

Use "IN"

SELECT entity_name FROM default.APMMetrics WHERE service_id IN ('service1', 'service2', 'service3') and entity_type='E';

Use "BETWEEN"

SELECT service_id, service_resp_time FROM default.APMMetrics WHERE service_resp_time BETWEEN 100 AND 1000;

Use "LIKE"

SELECT service_id, entity_name FROM default.APMMetrics WHERE entity_name LIKE 'service%' LIMIT 5;
note

Supported Operators in WHERE:

  • =, >, <, >=, <=
  • LIKE (for pattern matching)
  • IN (for matching against a list of values)
  • BETWEEN (for range filtering)

3. Aggregations with GROUP BY

Aggregate data and group by column

SELECT service_id, AVG(service_resp_time) AS AvgRespTime FROM default.APMMetrics GROUP BY service_id;

Calculate error percentage per team

SELECT service_id, SUM(service_error_count) AS TotalErrors, AVG(service_error_percent) AS AvgErrorPercent FROM default.APMMetrics GROUP BY service_id;

Maximum Error Count per Service

SELECT service_id, MAX(service_error_count) AS max_error_count
FROM default.APMMetrics GROUP BY service_id
LIMIT 10;

4. Sorting with ORDER BY

Sort results in ascending or descending order

SELECT service_id, service_cpm FROM default.APMMetrics ORDER BY service_cpm DESC LIMIT 10;

5. Time-Based Filtering with SINCE, UNTIL

Fetch metrics for the last 15 minutes

SELECT service_id, service_cpm FROM default.APMMetrics SINCE 15 mins;

Fetch metrics for a specific time range

SELECT service_id, service_resp_time FROM default.APMMetrics SINCE 1 hour UNTIL 30 mins;

6. Time Comparison with COMPARE WITH

Compare metrics with a previous time window

SELECT service_id, service_cpm FROM default.APMMetrics SINCE 1 day COMPARE WITH 1 week;

7. Function-Based Queries

Use aggregation functions

SELECT MAX(service_resp_time) AS MaxRespTime, MIN(service_resp_time) AS MinRespTime FROM default.APMMetrics WHERE service_id = 'service1';

Calculate the percentage of 4xx and 5xx errors

SELECT service_id, service_error_code__4xx_percent, service_error_code__5xx_percent FROM default.APMMetrics WHERE service_error_percent > 10;

8. JVM and CLR Metrics

Analyze JVM memory utilization

SELECT instance_jvm_memory_heap, instance_jvm_memory_noheap FROM default.APMMetrics SINCE 1 hour;

Compare CLR garbage collection metrics

SELECT instance_clr_gen0_collect_count, instance_clr_gen1_collect_count, instance_clr_gen2_collect_count FROM default.APMMetrics WHERE teamID = 'team123' COMPARE WITH 1 day;

9. Endpoint Metrics

Monitor endpoint response times and errors

SELECT endpoint_avg, endpoint_error_percent FROM default.APMMetrics SINCE 30 mins;

Aggregate endpoint metrics by SLA compliance

SELECT endpoint_sla, AVG(endpoint_error_percent) AS AvgErrorPercent FROM default.APMMetrics GROUP BY endpoint_sla;

10. Advanced Filtering

Find services with high call volume and low error rates

SELECT service_id, service_cpm, service_error_percent FROM default.APMMetrics WHERE service_cpm > 1000 AND service_error_percent < 5;

Identify entities with high response times

SELECT entity_id, entity_name, MAX(service_resp_time) AS MaxRespTime FROM default.APMMetrics GROUP BY entity_id, entity_name ORDER BY MaxRespTime DESC LIMIT 10;

11. Metrics Comparison

You can compare different metrics within the same query

SELECT service_id,service_cpm,service_epm,(service_cpm - service_epm) AS cpm_epm_diff FROM default.APMMetrics WHERE service_cpm > 100 ORDER BY cpm_epm_diff DESC LIMIT 10;