topic How to search for events based on certain field and its exclusive values between two searches? in Splunk Search

How to search for events based on certain field and its exclusive values between two searches?

FallMonkey — Wed, 22 Jun 2016 23:16:17 GMT

Say I have two searches on data sets which contain four fields [field1, field2, field3, field4], e.g.

[1,20,am,a]
[1,20,am,b]
[1,20,pm,b]
[1,20,pm,c]

Search 1: field1 = 1, field2 = 20, field3 = am will return [1,20,am,a] and [1,20,am,b]
Search 2: field1 = 1, field2 = 20, field3 = pm will return [1,20,pm, b] and [1,20,pm,c]

Yet I'm interested in field4 and those events with values of field4 exclusively in my first search, i.e. [1,2,am,a] in this case since field4=b is also presented in second search.

What would be an efficient way to do so? Thanks a lot!

Re: How to search for events based on certain field and its exclusive values between two searches?

ryanoconnor — Thu, 23 Jun 2016 01:55:01 GMT

Can you try to clarify a little bit more of what you're looking for here? I don't see field4 mentioned in either of your searches

Re: How to search for events based on certain field and its exclusive values between two searches?

FallMonkey — Thu, 23 Jun 2016 02:52:35 GMT

Thanks for the reply!

Yes field4 is not listed as my search keyword but it's inside the event/data sets. One event actually contains much more fields but the ones I listed are most interesting for me. Please lemme know if you need more information.

Re: How to search for events based on certain field and its exclusive values between two searches?

woodcock — Thu, 23 Jun 2016 06:42:44 GMT

You have not been clear at all. Please start over, show us COMPLETE sample events and then desired final output.

Re: How to search for events based on certain field and its exclusive values between two searches?

FallMonkey — Thu, 23 Jun 2016 07:15:32 GMT

Sorry for not being clear. I've edited my question with more concrete samples for your information.

Re: How to search for events based on certain field and its exclusive values between two searches?

woodcock — Thu, 23 Jun 2016 17:47:24 GMT

You have made a minor adjustment to YOUR plan (not working) and ignored MY plan (which would have gotten you an answer instead of snark). Let me take a stab and you tell me if I am guessing anything remotely close (I am a nerd, not a mind reader):

I am interested in taking running a search where I specify values for 3 fields and extracting from that search the values of a 4th field. I would then like to use those values to drive another search.

In the case of the example data above, the first search is 1: field1 = 1, field2 = 20, field3 = am and will return [1,20,am,a] and [1,20,am,b]
These events with values a and b for field field4. Now I would like to use those values to drive another search like this: field1 = 1, field2 = 20, field3 = pm (field4=b OR field4=c) which would return [1,20,pm, b].

How can I do this all in a single search?

See how I gave specific final desired output? I know that my guess is probably wrong, but why are you making us guess?

Re: How to search for events based on certain field and its exclusive values between two searches?

FallMonkey — Thu, 23 Jun 2016 18:02:28 GMT

Thanks a lot for the comment. Again sorry for the confusion caused here.

I wish to run two similar searches first, as shown above. Then in the results there will be some events with same value of field4 between two search results.

From there I wish to run another search/filtering on complete dataset, to get rid of those events with values of field4 that show up in my 2nd search. Therefore [1,20,am,b] and [1,20,pm,b] are taken out because field4=b is in my 2nd search, as well as [1,20,pm,c]. Clearly I need first two searches to identify how values of field4 are distributed between two searches, so that I could start filter.

Then my question is how I can do all of this in one line. Please lemme know if something is still unclear.

Re: How to search for events based on certain field and its exclusive values between two searches?

diogofgm — Thu, 23 Jun 2016 18:13:34 GMT

Try this

"Your search1" | join type=outer field4 [search "your search2"]

Re: How to search for events based on certain field and its exclusive values between two searches?

woodcock — Thu, 23 Jun 2016 18:45:42 GMT

Like this:

index=YourIndexHere sourcetype=YourSourcetypeHere field1 = "1" field2 = "20" (field3 = "am" OR field3 = "pm")
| stats dc(field3) AS numSources values(*) AS * BY field4

You now have a fully joined set:
For left Join, add this:

 | search field3="am" AND numSources>1

For right join, add this:

 | search field3="pm" AND numSources>1

For inner join, add this:

| search numSources>1

For outer join, add this:

| search numSources=1