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
Legend

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
---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

renjith_nair
Legend

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
---
What goes around comes around. If it helps, hit it with Karma 🙂

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!

Fueling your curiosity with new Splunk ILT and eLearning courses

At Splunk Education, we’re driven by curiosity—both ours and yours! That’s why we’re committed to delivering ...

Splunk AI Assistant for SPL 1.1.0 | Now Personalized to Your Environment for Greater ...

Splunk AI Assistant for SPL has transformed how users interact with Splunk, making it easier than ever to ...

Unleash Unified Security and Observability with Splunk Cloud Platform

     Now Available on Microsoft AzureOn Demand Now Step boldly into the AI revolution with enhanced security ...