Feature Idea: Timeseries queries with sparse data points

I’d like to use Insights to monitor our Microsoft Azure auto-scaling rules, and I’ve written a tool that creates a custom Insights event whenever one of our Azure webapps scales up or down.

I’m trying to use NRQL to plot a timeseries graph with the number of instances a particular webapp is using over time. The data looks roughly like this:

timestamp    appName   newInstanceCount  scaleDirection
00:10        site1     2                 ScaleUp
00:15        site2     4                 ScaleDown
04:00        site1     3                 ScaleUp
05:00        site1     2                 ScaleDown

I’m using a NRQL query like SELECT max(newInstanceCount) FROM AutoScaleAction SINCE 1 DAY AGO WHERE appName = 'site1' TIMESERIES AUTO to render a line chart with the value of newInstanceCount over time.

The problem I’m running into is that when using TIMESERIES, periods during which there is no data are rendered as zero on the graph. Since I only insert an event when the value of newInstanceCount changes, the line drops to zero between 00:10 and 04:00. I’d like the line chart to display the last-known value for periods where there is no data, rather than zero.

I’ve looked at the NRQL reference but I can’t seem to figure out a way to graph the data the way I want – Is this sort of scenario supported? What am I doing wrong here?

New Relic Edit

We take feature ideas seriously and our product managers review every one when plotting their roadmaps. However, there is no guarantee this feature will be implemented. This post ensures the idea is put on the table and discussed though. So please vote and share your extra details with our team.

Hey @brantb,

Nice job! You’re not doing anything wrong here. Unfortunately we don’t support this specific scenario at this point in time. With that said, I’d like to submit a feature request for you so we can support the behavior in the future. While I don’t have an immediate workaround for you, I will ask that you are notified if this feature request is implemented. Hopefully that will help out. Let me know if you have any other questions regarding this.

Any word on that feature request?

How about doing a "Select uniqueCount(host) from Transaction where appName = ‘site1’ TIMESERIES Auto since 1 Day AGO limit 1000. That will only report on hosts doing something and will show you the rise and fall.

I have a similar problem. Is there any news on this topic?

I’d also like to know if there’s any news on this topic. I think treating no data points as 0 should be configurable and that NULL data points should join the line in the chart to the last non-NULL data point. My use case is having a number of custom events where we’re recording details about each stage of an applications deployment lifecycle. I can’t figure out a way to get this to work in the way that I want.

We still need this… We need to specify number of minutes between data points where the charts just connect the most recent points instead of going to zero or just simply give a NRQL keyword that means missing data points are not added to the charts… Dashboards look horrible because of this. We should not have to pull data from the API and roll our own charts to fix something this simple.

ugly.jpg1104×860 171 KB

This would be great. We’re in the same boat and have a lot of charts that would really benefit from this. +1 from us. Is there a proper way to ‘upvote’ this aside from clicking the above to get it prioritized in planning for New Relic?

It is now July 2019…

WE STILL NEED THIS FEATURE

In my case I specifically choose not to use newrelic if I know a chart is not going to have data nearly every minute. Anything that has less data… I roll my own dashboards.

…crickets

Thanks for the feedback everyone! And sorry this has been taking so long. I’ll get this escalated to the Insights team to try get an update. If I get anything I’ll follow up here

I was going to create a separate topic, but this really describes the issue I’ve been having. The Fastly-to-Insights container (https://hub.docker.com/r/newrelic/fastly-to-insights) is especially bad this way, since it reports data only once every three minutes, making the data look spiky even if it’s really completely smooth.

The only workaround I’ve found is to use TIMESERIES 6 MINUTES, but that only works because I know there’s a regular 3-minute period; it doesn’t help if the gaps are irregular. I was hoping a WHERE HITS != 0 or WHERE SUM(HITS) !=0 clause would work to filter out the no-data, but it doesn’t, in the first case because HITS is never equal to zero in the input data, only in the representation, and in the second case because it’s a syntax error (I think).

Please fix this issue! No data is not equal to a datapoint of “zero”.

Hey @jdweiner - Thanks for that - I’ll get your +1 added here.

Has there been any update since the last comment? This would really help our custom dashboards look better. This is making the dashboards look like spiky and not appealing. Also, it somewhat forces us to use other charts to represent data that should be represented as timeseries/line charts which hinders the story perspective of the application. Please try to implement this feature as soon as possible. Thank you.

Hey @marquis.kimble - I don’t yet have an update - but your thoughts have just been added in as a +1.

Four years… I wish I had kept track of how many times I have been asked “can you fix these ugly saw-tooth charts?”

Highcharts (which New Relic used at some point), calls this connectNulls. I do not know if NR switched to server side generated Highcharts or some other thrid party libraries or if you rolled all your own server side SVG line and path generation, but what we are asking here should be very simple.

Please give us the option so that charts do not render gaps between null data points (example below) and instead render the line and path of the chart directly between the given data points.

image.jpg1085×366 129 KB

Thanks for sharing your feedback @rguess - I’ve got that submitted to the product team.

I found, thanks to NR staff’s help with an unrelated issue, something that might help a little. In Alerts (and only Alerts, sorry), when you’re setting up a NRQL alert, under Thresholds, choose “When the sum of query results is”. Then, in “at least once in X minutes”, choose a value of X that covers at least one expected data point.

Again, this is probably more helpful if you know that the period is regular, but at least it helps smooth things out.

https://docs.newrelic.com/docs/alerts/new-relic-alerts/defining-conditions/create-alert-conditions-nrql-queries#sum

Thanks for sharing that @jdweiner