How to calculate the difference between count of two different searches?

[changux]

Hi all.

I have a first search:

index=first sourcetype=type1 | stats count 

And a second:

index=first  sourcetype=data_sourcetype | join ID  [search index="second" sourcetype="datatype2"] | stats count 

Both cases returns number of events. But I need to calculate the number difference between the first search's value and the second. How i can proceed?

Thanks!

[somesoni2]

Try this

index=first  (sourcetype=data_sourcetype  [search index="second" sourcetype="datatype2" | stats count by ID | table ID ] ) OR sourcetype=type1 
| chart count over index by sourcetype | eval difference='data_sourcetype' - 'type1'

[niketn]

Based on the above search you might not need join for the second search. See if following query helps:

(index="first" sourcetype="data_sourcetype" ) OR (index="second" sourcetype="datatype2") | stats count as count1 | appendcols [search index="first" sourcetype="type1"| stats count as count2]| eval difference=count1-count2| table count1, count2, difference

You can also save index AND sourcetype searches as three different eventtypes and then use stats count by eventtype as your query. accum and delta can be used on different rows of search results by eventtype.