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 :[

{}]