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!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...