lynn,
unfortunately for me this solution still did not work, see in the examples below in both squirrel and grafana, none of the queries brings a satisfactory result.
ps: on this server that I'm testing I have over 2000 agents, so I'm testing on several to see if I can get a response from any of them, but to no avail.
select metric_path,year(ts) || '-' || month(ts) || '-' || dayofmonth(ts) || ' ' || hour(ts) || ':' || minute(ts) || ':' || second(ts) as theTime,
(timestampdiff(SQL_TSI_SECOND, FROM_UNIXTIME(0), PARSETIMESTAMP( year(ts) || '-' || month(ts) || '-' || dayofmonth(ts) || ' ' || hour(ts) || ':' || minute(ts) || ':' || second(ts) ,'yyyy-MM-dd hh:mm:ss')))*1000 as tstamp,
sum(value_count) as value_count,
avg(agg_value) as agg_value,
min(min_value) as min_value,
max(max_value) as max_value
from numerical_metric_data
where frequency=15000
and ts between '2018-10-18 11:39:23' and '2018-10-18 11:54:23'
and agent_name like_regex '.*jboss\\_p\\_06857\\_eassinarfb.*'
and metric_path like 'Frontends|Apps|%:Responses Per Interval'
group by metric_path,year(ts),month(ts),dayofmonth(ts),hour(ts), minute(ts), second(ts)
order by metric_path
Another empty result:
select metric_path,year(ts) || '-' || month(ts) || '-' || dayofmonth(ts) || ' ' || hour(ts) || ':' || minute(ts) || ':' || second(ts) as theTime,
(timestampdiff(SQL_TSI_SECOND, FROM_UNIXTIME(0), PARSETIMESTAMP( year(ts) || '-' || month(ts) || '-' || dayofmonth(ts) || ' ' || hour(ts) || ':' || minute(ts) || ':' || second(ts) ,'yyyy-MM-dd hh:mm:ss')))*1000 as tstamp,
sum(value_count) as value_count,
avg(agg_value) as agg_value,
min(min_value) as min_value,
max(max_value) as max_value
from numerical_metric_data
where frequency=15000
and ts between '2018-10-18 11:39:23' and '2018-10-18 11:54:23'
and agent_name like_regex '.*spcdsrvv0895.*'
and metric_path like 'Frontends|Apps|%:Responses Per Interval'
group by metric_path,year(ts),month(ts),dayofmonth(ts),hour(ts), minute(ts), second(ts)
order by metric_path
logs from apmsqlgrafanaservice.log:
Before result.from: 2018-10-18T14:39:23.811Z
Before result.to: 2018-10-18T14:54:23.812Z
kSmartStorTimeZone: sun.util.calendar.ZoneInfo[id="America/Sao_Paulo",offset=-10800000,dstSavings=3600000,useDaylight=true,transitions=129,lastRule=java.util.SimpleTimeZone[id=America/Sao_Paulo,offset=-10800000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=10,startDay=1,startDayOfWeek=1,startTime=0,startTimeMode=0,endMode=3,endMonth=1,endDay=15,endDayOfWeek=1,endTime=0,endTimeMode=0]]
After result.from: 2018-10-18 11:39:23
After result.to: 2018-10-18 11:54:23
diffHours<1
Target has more than 1:false
rawCommand: apmsql '.*spcdsrvv0895.*' 'Frontends|Apps|%:Responses Per Interval' agg_value exact
select metric_path,year(ts) || '-' || month(ts) || '-' || dayofmonth(ts) || ' ' || hour(ts) || ':' || minute(ts) || ':' || second(ts) as theTime,
(timestampdiff(SQL_TSI_SECOND, FROM_UNIXTIME(0), PARSETIMESTAMP( year(ts) || '-' || month(ts) || '-' || dayofmonth(ts) || ' ' || hour(ts) || ':' || minute(ts) || ':' || second(ts) ,'yyyy-MM-dd hh:mm:ss')))*1000 as tstamp,
sum(value_count) as value_count,
avg(agg_value) as agg_value,
min(min_value) as min_value,
max(max_value) as max_value
from numerical_metric_data
where frequency=15000
and ts between '2018-10-18 11:39:23' and '2018-10-18 11:54:23'
and agent_name like_regex '.*spcdsrvv0895.*'
and metric_path like 'Frontends|Apps|%:Responses Per Interval'
group by metric_path,year(ts),month(ts),dayofmonth(ts),hour(ts), minute(ts), second(ts)
order by metric_path
limit 2147483647
Result Set is Empty for given query ->apmsql '.*spcdsrvv0895.*' 'Frontends|Apps|%:Responses Per Interval' agg_value exact and count is 0
i is 0 hence printing [
Empty String is :[
{}]