Diff between 2 tables

[sfbayeng]

I have splunk queries that generates 2 different tables having similar fields (METHOD, URI, COUNT). I wanted to do a diff between them based on URI and also the count.
Eg:

tableA

METHOD	URI	COUNT
GET	1/0/foo	3
PUT	1/0/bar	11

tableB

METHOD	URI	COUNT
GET	1/0/foo	2
PUT	1/0/bar	11
PUT	1/0/buzz	1

Is there a way to do difference between 2 tables based on METHOD+URI and COUNT? Result should be something like 

METHOD	URI	COUNT
GET	1/0/foo	1
PUT	1/0/buzz	1

[kamlesh_vaghela]

@sfbayeng 

Can you please try this?

YOUR_FIRST_TABLE_SEARCH
| append [
YOUR_SECOND_TABLE_SEARCH ]
| stats max(COUNT) as mx min(COUNT) as mn list(COUNT) as COUNT by URI METHOD
| eval COUNT=if(mvcount(COUNT)==1,COUNT, mx-mn)
| where COUNT>0
| table METHOD URI COUNT

My Sample Search :

| makeresults | eval _raw="METHOD	URI	COUNT
GET	1/0/foo	3
PUT	1/0/bar	 11" | multikv forceheader=1
| append [
| makeresults | eval _raw="METHOD	URI	COUNT
GET	1/0/foo	2
PUT	1/0/bar	11
PUT	1/0/buzz	1" | multikv forceheader=1 ]
| stats max(COUNT) as mx min(COUNT) as mn list(COUNT) as COUNT by URI METHOD
| eval COUNT=if(mvcount(COUNT)==1,COUNT, mx-mn)
| where COUNT>0
| table METHOD URI COUNT

I hope this will help you.

Thanks
KV
▄︻̷̿┻̿═━一   😉

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[sfbayeng]

Thanks for the reply KV. In my case, the dataset is huge, so append might be bit problematic.  I'll try this approach out and let you know. 

EDIT: This approach was bit slow, so went with other solution mentioned in thread. Thanks a lot @kamlesh_vaghela 

[PickleRick]

A bit overcomplicated 😉

If you want to use append (which might not be the best approach if there are many results), you can simply

<first_search>
| eval searchno=1
| append [
 <second_search>
 | eval searchno=2 ]
| stats count(eval(searchno=1)) as s1count
   count(eval(searchno=2)) as s2count
   by METHOD URI
| eval diff=s1count-s2count

[PickleRick]

The diff part should be ralitively easy. You just count values from one set into one field, from the other set into other one and do your substraction. It can be more tricky to generate a common result set without using append. Is there a single field that differentiates your both sets (like, first search is from one index and the other is from another; or source; or whatever)?

[sfbayeng]

Thanks for the reply PickleRick. I do have a field that can help me differentiate both sets (something like "System1" and "System2").  Both datasets are pretty huge, so was trying to see if there was better way than append suggestion in previous post.

[PickleRick · Accepted solution]

Thought so 🙂

If your result sets are sizeable, you shouldn't indeed use append.

The proper way to go would be to combine conditions from both searches into one (with an OR operator) and then conditionally set an indicator field to show if it's an event from the first search or the second one. Something like

(<your_first_search_conditions>) OR (<your_second_search_conditions>)
| eval searchno=if(some_field=something,1,2)
| stats count(eval(searchno=1)) as s1count
  count(eval(searchno=2)) as s2count
  by METHOD URI
| eval diff=s2count-s1coun

[sfbayeng]

Thanks a lot..it works with your approach