topic Re: Complete a timechart with a total column in Splunk Search

Complete a timechart with a total column

Emilien — Mon, 28 Sep 2020 11:45:59 GMT

Hello,

I got a timechart with 16 values automatically generated. But I want to have another column to show the sum of all these values.

This is my search :

index="jdbc"
sourcetype="BD_PANDORA_PROD" |
timechart span=1mon limit=16
eval(max(nbr_teams)) by directorate |
rename _time AS Time | eval
Time=strftime(Time, "%B")

http://imageshack.us/photo/my-images/502/totalresult2.jpg/

Have a look at the only result I was able to make :

index="jdbc"
sourcetype="BD_PANDORA_PROD" |
timechart span=1mon limit=16
eval(max(nbr_teams)) by directorate |
append [search index="jdbc"
sourcetype="BD_PANDORA_PROD" |
timechart
eval(round((sum(nbr_teams)/(count(nbr_teams)/16)),0))
AS TOTAL by Time ]

| rename _time AS Time | eval
Time=strftime(Time, "%B")

But this is not what I expected…
http://imageshack.us/photo/my-images/856/totalresult1.jpg/

Thank you for helping me.

Emilien

Re: Complete a timechart with a total column

sowings — Wed, 02 May 2012 15:00:26 GMT

Just as an aside, you can do "convert timeformat=%B ctime(_time) AS Time" instead of the rename / eval.

Re: Complete a timechart with a total column

sowings — Wed, 02 May 2012 15:03:48 GMT

In the past I've used eventstats to calculate a total field for a search. Unfortunately, the place to apply it doesn't jump out at me from your example.

Maybe something like

index="jdbc"
sourcetype="BD_PANDORA_PROD"  |
stats max(nbr_teams) AS max by directorate |
eventstats sum(max) AS Total | 
timechart ...

Re: Complete a timechart with a total column

Emilien — Mon, 28 Sep 2020 11:46:16 GMT

I'm sorry, I did not succeed in this way.
I'm close to the goal with :

index="jdbc" sourcetype="BD_PANDORA_PROD"

| timechart span=1mon limit=16 eval(max(nbr_teams)) by directorate
| appendcols
[search index="jdbc" sourcetype="BD_PANDORA_PROD" | timechart span=1mon eval(round((sum(nbr_teams)/(count(nbr_teams)/16)),0)) AS TOTAL by Time ]
| rename _time AS Time | eval Time=strftime(Time, "%B")

But this new column is called "Null" and stay between others column instead of being at the end...
But I think, they is a better solution for my huge eval expression

Re: Complete a timechart with a total column

Emilien — Mon, 28 Sep 2020 11:46:21 GMT

Stop searching, this is very simple, just add "| addtotals"

it looks like this code :

index="jdbc"
sourcetype="BD_PANDORA_PROD" |
timechart span=1mon limit=16
eval(max(nbr_teams)) by directorate |
addtotals | rename _time AS Time |
eval Time=strftime(Time, "%B")

And it's done !

Enjoy !