topic How to calculate difference between multiple fields? in Splunk Search

How to calculate difference between multiple fields?

mistydennis — Tue, 06 Dec 2022 14:36:37 GMT

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.

Re: Calculate difference between multiple fields

ITWhisperer — Tue, 06 Dec 2022 08:44:28 GMT

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

Re: Calculate difference between multiple fields

mistydennis — Mon, 05 Dec 2022 23:04:23 GMT

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.

Re: Calculate difference between multiple fields

PickleRick — Mon, 05 Dec 2022 23:44:49 GMT

Should be

values(total)

Simple typo - eaten "s" 😉

Re: Calculate difference between multiple fields

mistydennis — Tue, 06 Dec 2022 13:32:40 GMT

Thanks, @PickleRick for the typo fix 🙂

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

Re: Calculate difference between multiple fields

ITWhisperer — Tue, 06 Dec 2022 13:54:48 GMT

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

Re: Calculate difference between multiple fields

PickleRick — Tue, 06 Dec 2022 13:58:38 GMT

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.