I was hoping that nested queries would allow 366 buckets per query but it appears that this is the limit for all queries.
I have a number of Terminal Services servers that I’m collecting the session counter information for via the nri-perfmon integration so I can sum the sessions per host for a total per timeseries and then get the timeseries max.
Data is collected from each host every 5 minutes so the 5 minute timeseries with the maximum sessions total is required to give the peak sessions per day.
I have the following query that will give this maximum for a 24 hour period for the 5 minute timeseries.
SELECT max(Total) FROM (SELECT sum(TotalSessions) as Total FROM TerminalServices TIMESERIES 5 minutes) SINCE 1 day ago
However what I really want is a graph for 30 days that will show this daily (24 hour) max, so a query like below:
SELECT max(Total) FROM (SELECT sum(TotalSessions) as Total FROM TerminalServices TIMESERIES 5 minutes) SINCE 30 day ago TIMESERIES 24 hours
I was hoping that the nested “inner” query would get all 5 minute timeseries sum (288 buckets) independently of the “outer” query within its 24 hour timeseries.
So the “inner” query would return all 5 minute timeseries session totals for each timeseries of the “outer” query.
Hence the !outer” query would then return the maximum 24 hour session total for each of the previous 30 days it is set to run for.
However it appears that the total 366 buckets is shared for all queries as the maximum since days ago I can set for the “outer” query is 47 hours, setting 48 exceeds the 366 bucket limit.
I can’t see how what I want is possible without having to perform the “inner” queries aggregation outside of New Relic and then ingest it back in to a new custom event table to then run the “outer” query against.
Is there a way to use nested queries to get the required outcome?
I really don’t want to have to start running additional scripts outside of New Relic when all the data I require is already there.