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!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...