topic Re: Streamstats sum that doesn't go below zero in Splunk Search

Streamstats sum that doesn't go below zero

ALXWBR — Tue, 20 Aug 2019 12:35:47 GMT

This is the second time I have come across this problem but I really can't seem to find any answer anywhere. I need to streamstats sum a number field that has positive and negative integers, but I don't want the answer to go below zero. Fore example:

If the FIELD column represents the values I want to sum, then currently a streamstats sum(FIELD) command produces results in the CURRENT column, where as, I need it to calculate as the DESIRED column.

This is because I am reviewing capacity usage and I can't have it negatively used.

PLEASE HELP!!!

Re: Streamstats sum that doesn't go below zero

DavidHourani — Tue, 20 Aug 2019 13:48:11 GMT

Hi @ALXWBR,

Wow that's a real mind twister... Made me scratch my head for this one...

At first I thought that using reset_after or reset before option from streamstats would help, but they don't make much sense since your case doesn't really follow a fixed streamstats logic. It's really a maths problem.

So here's how I think you can solve it for negative values :

Step 1: Run a search to get the FIELD and current column that you have in your table above.

Step 2: Generate a new field containing the running minimum value of of your CURRENT field. This means anytime there's a new minimum for CURRENT it will be saved, this field will be used as the "real zero" field in step 3. You can do that using |streamstats min(CURRENT) as minCURRENTby this step you have "FIELD", "CURRENT" and "minCURRENT" lined up.

Step 3: Your minCURRENT is now the real 0 for all negative values, so anytime your anytime the minCURRENT is equal to CURRENT you should replace it with 0. And any time CURRENT is negative and larger than minCURRENT you should replace it with the diff of both values.
This translates to that : ....| eval DESIRED= case(CURRENT== minCURRENT, 0, CURRENT<0 AND CURRENT>minCURRENT, CURRENT-minCURRENT, CURRENT>0 AND CURRENT< FIELD, FIELD, 1=1 , CURRENT)
That last part : CURRENT>0 AND CURRENT< FIELD, FIELD, 1=1 , CURRENT handles values when CURRENT is positive.

Let me know if you need more details or help, this isn't very easy I have to admit.

Cheers,
David

Re: Streamstats sum that doesn't go below zero

ALXWBR — Tue, 20 Aug 2019 14:06:42 GMT

David....there are no words to describe how grateful I am!

That appears to have done the trick! Thank you so much! I've been racking my brain over that one for hours. I was all over the reset_after etc. too!

Have a great day!

Re: Streamstats sum that doesn't go below zero

DavidHourani — Tue, 20 Aug 2019 19:00:51 GMT

You're most welcome ! It's really tricky... felt like solving one of those brain teasers lol

Keep a lookout for the change from negative to positive though, not sure how well that case is handled so let me know if you notice anything strange about it 🙂

Re: Streamstats sum that doesn't go below zero

benhooper — Thu, 13 Aug 2020 14:54:27 GMT

As I detailed at https://community.splunk.com/t5/Splunk-Search/Case-open-count-and-trend-history/m-p/513894/highlight/false, this is a great solution but has one problem: it incorrectly handles the field "CURRENT" having a value of 0.

To work around this, I simply used something like "| append [| makeresults | eval CURRENT = -10000] | reverse" which sets a baseline so low that CURRENT should never even get to 0 but everything else still works.