Splunk Search

## Streamstats sum that doesn't go below zero

Path Finder

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.

Tags (5)
1 Solution
Super Champion

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 minCURRENT`by 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

Super Champion

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 minCURRENT`by 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

Communicator

As I detailed at https://community.splunk.com/t5/Splunk-Search/Case-open-count-and-trend-history/m-p/513894/highlight..., 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.

Path Finder

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!

Super Champion

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 🙂

Get Updates on the Splunk Community!

#### .conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

#### Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

#### Troubleshooting the OpenTelemetry Collector

In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...