How to find common and distinct data from two datasets?

[maverick27]

Hi @ITWhisperer ,

I have two datasets from two search queries. I need to fetch the common as well as distinct values from both the datasets in the final result. 

Something like this:

Field1	Field2	Result
1	2	1
3	4	2
5	6	3
7	8	4
9	10	5
10		6
		7
		8
		9
		10

Can you please help with the query?

[maverick27]

May be I wasn't clear with my requirement. Apologies guys!

Let me give you an example as to what I'm trying to do. 

1st search contains the following data:

Field1	DEPT	UID
1	Accounts	AA
3	HR	CC
5	Ops	EE
7	Tech	GG
9	Ops	II
10	Tech	JJ
11	HR	KK

2nd search contains the following data:

Field2	REGION
2	NA
4	TY
6	HK
8	AS
10	EU
11	AS

Now, I need to get common as well as disctinct rows from both the tables as shown below:

Result	DEPT	UID	REGION
1	Accounts	AA
2			NA
3	HR	CC
4			TY
5	Ops	EE
6			HK
7	Tech	GG
8			AS
9	Ops	II
10	Tech	JJ	EU
11	HR	KK	AS

[PickleRick]

(search1) OR (search2)
| stats values(*) as * by Field1

If they are bigger complex searches, you'd need to use append instead of simple OR for conditions but then you have to watch for limits for subsearches.

[gcusello]

hi @maverick27 ,

you have to expend my search:

index=index1 OR index=index2
| eval Result=coalesce(field1, field2)
| table Result DEPT UID REGION

Ciao.

Giuseppe

[ITWhisperer]

More like this

index=index1 OR index=index2
| eval Result=coalesce(field1, field2)
| stats values(*) as * by Result

[ITWhisperer]

It looks like @gcusello has provided a good answer - It is probably best not to call out individuals when first posing a question; we are all volunteers here and you don't know when the requested volunteer is going to be available, and others may feel that they shouldn't answer when the question is directed to particular volunteers (do you not value others' contributions?)

[maverick27]

Totally agree. I value everyone's contribution and restricting my question only to a certain individual will only delay or prolong the process. Apologies for that. This won't be repeated. Let me try @gcusello's solution and get back! Thanks guys!

[PickleRick]

And you have those results in multivalued fields? In separate result rows?

[gcusello]

Hi @maverick27 ,

you could use eval coalesce, something like this:

index=index1 OR index=index2
| eval new_field=coalesce(field1, field2)
| table new_field

Ciao.

Giuseppe