topic Re: How to calculate the difference in field values in Splunk Search

How to calculate the difference in field values

changux — Wed, 02 Nov 2016 22:31:57 GMT

Hi all.

I have two basic searches like this:

index=first sourcetype=first-sourcetype | stats count by FIELD1

index=second sourcetype=second-sourcetype | stats count by FIELD1

FIELD1 has values like ONE, TWO, THREE.

I want to calculate the difference between the two searches value per value. How i can do? The two sourcetypes are equal, the difference is the quantity of values on each one.

Thanks!

Re: How to calculate the difference in field values

twinspop — Wed, 02 Nov 2016 22:43:54 GMT

Try this:

(index=first OR index=second) (sourcetype=first-sourcetype OR sourcetype=second-sourcetype) |
chart count over FIELD1 by index |
eval diff=first-second

Re: How to calculate the difference in field values

changux — Wed, 02 Nov 2016 23:37:20 GMT

Works great. if my searches are:

index=first sourcetype=anysourcetype | join ID [search index=second sourcetype=othersrt]

and

index=second sourcetype=othersrt

How i can obtain the same calculation?

Thanks!

Re: How to calculate the difference in field values

niketn — Thu, 03 Nov 2016 06:47:24 GMT

Option 1

Step 1: Run search index="first" sourcetype="anysourcetype" and save as Event Type "firstany".

Step 2: Run search index="second" sourcetype="othersrt" and save as Event Type "secondother"

Step 3: Run the following search query

(eventtype="firstany" ) OR (eventtype="secondother") | chart count over FIELD1 by eventtype | eval difference=firstany-secondother

Also refer to previous answer on similar lines as this seems to be a duplicate thread.
https://answers.splunk.com/answers/470876/how-to-calculate-the-difference-between-count-of-t.html#answer-469923

Option 2

(index="first" sourcetype="anysourcetype") OR (index="second" sourcetype="otherstr") | eval statsfield= FIELD1 + " - " +  index  + " - " + sourcetype | stats count by statsfield | delta count as Difference

Please let me know if this is not what you are looking for, so that I may assist.

Re: How to calculate the difference in field values

changux — Thu, 03 Nov 2016 12:17:00 GMT

Thanks!
If my searches are:

 index=first sourcetype=anysourcetype | join ID [search index=second sourcetype=othersrt]

 index=second sourcetype=othersrt

Any suggestion?

Re: How to calculate the difference in field values

niketn — Thu, 03 Nov 2016 12:52:16 GMT

Try the following:

 (index="first" sourcetype="anysourcetype") OR (index="second" sourcetype="otherstr") |  stats count as count1 by FIELD1 | appendcols [search index="second" sourcetype="otherstr"| stats count as count2 by FIELD1]|  eval Difference=count1-count2

Re: How to calculate the difference in field values

sideview — Thu, 03 Nov 2016 17:34:49 GMT

It's very tempting to solve this problem with a second join. However breaking down the requirements with paper and pencil, there are other ways to do it in more of a splunklike fashion. (and this means with far better performance, without breaking map-reduce, and with no limits on the size of your results.)

The first search (assuming there's an implicit | stats count by FIELD1 on the end as before) is saying "find me the counts for all values of FIELD1, for just the rows in index=first sourcetype=anysourcetype whose ID appears also in index=second sourcetype=othersrt.

Now to compare the "search1" counts vs the "search2" couonts, we kind of need to transform the incoming rows in two different (kind of mutually exclusive) ways. Obviously we can't transform the results in incompatible ways, and this means we have to do the same work without transforming the incoming rows at all. Whenever you hit this sort of thing you should think of eventstats and streamstats because they are your tools for that.

So, a way to get search1's, (and I've gone ahead and put back the chart count by ID bit)

 (index=first OR index=second) (sourcetype=first-sourcetype OR sourcetype=second-sourcetype)
| eventstats values(index) as indexesWithThisID by ID
| search indexesWithThisID=second index=first
| chart count by ID

it's kind of weird. We send eventstats off on a mission to find, for each value of "ID", which indexes that value in across the whole set. We then paint those indexes on each row as a multivalue field called indexesWithThisID.

The end result is that we can filter the set down just with | search indexesWithThisID=second index=first

Let's now modify this, so that it can take the FIELD1 counts for these events, and compare them with the FIELD1 counts for the index=second sourcetype=othersrt events. Here I'm using a conditional eval to break it into steps.

 (index=first OR index=second) (sourcetype=first-sourcetype OR sourcetype=second-sourcetype)
| eventstats values(index) as indexesWithThisID by ID
| eval matchesWhichSearch=case(indexesWithThisID="second" AND index="first",1,index="second",2,true(),-1)
| search matchesWhichSearch>0
| chart count over FIELD1 by matchesWhichSearch

Note - it may make sense for this to be moved out to its own question but I'll leave it here for now.

Re: How to calculate the difference in field values

changux — Fri, 04 Nov 2016 00:37:52 GMT

Thanks @sideview, @niketnilay . I need the join, some context to understand:

Each sourcetype has:

index=first sourcetype=anysourcetype => Executed tasks
index=second sourcetype=othersrc => To execute tasks.

Then, the join returns to me the REAL executed tasks with some details about it. The field FIELD1 is originally present at index=second sourcetype=othersrc, so after join, i have the list of REAL executed and the value with states like type1, type2, type3, etc (included in FIELD1). I need to show the list of NOT EXECUTED tasks classified by FIELD1.

Thanks!

Re: How to calculate the difference in field values

changux — Mon, 07 Nov 2016 00:29:57 GMT

Anybody? Please help me.

Re: How to calculate the difference in field values

niketn — Fri, 03 Mar 2017 21:08:45 GMT

You can just replace appencols in above example with join ID

So try the following:

(index="first" sourcetype="anysourcetype") OR (index="second" sourcetype="otherstr") |  stats count as count1 by FIELD1 | join ID [search index="second" sourcetype="otherstr"| stats count as count2 by FIELD1]|  eval Difference=count1-count2

FYI - append, appendcols, join, stats, eventstats, transaction, subsearch etc are all for event correlation and most of them might fit in to return the results you want. However, you should try job inspector to evaluate which one performs best. Refer to following Splunk documentation on how to choose between various event correlation commands

http://docs.splunk.com/Documentation/Splunk/latest/Search/Abouteventcorrelation