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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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 ...