APMSQL Server query is not returning any rows

Document ID : KB000110273
Last Modified Date : 10/08/2018
Show Technical Document Details
Issue:
Running the following  format query returns nothing

SELECT DISTINCT agent_name
FROM numerical_metric_data
WHERE ts BETWEEN '2018-06-01' AND '2018-06-02'
AND frequency = 15000
ORDER BY agent_name DESC


if I change the query and remove the 'AND frequency = 15000' query it returns the data which seems in conflict with the documentation -

"Frequency and ts are essential. Include a number for frequency and a time stamp (that is, 'ts') attribute in every query, as you see in the following examples."
Environment:
APM 10.5 and later
Cause:
The reason for the no data returned is that the data crosses tier1, the frequency of the data storage changes. So after tier1 data is not stored with a frequency of 15s - default frequency for tier2 and tier3 is 60s and 900s respectively
Resolution:
Change the frequency qualifier in the query from "=" to ">=" ie..

SELECT DISTINCT agent_name
FROM numerical_metric_data
WHERE ts BETWEEN '2018-06-01' AND '2018-06-02'
AND frequency >= 15000
ORDER BY agent_name DESC