Splunk Search

How to get a stats sum of a column available in every row?

Amohlmann
Communicator

I am creating a simple stats search. I am trying to work out that chance that a part will die over time. I consider a part dead if it has not sent me a status message in the last 20 minutes. This is what I have so far:

 Base search
| bucket _time span=1d
| stats latest(_time) AS LatestTime, earliest(_time) AS EarliestTime by PartID
| eval delayDays=(NOW()-LatestTime)/60/60/24
| eval dead=if((delayDays>20),1,0)
| eval lifeLength=(LatestTime - EarliestTime)/60/60/24
| chart count(eval(dead=0)) as "numberAlive", count(eval(dead=1)) as "numberDead" by lifeLength span=1
| eval sumOfDeadAlive=numberAlive+numberDead

The last piece of the puzzle for me is to create an eval that pretty much does the following:

eval SurvivalRate = (Sum(sumOfDeadAlive) - (numberDead)) / (Sum(sumOfDeadAlive))

The problem is I cannot work out how to get the sum of the sumOfDeadAlive column without destroying the chart currently as is.
If the total number of the sumOfDeadAlive is 150 I want to have access to that 150 in every row.

I hope this makes sense.

Thanks for the help.

0 Karma
1 Solution

mrobichaud_splu
Splunk Employee
Splunk Employee

The command name makes this unintuitive, but you can use eventstats to add this to every row...

| eventstats count(eval(dead=1)) as sumOfDead
| eventstats count(eval(dead=0)) as sumOfAlive

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

If you're current Output is this

lifeLength          numberAlive              numberDead             sumOfDeadAlive
1                             60                                90                                   150
2                             40                                 50                                   90
.......

What is your expected output?

0 Karma

Amohlmann
Communicator

Sorry I should have been a bit clearer. The main thing I cannot get is the sum of the sumOfDeadAlive column. So in your example I want another column or at least access to the number 240 (150 + 90).

0 Karma

mrobichaud_splu
Splunk Employee
Splunk Employee

The command name makes this unintuitive, but you can use eventstats to add this to every row...

| eventstats count(eval(dead=1)) as sumOfDead
| eventstats count(eval(dead=0)) as sumOfAlive

Amohlmann
Communicator

Ahh. Thanks your answer set me off in the right direction. I was trying to mess with streamstats instead of eventstats.

My Answer was to just slap in a |eventstats sum(sumOfDeadAlive)

That gives me everything I need super easily.

Amohlmann
Communicator

It will not let me post another question for some reason.
Do you know how to get the product of a streamstats instead of the sum?

I have a query that returns the survival rate over time. For instance:

Time                SurvivalRate     
1                        0.98                    
2                        0.96                     
3                        0.65
4                        1
.                          .
.                          .
.                          .

I would like to show a running survival rate that is like streamstats sum(survivalRate) but instead of adding the numbers in each new line, it multiplies it. So it would return something like this:

Time                SurvivalRate     RunningSurvivalRate
1                        0.98                    0.98
2                        0.96                    0.9408 (0.98 * 0.96)
3                        0.65                    0.61152 (0.9408 * 0.65)
4                        1                       0.61125 (0.61152 * 1)
.                          .
.                          .
.                          .

Am I using the wrong tool for this job? Is there a streamstats function that I am ignorant of?
Thanks for the assistance.

0 Karma
Get Updates on the Splunk Community!

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

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...