Splunk Search

untable for multiple aggregate stats

sistemistiposta
Path Finder

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

0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

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
Happy Splunking!

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

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
Happy Splunking!

sistemistiposta
Path Finder

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

0 Karma

sistemistiposta
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...