Splunk Search

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

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 lifeLength=(LatestTime - EarliestTime)/60/60/24
``````

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.

Tags (5)
1 Solution
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
``````
Revered Legend

If you're current Output is this

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

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

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
``````
Communicator

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

That gives me everything I need super easily.

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.

Get Updates on the Splunk Community!

Optimize Cloud Monitoring

TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...