topic Re: Diff between 2 tables in Splunk Search

Diff between 2 tables

sfbayeng — Wed, 03 Nov 2021 23:19:49 GMT

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

Re: Diff between 2 tables

PickleRick — Thu, 04 Nov 2021 05:11:45 GMT

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)?

Re: Diff between 2 tables

kamlesh_vaghela — Thu, 04 Nov 2021 05:19:12 GMT

@sfbayeng

Can you please try this?

My Sample Search :

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.

Re: Diff between 2 tables

PickleRick — Thu, 04 Nov 2021 08:28:18 GMT

A bit overcomplicated 😉

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

Re: Diff between 2 tables

sfbayeng — Fri, 05 Nov 2021 00:08:32 GMT

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

Re: Diff between 2 tables

sfbayeng — Thu, 04 Nov 2021 16:05:27 GMT

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.

Re: Diff between 2 tables

PickleRick — Thu, 04 Nov 2021 19:10:33 GMT

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

Re: Diff between 2 tables

sfbayeng — Fri, 05 Nov 2021 00:07:10 GMT

Thanks a lot..it works with your approach