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?
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 |
(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.
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
More like this
index=index1 OR index=index2
| eval Result=coalesce(field1, field2)
| stats values(*) as * by Result
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?)
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!
And you have those results in multivalued fields? In separate result rows?
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