How to search using values from another search result?

[iammax]

Please help...

1st search query is where I get a value from the result. (value can be in either 1 of 3 fields)

index=index1 | table SQ1-user SQ1-field1 SQ1-field2 SQ1-field3

SQ1-user	SQ1-field1	SQ1-field2	SQ1-field3
john	null	null	apple
jane	null	orange	null
doe	banana	null	null

From that value, I want to use it to check if it exist in another search query, (the value can be on any fields)

index=index2 | where ANY_FIELD=SQ1-field1 OR ANY_FIELD=SQ1-field2 OR ANY_FIELD=SQ1-field3

SQ2-ID	SQ2-field1	SQ2-field2	SQ2-field3
001	null	apple	null
002	banana	null	null

if it exist in the second query, I want to have a new field on my first query that says the ID of where it was found or "NOT FOUND".

SQ1-user	SQ1-field1	SQ1-field2	SQ1-field3	(NEW FIELD)SQ2-ID
john	null	null	apple	001
jane	null	orange	null	NOT FOUND
doe	banana	null	null	002

[yuanliu]

As @johnhuang said, it is critical to illustrate data because a successful search strategy depends on data characteristics.  You really did yourself a great favor to update.

Now, I assume that those values that you displayed as "null" are actual null values.  What you wanted from this data can be easily achieved with coalesce function.

First, to address the "| where" filter.

index IN (index1, index2)
| eval SQ1-field = coalesce('SQ1-field1', 'SQ1-field2', 'SQ1-field3')
| eval SQ2-field = coalesce('SQ2-field1', 'SQ2-field2', 'SQ2-field3')
| where SQ1-field == SQ2-field
| table SQ1-user SQ1-field SQ2-ID

When you use that | where, you cannot have that row with jane as the last table illustrates.

To make a new field as illustrated that includes "not found", do

index IN (index1, index2)
| eval SQ1-field = coalesce('SQ1-field1', 'SQ1-field2', 'SQ1-field3')
| eval SQ2-field = coalesce('SQ2-field1', 'SQ2-field2', 'SQ2-field3')
| eval "(New field)SQ2-ID" = if('SQ2-field' == 'SQ1-field', 'SQ2-ID', "Not found")
| table SQ1-user SQ1-field '(New field)SQ2-ID'

Now, if those "null" values are literal strings spelled as "null", you can always clean them up before proceeding, e.g.,

index IN (index1, index2)
| foreach SQ*-field*
  [eval <<FIELD>> = if(<<FIELD>> == "null", null(), <<FIELD>>)]
| eval SQ1-field = coalesce('SQ1-field1', 'SQ1-field2', 'SQ1-field3')
| eval SQ2-field = coalesce('SQ2-field1', 'SQ2-field2', 'SQ2-field3')
| eval "(New field)SQ2-ID" = if('SQ2-field' == 'SQ1-field', 'SQ2-ID', "Not found")
| table SQ1-user SQ1-field '(New field)SQ2-ID'

[iammax]

Hi @yuanliu , Thank you so much for the help.

I understand that in using coalesce, i can only get 1 value per user, but what if both query1 and query2 has values in 2 or 3 fields? How can I check if any field on query1 matches in any field of query2?

SQ1-user	SQ1-field1	SQ1-field2	SQ1-field3
john	berry	mango	apple
jane	berry	orange	null
doe	banana	null	null

john have 3 fields with value / jane have 2 fields with value / doe have 1 field with value.

SQ2-ID	SQ2-field1	SQ2-field2	SQ2-field3
001	null	apple	grapes
002	banana	pineapple	null

001 and 002 have 2 fields with value.

*** For SQ1-user john, how can I check if any of the value in the 3 fields (berry OR mango OR apple) can match in any of the fields in query2? 

Final Result should still be the same:

SQ1-user	SQ1-field1	SQ1-field2	SQ1-field3	(NEW FIELD)SQ2-ID
john	berry	mango	apple	001
jane	berry	orange	null	NOT FOUND
doe	banana	null	null	002

Thanks again in advance.  🙂

[yuanliu]

---

 what if both query1 and query2 has values in 2 or 3 fields? How can I check if any field on query1 matches in any field of query2?

That is exactly why every helper on this forum emphasizes that you need to illustrate your data, including characteristics like this.  Otherwise others just waste time trying to read your mind.

Because the task is really just trying to match user with ID via any field value, I'll assume that whether a match occurs in field1 or field2 or field3 is of no importance and simplify the output; you can still work out a way to preserve the original field ID if that's important. (Just take away all the stats and work them back in.)

| rename SQ*-field* as SQ*_field*
| foreach SQ*_field*
    [eval SQ_field = mvappend(SQ_field, <<FIELD>>)]
| mvexpand SQ_field
| stats values(SQ1-user) as user values(SQ2-ID) as ID by SQ_field
| stats values(ID) as ID values(SQ_field) as SQ_field by user
| fillnull ID value="NOT FOUND"

 With your sample data, the output is

user	ID	SQ_field
doe	002	banana
jane	NOT FOUND	berry orange
john	001	apple berry mango

Hope this helps.

[johnhuang]

It'll be easier if you can provide a sample query for the first and second search.

[iammax]

just the basic query where you can get my sample result above. (i edited my post for sample query). I am confused on how to do the correlation and on the adding of the new field part.