Splunk Search

How to calculate difference between multiple fields?

Communicator

Hi Splunk experts - I have an unusual math problem on my hands and I'm not sure how to deal with it. We are trying to prove how many tickets have been completed, so we are only counting the numbers that show improvement, not the numbers that show the addition of more tickets (following me?).

Here's the data:

report_date total
2022-11-07 4111
2022-11-08 3764
2022-11-09 3562
2022-11-10 3633
2022-11-11 3694
2022-11-14 7506
2022-11-15 12987
2022-11-16 15159
2022-11-17 14851
2022-11-18 14410
2022-11-21 6674
2022-11-22 5793
2022-11-23 5601

What I am trying to do is determine the difference between the "total" fields, but only when the count goes down. So for example, 11/7 - 11/9 show counts going down (4111-3562=549). But the numbers go up on 11/10, so we don't want to count those. And then the numbers go down again on 11/17, so I would add the difference between 11/16 and 11/17 to the previous 549.

I feel like I am making this more complicated that it needs to be. Help.

Labels (2)

• transaction

1 Solution
SplunkTrust

It looks like your field is called mytotal not total - try this

``````index=myindex
| stats count as total by report_date
| streamstats window=1 current=f values(mytotal) as previous_total
| eval diff=if(previous_total > mytotal, previous_total-mytotal, 0)
| streamstats sum(diff) as running_total_diff``````
SplunkTrust

Try something like this

``````| streamstats window=1 current=f values(total) as previous_total
| eval diff=if(previous_total > total, previous_total-total, 0)
| streamstats sum(diff) as running_total_diff``````
Communicator

When plugged into my search I get an error:

``````index=myindex
| stats count as total by report_date
| streamstats window=1 current=f value(total) as previous_total
| eval diff=if(previous_total > total, previous_total-total, 0)
| streamstats sum(diff) as running_total_diff``````

Error: Error in 'streamstats' command: The argument 'value(total)' is invalid.

Communicator

Thanks, @PickleRick for the typo fix 🙂

After plugging in the search, I'm not getting expected results - everything is "0":

Ultra Champion

You must have done something differently since you have different field names.

So you probably hit the "if" condition which cannot perform the substraction since it doesn't have one or both of the fields defined and so you land with the "else" value which is zero.

BTW, if report_date is the same as _time, you could use tstats to count the events - it would be way way faster.

SplunkTrust

It looks like your field is called mytotal not total - try this

``````index=myindex
| stats count as total by report_date
| streamstats window=1 current=f values(mytotal) as previous_total
| eval diff=if(previous_total > mytotal, previous_total-mytotal, 0)
| streamstats sum(diff) as running_total_diff``````
Ultra Champion

Should be

`values(total)`

Simple typo - eaten "s" 😉

Get Updates on the Splunk Community!

Devesh Logendran, Splunk, and the Singapore Cyber Conquest

At this year’s Splunk University, I had the privilege of chatting with Devesh Logendran, one of the winners in ...

There's No Place Like Chrome and the Splunk Platform

WATCH NOW!Malware. Risky Extensions. Data Exfiltration. End-users are increasingly reliant on browsers to ...