Solved: Re: Add the interval time to a timeseries array

gilgi · ‎01 Jan 2024

Hi,

I've came across the following requirement: Customer wants to display in a table the different values of a metric (let's say CPU), but also show the time for each metric.

We know that the following will create an array with the metric aggregated value per every minute:

timeseries cpu=avg(dt.host.cpu.usage), by:{dt.entity.host}, interval:1m

and I know that expand will take all array values and actually create seperate line for each one of them.

My question is, how (is it actually possible) can we display also the time interval per row?

Gil.

krzysztof_hoja · ‎02 Jan 2024

According to my knowledge there is no simple way to achieve it. My idea starts with having array of subsequent integer numbers of the length of at least the length of the timeseries (in the example below I used "last 30min" timeframe and 5min interval). Unfortunately there there is no such function to generate needed table, but if you can predict maximum number of elements and type in such array by hand then with use of iterative expressions we can calculate corresponding timestamps staring from timesframe[start] and incrementing by interval. Because iterative expression requires all tables in formula to have same length, the table with timestamps needs to be limited to the size of table with metric values. Also using iterative expression table of records containing timestamp and corresponding metric values can be created which can be expanded/flattened to give rows where each is for different host and timestamp

timeseries {cpu=avg(dt.host.cpu.usage)}, by: {dt.entity.host}, interval: 5m
| limit 3
| fieldsAdd time=array(0,1,2,3,4,5,6,7,8,9,10,11,12)
| fieldsAdd time=timeframe[start] + time[]*interval
| fieldsAdd time=arrayRemoveNulls(iCollectArray(if(time[]<timeframe[end],time[])))
| fields dt.entity.host, d=record(time=time[], cpu=cpu[])
| expand d
| fieldsFlatten d

According to this message after execution of timeseries for long period of time "Creating more than 1500 bins is deprecated and will soon no longer be supported. Adapt the interval or bins parameter to produce a maximum 1500 bins." timeseries command will have built in limitation, so there will be limit. Such query generates table longer then 2.6k of elements

data record(time=array(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,26,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40))
| fieldsAdd time2=arrayLast(time)+1+time[], time3=2*(arrayLast(time)+1)+time[], time4=3*(arrayLast(time)+1)+time[]
| fieldsAdd time=arrayConcat(time,time2,time3,time4)
| fieldsAdd time2=arrayLast(time)+1+time[], time3=2*(arrayLast(time)+1)+time[], time4=3*(arrayLast(time)+1)+time[]
| fieldsAdd time=arrayConcat(time,time2,time3,time4)
| fieldsAdd time2=arrayLast(time)+1+time[], time3=2*(arrayLast(time)+1)+time[], time4=3*(arrayLast(time)+1)+time[]
| fieldsAdd time=arrayConcat(time,time2,time3,time4)

By putting both queries together we get:

timeseries {cpu=avg(dt.host.cpu.usage)}, by: {dt.entity.host}, interval: 5m
| limit 3

| fieldsAdd fakeSource=1
| lookup [
data record(time=array(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,26,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40))
| fieldsAdd time2=arrayLast(time)+1+time[], time3=2*(arrayLast(time)+1)+time[], time4=3*(arrayLast(time)+1)+time[]
| fieldsAdd time=arrayConcat(time,time2,time3,time4)
| fieldsAdd time2=arrayLast(time)+1+time[], time3=2*(arrayLast(time)+1)+time[], time4=3*(arrayLast(time)+1)+time[]
| fieldsAdd time=arrayConcat(time,time2,time3,time4)
| fieldsAdd time2=arrayLast(time)+1+time[], time3=2*(arrayLast(time)+1)+time[], time4=3*(arrayLast(time)+1)+time[]
| fieldsAdd time=arrayConcat(time,time2,time3,time4)
| fieldsAdd fakeLookup=1
], sourceField:fakeSource, lookupField:fakeLookup, fields:{time}

| fieldsAdd time=timeframe[start] + time[]*interval
| fieldsAdd time=arrayRemoveNulls(iCollectArray(if(time[]<timeframe[end],time[])))

| fields dt.entity.host, d=record(time=time[], cpu=cpu[])
| expand d
| fieldsFlatten d

Kris