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
Revered Legend

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!

Enterprise Security Content Update (ESCU) v3.54.0

The Splunk Threat Research Team (STRT) recently released Enterprise Security Content Update (ESCU) v3.54.0 and ...

Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...

New Learning Videos on Topics Most Requested by You! Plus This Month’s New Splunk ...

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...