Subtract two fields and create a timechart

[priyangshupal]

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!

[gcusello]

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

[gcusello]

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

[priyangshupal]

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?

[gcusello]

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

[priyangshupal]

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

[gcusello]

Hi @priyangshupal,

ok please, try this:

index=your_index 
| bin span=1d _time
| stats count AS c_skill1 BY _time skill1
| append [ search 
     index=your_index 
     | bin span=1d _time
     | stats count AS c_skill2 BY _time skill2
     ]
| eval skill=coalesce(skill1,skill2)
| stats values(c_skill1) AS c_skill1 values(c_skill2) AS c_skill2 BY _time skill
| eval diff=c_skill1-c_skill2
| table _time skill diff

Ciao.

Giuseppe

[priyangshupal]

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

[priyangshupal]

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

[gcusello]

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

[priyangshupal]

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.