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!
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.
Thanks!
If my searches are:
index=first sourcetype=anysourcetype | join ID [search index=second sourcetype=othersrt]
index=second sourcetype=othersrt
Any suggestion?
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
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
Try this:
(index=first OR index=second) (sourcetype=first-sourcetype OR sourcetype=second-sourcetype) |
chart count over FIELD1 by index |
eval diff=first-second
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!
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.
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!
Anybody? Please help me.