Only display values over a certain threshold

#1

I have a user activity graph that has a line for each user logged into the system. I’m only interested in the ones that are doing a lot of requests though, i.e. that are very active.

I therefore want to put a threshold on the graph so it ONLY displays users/data points above say 20 opm. Otherwise my legend gets clogged up by hundreds of users doing 1-3 opm.

Is there a way to do this? The threshold feature just seems to draw lines on the graphs and be used for alerting, which is not what I want.

Grafana 4.5.2

#2

Which data source are you using? With Graphite you can use minimumAbove to show only series with a minimum above a given value, there is also averageAbove which is the same thing but uses the series average rather than the minimum.

#3

I’m using influxdb data source.

#4

Anyone have any ideas?

In fact another solution may be to only show the lines with the highest values.

The root problem is that if I graph the last 15 mins of data it shows correctly, as it’s only trying to graph maybe 50 lines, but if I put it out to 3 hours there are then thousands of lines that it is trying to draw in theory. In practice is seems to give up after a set number, so the lines you’re actually interested in, the ones showing high values, are omitted completely from the graph.

#6

An example: Below is the graph with one hour shown which shows me useful data:

but this is the same graph just changes to 3 hours:

As you can see the useful information is omitted and this implies that there’s no load on the system.

#7

bump this because urgently need an answer to get valid data. Would suspect this would be a common issue.

#8

Can you share the query you’re using? It would also be helpful if you could take the same screenshots but first click on the name of the series that shows high usage on the 1-hour graph so that only that one is shown for both time-scales.

#9

The query is:
SELECT count(“amount”) FROM “AUTH” WHERE (“gateway” =~ /^$gateway$/ AND “provider” =~ /^$Provider$/) AND $timeFilter GROUP BY time(1m), “terminalNumber” fill(null)

The one that shows high on the 1 hour graph doesn’t show at all on the 3 hour graph, that’s the main problem! I think it’s because its just drawing the first 55 lines and then crapping out, and it hasn’t got to that value yet.

I effectively need it to show the top 55 by value, not the first 55 in whatever order it finds them.

#10

This might be helpful:

https://stackoverflow.com/questions/41187962/influxdb-select-top-n-results-from-a-group-by-tag

#11

That solution is fine for tabulated data, but it’s not that simple for time series data. I’m trying to create the query now without much luck.

#12

Here are two methods I’ve tried:

SELECT count("amount") FROM "AUTH" WHERE ("terminalNumber" =~ (SELECT "terminalNumber" from (SELECT top("amount",20), "terminalNumber" FROM "AUTH" WHERE time >= now() - 1h))) AND $timeFilter GROUP BY time(1m), "terminalNumber" fill(null)
This complains because you can’t use a subquery like that.

SELECT amount, terminalNumber FROM (SELECT top("amount",20) as "amount" FROM "AUTH" WHERE $timeFilter GROUP BY "amount") WHERE $timeFilter GROUP BY "terminalNumber" fill(null)
This seems closer to me but still doesn’t seem to give me a clean graph. Single points are graphed for some terminalNumbers that do not show a line at all, so are not visible.

#13

Still no luck with this. Gotta bump it again. Any help out there?

#14

SOLUTION: It’s quite simple, actually. In your metrics, just add: “AND value > 20”. Check the screenshot attached.

Hope this helps!

#15

How do you add an AND ? can’t seem to do this?