Splunk Search

Diff between 2 tables

sfbayeng
Explorer

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

METHODURICOUNT
GET1/0/foo3
PUT1/0/bar 11

 

tableB

METHODURICOUNT
GET1/0/foo2
PUT1/0/bar11
PUT1/0/buzz


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

METHODURICOUNT
GET1/0/foo
PUT1/0/buzz
Labels (1)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

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

View solution in original post

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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
Explorer

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 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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
0 Karma

PickleRick
SplunkTrust
SplunkTrust

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
Explorer

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.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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
Explorer

Thanks a lot..it works with your approach

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