topic Re: How to find common and distinct data from two datasets? in Dashboards & Visualizations

How to find common and distinct data from two datasets?

maverick27 — Thu, 01 Feb 2024 06:26:20 GMT

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?

Re: How to find common and distinct data from two datasets?

gcusello — Thu, 01 Feb 2024 06:57:54 GMT

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

Re: How to find common and distinct data from two datasets?

PickleRick — Thu, 01 Feb 2024 07:11:38 GMT

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

Re: How to find common and distinct data from two datasets?

ITWhisperer — Thu, 01 Feb 2024 09:44:51 GMT

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

Re: How to find common and distinct data from two datasets?

maverick27 — Thu, 01 Feb 2024 09:56:12 GMT

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!

Re: How to find common and distinct data from two datasets?

maverick27 — Thu, 01 Feb 2024 12:32:54 GMT

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

Re: How to find common and distinct data from two datasets?

gcusello — Thu, 01 Feb 2024 13:42:03 GMT

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

Re: How to find common and distinct data from two datasets?

ITWhisperer — Thu, 01 Feb 2024 13:44:22 GMT

Re: How to find common and distinct data from two datasets?

PickleRick — Thu, 01 Feb 2024 13:51:13 GMT

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