topic Re: Subtract two fields and create a timechart in Splunk Search

Subtract two fields and create a timechart

priyangshupal — Wed, 15 Sep 2021 07:10:02 GMT

I have two fields skill1 and skill2

skill2:

skill1:

Both these queries are producing results:

timechart span=1d count by skill1

timechart span=1d count by skill2

I want to create a separate variable skill which contains difference of skill1's values and skill2's values and create a timechart out of it.

I tried doing:

timechart span=1d count by skill1-skill2

But it's not working.

Any help would be appreciated!

Re: Subtract two fields and create a timechart

gcusello — Wed, 15 Sep 2021 07:14:41 GMT

hi @priyangshupal,

please try something like this:

index=your_index | bin span=1d _time | stats count(skill1) AS skill1 count(skill2) AS skill2 BY _time | eval diff=skill1-skill2 | table _time diff

Ciao.

Giuseppe

Re: Subtract two fields and create a timechart

priyangshupal — Wed, 15 Sep 2021 07:30:31 GMT

Hi @gcusello

This is not working, it's showing diff as 0.

Also, in the table I want the values too i.e., VALUE1, VALUE2, VALUE3 etc. The query is not showing that either.

Re: Subtract two fields and create a timechart

gcusello — Wed, 15 Sep 2021 07:55:43 GMT

Hi @priyangshupal,

sorry I forgot a piece!

please try this:

index=your_index | bin span=1d _time | stats count(skill1) AS skill1 count(skill2) AS skill2 BY _time value | eval diff=skill1-skill2 | table _time value diff

Ciao.

Giuseppe

Re: Subtract two fields and create a timechart

priyangshupal — Wed, 15 Sep 2021 08:13:35 GMT

hi @gcusello

Assuming I had to replace value in your query with the field I want, I tried this query:

index=your_index | bin span=1d _time | stats count(skill1) AS skill1 count(skill2) AS skill2 BY _time VALUE1 | eval diff=skill1-skill2 | table _time VALUE1 diff

But now it's showing No results found

Did I went wrong somewhere in the above query?

Re: Subtract two fields and create a timechart

gcusello — Wed, 15 Sep 2021 08:24:32 GMT

Hi @priyangshupal,

check two things:

if the main search has results,
if VALUE1 is the name of the field (not the value but the field name).

if you want only the count for value=VALUE1, you can put a filter in the main search:

index=your_index value=VALUE1 | bin span=1d _time | stats count(skill1) AS skill1 count(skill2) AS skill2 BY _time | eval diff=skill1-skill2 | table _time diff

Ciao.

Giuseppe

Re: Subtract two fields and create a timechart

priyangshupal — Wed, 15 Sep 2021 08:33:42 GMT

hi @gcusello

I checked, the main search does have events. But there's no such field as VALUE1.

VALUE1 is present in the fields named: skill1 and skill2. (check the main post)

And, no I do not want the count for only VALUE1, I want the count for all the VALUEs i.e. VALUE1, VALUE2, VALUE3, VALUE4 and so on.

What essentially I want to do is:

skill1.VALUE1 - skill2.VALUE1

skill1.VALUE2 - skill2.VALUE2

skill1.VALUE3 - skill2.VALUE3

...

skill1.VALUE7 - skill2.VALUE7

And I want to create a timechart out of the difference table

Re: Subtract two fields and create a timechart

gcusello — Wed, 15 Sep 2021 08:44:21 GMT

Hi @priyangshupal,

ok please, try this:

Ciao.

Giuseppe

Re: Subtract two fields and create a timechart

priyangshupal — Wed, 15 Sep 2021 09:21:54 GMT

hi @gcusello
This is very close to what I need but the diff column in the table generated is empty.

One more thing, I observed:

eval skill = coalesce(skill1, skill2)

added values in skill1 and skill2 and created a new field skill with the same values. Isn't there a way to reverse it? I mean, instead making it add, just make it take the difference? (because that is what I want). I am new to splunk so don't know, just asking.

If that's not possible then can u pls tweak the last query to display diff?

Thanks

Re: Subtract two fields and create a timechart

priyangshupal — Wed, 15 Sep 2021 09:37:39 GMT

One thing @gcusello

Can we by some way turn all values in skill2 to negative and then use the coalesce function?

I think this might work. Is there any way to turn all values in skill2 to negative?

I mean, if the value of skill2.VALUE1 is 300, it should turn to -300

And so on for VALUE2, VALUE3, VALUE4 and all others in skill2.

Is there any way to do it?

Thanks

Re: Subtract two fields and create a timechart

gcusello — Thu, 16 Sep 2021 07:24:15 GMT

Hi @priyangshupal,

the eval=coalesce... command is mandatory to have values of skill1 and skill2 in one field to use in the stats command.

I don't understand the request of negative skill2: a count is always a positive number and calculating difference between skill1 and skill2 you always subtract the second from the first, so what's your need?

Ciao.

Giuseppe