Splunk Search

How to edit my search to append a total average column for a chart?

splunkin11
Path Finder

I can't seem to find a solution for this. I've created a chart over a given time span. I've been able to add a column for the totals for each row and total averages at the bottom but have not been able to figure out how to add a column for the average of whatever the selected time span would be.
What I have so far ....

index=*
| bucket _time span=1d  
|convert ctime(_time) AS date timeformat="%Y/%m/%d" 
| chart count over host by date 
| addtotals
| appendpipe [stats avg(* ) as *]
0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

index=*
 | bucket _time span=1d  
 |convert ctime(_time) AS date timeformat="%Y/%m/%d" 
 | stats count by host date 
 | appendpipe [| stats avg(count) as count by host | eval date="Host_Avg" ]
 | xyseries host date count
 | addtotals
 | appendpipe [stats avg(* ) as *]

Final

index=*
  | bucket _time span=1d 
  |convert ctime(_time) AS date timeformat="%Y/%m/%d" 
  | stats count by host date 
  | appendpipe [| stats avg(count) as count by host | eval date="Host Avg" ]
  | xyseries host date count
  | addtotals | eval Total=Total-'Host Avg'
  | appendpipe [stats avg(*) as * | foreach * [eval "<<FIELD>>"=round('<<FIELD>>') ] ]

View solution in original post

somesoni2
Revered Legend

Give this a try

index=*
 | bucket _time span=1d  
 |convert ctime(_time) AS date timeformat="%Y/%m/%d" 
 | stats count by host date 
 | appendpipe [| stats avg(count) as count by host | eval date="Host_Avg" ]
 | xyseries host date count
 | addtotals
 | appendpipe [stats avg(* ) as *]

Final

index=*
  | bucket _time span=1d 
  |convert ctime(_time) AS date timeformat="%Y/%m/%d" 
  | stats count by host date 
  | appendpipe [| stats avg(count) as count by host | eval date="Host Avg" ]
  | xyseries host date count
  | addtotals | eval Total=Total-'Host Avg'
  | appendpipe [stats avg(*) as * | foreach * [eval "<<FIELD>>"=round('<<FIELD>>') ] ]

splunkin11
Path Finder

Great!! Your final answer fixes everything - thanks!

0 Karma

splunkin11
Path Finder

Almost there. I found a way to add the correct total for each column with another appendcols but noticed that the final totals were lost - with the |appendpipe [stats avg(* ) as *]

Here's what I have now but missing the final totals:

index=
| bucket _time span=1d

|convert ctime(_time) AS date timeformat="%Y/%m/%d"
| stats count by host date
| appendpipe [| stats avg(count) as count by host | eval date="Host Avg" ]
| xyseries host date count
| appendcols [search index=
| stats count as "Total Count" by host ]
| appendpipe [stats avg(* ) as *]

0 Karma

somesoni2
Revered Legend

It would be better (efficient) to just add following after addtotals and before last appendpipe. (to avoid querying the host data again.

| eval Total=Total-'Host Avg'

Full search

index=*
| bucket _time span=1d 
|convert ctime(_time) AS date timeformat="%Y/%m/%d" 
| stats count by host date 
| appendpipe [| stats avg(count) as count by host | eval date="Host Avg" ]
| xyseries host date count
| addtotals | eval Total=Total-'Host Avg'
| appendpipe [stats avg(*) as * ]
0 Karma

splunkin11
Path Finder

Would there be a way to round the final totals from using [stats avg(*) as * ] ?

0 Karma

somesoni2
Revered Legend

Of course 🙂

 index=*
 | bucket _time span=1d 
 |convert ctime(_time) AS date timeformat="%Y/%m/%d" 
 | stats count by host date 
 | appendpipe [| stats avg(count) as count by host | eval date="Host Avg" ]
 | xyseries host date count
 | addtotals | eval Total=Total-'Host Avg'
 | appendpipe [stats avg(*) as * | foreach * [eval "<<FIELD>>"=round('<<FIELD>>') ] ]
0 Karma

splunkin11
Path Finder

Wowzers! That's something I've never seen or heard of before ... you're awesome!

0 Karma

somesoni2
Revered Legend

Glad to be of help. If there are no follow-up questions, you can close the question by accepting this answer. I'll update my answer to reflect the final version of search.

0 Karma

splunkin11
Path Finder

.. one small addition if you don't mind .. is there also a way to add a label for the last totals row produced from :
| appendpipe [stats avg(*) as * | foreach * [eval "<>"=round('<>') ] ]

0 Karma

somesoni2
Revered Legend

If by label you mean a value of say "Average by Host" (or something similar) on the 'host' field for very last row, yes. Just add this eval after the foreach command.

....
 | appendpipe [stats avg(*) as * | foreach * [eval "<<FIELD>>"=round('<<FIELD>>') ]  | eval host="Average by Host"]
0 Karma

splunkin11
Path Finder

cool - thank you sir!

0 Karma

splunkin11
Path Finder

I see - it's including the avg(count) # into the total count but how can I exclude the avg count from the total?

0 Karma

splunkin11
Path Finder

.. oops .. my fault ... getting results now and the avg. is correct and in a new column but the total column is now showing an incorrect total for the time span for each row.

0 Karma

splunkin11
Path Finder

hmmm .... returns 0 results

0 Karma
Get Updates on the Splunk Community!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...