Splunk Search

How to calculate difference between multiple fields?

mistydennis
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)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
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

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
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
0 Karma

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

0 Karma

mistydennis
Communicator

Thanks, @PickleRick for the typo fix 🙂

 

After plugging in the search, I'm not getting expected results - everything is "0":Screenshot 2022-12-06 063204.jpg

 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

ITWhisperer
SplunkTrust
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
0 Karma

PickleRick
SplunkTrust
SplunkTrust

Should be

values(total)

Simple typo - eaten "s" 😉

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...