Splunk Search

How to calculate the difference in field values

changux
Builder

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!

0 Karma

niketn
Legend

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#an...

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

changux
Builder

Thanks!
If my searches are:

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

 index=second sourcetype=othersrt

Any suggestion?

0 Karma

niketn
Legend

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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

twinspop
Influencer

Try this:

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

changux
Builder

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!

0 Karma

sideview
SplunkTrust
SplunkTrust

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.

0 Karma

changux
Builder

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!

0 Karma

changux
Builder

Anybody? Please help me.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...