topic Re: Top 10 Values Timeseries with Additional 'Other' Aggregate Category in DQL

Top 10 Values Timeseries with Additional 'Other' Aggregate Category

jegron — Thu, 11 Jan 2024 14:33:46 GMT

Hi,

I would like to construct a timeseries that includes a set number of the highest values, plus an additional aggregated category named 'Other.' This 'Other' category should represent the sum of all the remaining values not included in the set limit of highest values :

How can I do that ?

fetch logs
| makeTimeseries count(default:0), by:{src_ip}
| limit 10

Thanks!

Julien

Re: Top 10 Values Timeseries with Additional 'Other' Aggregate Category

krzysztof_hoja — Thu, 11 Jan 2024 20:05:51 GMT

After producing timeseries for all IPs
- you need to collect them in single array,
- sort it by chosen order (I selected sum of all value in timeseries as a ordering criteria, kept in o field of the record)
- remember the 10th value from the array in fields top
- expand and summarize again by srcaddr, but it will be replaced with "Other" word when value of o field is lower then remembered threshold (top)

fetch logs | filter ipIn(srcaddr,"10.179.22.0/24") | makeTimeseries {c=count()}, by:{srcaddr}, interval:5m | summarize { d=collectArray( record(o=arraySum(c), srcaddr, c)) }, by:{timeframe, interval} | fieldsAdd d=arraySort(d, direction:"descending" ) | fieldsAdd top=d[9][o] | expand d | fieldsAdd srcaddr = if( isTrueOrNull(d[o]>=top), d[srcaddr], else:"Other") | summarize c=sum(d[c][]), by:{srcaddr, timeframe, interval}

Just note: this can give you more then 10 top timeseries if 11th element has the same value of o as 10th, etc....

Kris

Re: Top 10 Values Timeseries with Additional 'Other' Aggregate Category

jegron — Mon, 15 Jan 2024 09:41:18 GMT

Hi,

Thanks !

I don't understand why there is the "C" value here. What does it mean ?

Also Does Dynatrace plan to release an "useother" option for Timeseries like competitor?

Example :

Syntax: useother=<bool>

Description: You specify which series to include in the results table by using the <agg>, <limit>, and <where-clause> options. The useother option specifies whether to merge all of the series not included in the results table into a single new series. If useother=true, the label for the series is controlled by the otherstr option.

Default: true

If not I can write an RFE 🙂

Julien

Re: Top 10 Values Timeseries with Additional 'Other' Aggregate Category

krzysztof_hoja — Wed, 17 Jan 2024 07:27:13 GMT

Apparently there are record where srcaddr is null. When the dimension (name of timeseries) is null, the timeseries field name (c) is used in chart legend. To confirm this, you can display your data in form of table or record list.

Also I agree with you that 'useother' option would be very useful. Moreover such extension would be needed not only as a part makeTimeseries, but also as a part of timeseries for out native metrics. Please go ahead with RFE.
On the other side, DQL is flexible and powerful enough so such data manipulations are possible before implementation of specialized features happens.