Top X tags over time

1

Hi there,

I’m new to InfluxDB. I’m using it to store ntopng timeseries data.

ntopng writes a measurement called “asn:traffic” that stores how many bytes were sent and received for an ASN.

> show tag keys from "asn:traffic"
name: asn:traffic
tagKey
------
asn
ifid
> show field keys from "asn:traffic"
name: asn:traffic
fieldKey   fieldType
--------   ---------
bytes_rcvd float
bytes_sent float
>

I can run a query to see the data rate in bps for a specific ASN:

> SELECT non_negative_derivative(mean("bytes_rcvd"), 1s) * 8 FROM "asn:traffic" WHERE "asn" = '2906' AND time >= now() - 12h GROUP BY time(30s) fill(none)
name: asn:traffic
time                non_negative_derivative
----                -----------------------
1550294640000000000 30383200
1550294700000000000 35639600
...
...
...
>

However, what I would like to do is create a query that I can use to return the top N ASNs by data rate and plot that on a Grafana graph. Sort of like this example that is using ELK:

39966490-d8d6032e-56ac-11e8-8784-b9903855d4f3
39966490-d8d6032e-56ac-11e8-8784-b9903855d4f3.png2032×1192 413 KB

Does anyone have any ideas on how I might be able to achieve that?

Thank you!

2

I’ve tried a few variants from posts here and elsewhere that suggest using top(), but I haven’t been able to get what I’m after. For example, this query I think gets me closer to where I want to be, but there are no values in asn:

> select top(bps,asn,10) from (SELECT non_negative_derivative(mean(bytes_rcvd), 1s) * 8 as bps FROM "asn:traffic" WHERE time >= now() - 12h GROUP BY time(30s) fill(none))
name: asn:traffic
time                top                asn
----                ---                ---
1550299860000000000 853572800
1550301660000000000 1197327200
1550301720000000000 1666883866.6666667
1550310780000000000 674889600
1550329320000000000 20979431866.666668
1550332740000000000 707015600
1550335920000000000 2066646533.3333333
1550336820000000000 618554933.3333334
1550339280000000000 669084933.3333334
1550340300000000000 704147333.3333334
>

Thinking then that perhaps the sub query needs to select asn also, however that proceeds an error about mixing queries:

> select top(bps,asn,10) from (SELECT asn, non_negative_derivative(mean(bytes_rcvd), 1s) * 8 as bps FROM "asn:traffic" WHERE time >= now() - 12h GROUP BY time(30s) fill(none))
ERR: mixing aggregate and non-aggregate queries is not supported
>

Anyone have any thoughts on a solution?