untable for multiple aggregate stats

[sistemistiposta]

Hello,

I would like to plot an hour distribution with aggregate stats over time. For instance, I want to see distribution of average value over hour regarding 5 minutes sum of a field. I proceed with an instance to be clear:

index="something"
| timechart span=5m
                      sum(nrcpt) as "Dest in 5m"
                  by sasl_username
                  limit=1 useother=f
                  | fillnull | untable _time sasl_username "Dest in 5m"
| eval date_hour=strftime(_time,"%1H")
| chart avg("Dest in 5m")  over date_hour by sasl_username

timechart let me to fill null values with 0, to obtain a desired average over time.
But let suppose I want more aggregate statistics. The followin example doesn't work:

index="something" flow=outbound
| timechart span=5m
                      sum(nrcpt) as "Dest in 5m"
                      count(sasl_username) as "Msg in 5m"
                  by sasl_username
                  limit=1 useother=f
                  | fillnull | untable _time sasl_username "Dest in 5m" "Msg in 5m"
| eval date_hour=strftime(_time,"%1H")
| chart avg("Dest in 5m")  avg("Msg in 5m") over date_hour by sasl_username

Ouch! Because untable supports only one serie.

So, to obtain the result, I have to run a complex search, such as

index="something"
| timechart span=5m
                      sum(nrcpt) as "Dest in 5m"                      
                  by sasl_username
                  limit=1 useother=f
                  | fillnull | untable _time sasl_username "Dest in 5m"
| append
  [search index="something"
    | timechart span=5m
                         count(sasl_username) as "Msg in 5m"
                     by sasl_username
                     limit=1 useother=f
                     | fillnull | untable _time sasl_username "Msg in 5m"]
| eval date_hour=strftime(_time,"%1H")
| chart max("Dest in 5m") max("Msg in 5m") OVER date_hour BY sasl_username

Why does untable support only one serie? Is there a better way to write the last search?

Thank you very much
Warm Regards
Marco

[renjith_nair · Accepted solution]

Hi @sistemistiposta,

Does this work for you ?

 index="something"
 | timechart span=5m sum(nrcpt) as "Dest in 5m",count(sasl_username) as "Msg in 5m" by sasl_username useother=f
 | eval date_hour=strftime(_time,"%1H")
 | stats avg(*)  as * by date_hour

[sistemistiposta]

Ouch! In this way I loose the aggregation "by sasl_username" in trellis view of the chart.

[sistemistiposta]

Stunning!
It was so simple... I forgot the power of wildcards! 😉
Thank you very much @renjith.nair