Hi im using telegraf to get winperfmon counters form various machines. The data is getting stored in inflxud db which is then presented.hooked to grafana on the front end side.
Im trying to only show my top 10 hosts by CPU or memory for example but struggling to do so.
Is this something i have to take care in influx or can be done in grafana easily.
Given the value is not a tag so i cannot use a where clause.
Hi @zaeem_mansoor,
Would top() work for you? https://docs.influxdata.com/flux/v0.x/stdlib/universe/top/
Hi sam im not using flux at the moment so not sure how to use that. I beleive flux is in the alpha release at the moment which im not using. Ideally all my data points are written to influx db on a 10 second interval. I have 100 servers where metrics like cpu and memory are recorded and others
I can easily graph them on grafana but when it comes to showing top n by cpu, im struggling there
Sorry im a newbie here so dont know much about Flux at all.
This is what top n gives me on grafana . Is there a way to use sub queries and what would be the syntax for it.
Oh okay, this post was tagged as InfluxDB 2 which would be using Flux by default. I think the InfluxQL implementation of top() (InfluxQL functions | InfluxDB OSS 1.7 Documentation) should still work for you.
Side note: if you’re using OSS 1.7+ or Enterprise 1.8+, you can enable Flux there as well. Note that this should be for testing/learning only. Flux is in “feature stage” and, therefore, has not gone through optimizations yet which means it hasn’t been deemed production-ready yet.
sorry apologies. yes using open source version. Will check InfluxQL implementation to see if it works
Im trying to use the below query
SELECT percentile(“Percent_Processor_Time”, 95) FROM “win_cpu” WHERE $timeFilter GROUP BY time($__interval), “host” fill(none)
From here i should be able to limit it to the top 10 host where Percent_Processor_Time >70% but cant seem to find the required sub query syntax with Top clause
I don’t think it makes sense to use top() on a percentile() as that returns only one value. Top would simply return the same value as percentile() did.
@Sam agreed but if you want to get top 10 out of 100 servers and plot there points on a graph, this query wont do the job. (unless im missing something)
The use case for me is that i have 100 servers that are plotted for a given interval.
20 of them have their Percent_Processor_Time>70%. I want to only get those servers where Percent_Processor_Time breached that threshold, get the host names and then plot them on the graph
Let me experiment a bit more. Seems like i might need to use continuous query or some other way to present that data
Below is just for illustration purposes but graphically i wont to plot something similar for Percent_Processor_Time for top 10 consumers/hosts where Percent_Processor_Time >70%
@Sam Something like below seems to be working but getting it to plot on grafana is perhaps not supported but on a graph. Thanks alot for your assistance on the matter
select PercentProcessorTime,host from (select TOP(Percent_Processor_Time,1) as PercentProcessorTime,host from win_cpu where time > now() -1m and Percent_Processor_Time > 70 group by host)
You’re right. Bottom query in the below screenshot should work (showing output of nested query to make sense of it):
Flux would look like this:
Can you see those images okay?
@Sam yes i can see the images. Flux seems much easier and cleaner. Waiting for the next version to get hands on it. thanks for your help. For now i think i’m good
Hi, did you manage to plot it using Grafana with InfluxQL?
Is there a way to use Flux with Influx 1.7 and Grafana?
Hi @Sam, I need help with grafana and influxDB.
I have a dashboard with this graph, which shows all of my Access Points and the amount of users connected to each of them. I have 294 APs in this environment but I only need to be shown in this graph the TOP 10. In other words, I need only the first 10 APs that contains de majority of devices connected by time.
How can I get this? I’ve tried the top () but I couldn’t get any result.
I would appreciate some help.
I know this is over a year old, but I did find a potentially useful answer to this problem. The solution is essentially to split your query to find the top 10 away from your series query. You can do this using the following approach:
topTenHosts for the sake of example.SELECT host FROM (SELECT TOP(usage, 10), host FROM (SELECT SUM(value) AS usage FROM "cpu" WHERE ("core" = '_Total') AND $timeFilter GROUP BY "host"))
The above will sum the usage over the time period and give you the top hitters. There is a potential problem to solve in this if you have an inconsistent set of data points per host in the timespan… but that’s for another day.
Now you have the above variable containing your host list, in your series query within your WHERE clause you can use:
AND "host" =~ /^$topTenHosts$/
This then restricts your series to the values your top 10 variable contains; hope this helps.
