topic Re: How to get a stats sum of a column available in every row? in Splunk Search

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

Amohlmann — Mon, 20 Jul 2015 07:25:03 GMT

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.

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

mrobichaud_splu — Mon, 20 Jul 2015 15:43:12 GMT

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

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

somesoni2 — Mon, 20 Jul 2015 15:55:10 GMT

If you're current Output is this

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

What is your expected output?

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

Amohlmann — Mon, 20 Jul 2015 22:02:01 GMT

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

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

Amohlmann — Mon, 20 Jul 2015 22:09:53 GMT

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.

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

Amohlmann — Tue, 21 Jul 2015 03:16:48 GMT

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.