How to search all value from one mvfield to another mvfield?

[iammax]

Hi Community,

I have 2 mvfields, how can I search for all the values in the first mvfield to all the values in the second mvfield?

index=animals 
| eval all_animals = mvappend('animal1', 'animal3', 'animal3')
| table id_animals all_animals

id_animals	all_animals
001	dog goat cow
002	tiger lion
003	parrot snake boar

index=pets 
| eval all_pets = mvappend('pet1', 'pet2')
| table id_pets all_pets

id_pets	all_pets
A1	parrot mouse
A2	dog cat

result:

id_animals	animals	id_pets
001	dog cat mouse	A2
002	tiger lion	NO MATCH
003	parrot snake	A1

[yuanliu]

@iammax Isn't this the exact same question as How to search using values from another search result? that we discussed?

[kamlesh_vaghela]

@iammax 

Can you please try this?

YOUR_FIRST_SEARCH
| mvexpand all_animals 
| eval all_pets = all_animals 
| join type=left all_pets 
    [YOUR_SECOND_SEARCH
    | makemv all_pets delim="|" 
    | mvexpand all_pets
        ] 
| stats values(id_pets) as id_pets, values(all_animals) as animals by id_animals 
| eval id_pets=if(isnull(id_pets),"NO MATCH",id_pets) 
| table id_animals animals id_pets

My Sample Search :

| makeresults 
| eval _raw="id_animals,all_animals
001,dog|goat|cow
002,tiger|lion
003,parrot|snake|boar" 
| multikv forceheader=1 
| makemv all_animals delim="|" 
| table id_animals,all_animals 
| rename comment as "upto this is sample data" 
| mvexpand all_animals 
| eval all_pets = all_animals 
| join type=left all_pets 
    [| makeresults 
    | eval _raw="id_pets,all_pets
A1,parrot|mouse
A2,dog|cat" 
    | multikv forceheader=1 
    | table id_pets,all_pets 
    | rename comment as "upto this is sample data" 
    | makemv all_pets delim="|" 
    | mvexpand all_pets
        ] 
| stats values(id_pets) as id_pets, values(all_animals) as animals by id_animals 
| eval id_pets=if(isnull(id_pets),"NO MATCH",id_pets) 
| table id_animals animals id_pets

 I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

[iammax]

Hi

this works perfectly. thank you very much.

I just noticed one thing from my logs. the "id_animals" is not unique. I have multiple "id_animals" and it was getting grouped in 1 entry. Can we group the logs by id_animals in every 5 mins?

sample result:

result:

Time	id_animals	animals	id_pets
01:00	001	dog cat mouse	A2
01:05	001	dog zebra	A2
01:00	002	lion	NO MATCH
01:05	002	tiger lion	NO MATCH
01:00	003	parrot snake	A1
01:05	003	elephant giraffe	NO MATCH

@kamlesh_vaghela

[kamlesh_vaghela]

@iammax 

Can you please try this?

YOUR_FIRST_SEARCH 
| mvexpand all_animals 
| eval all_pets = all_animals 
| join type=left all_pets 
    [ YOUR_SECOND_SEARCH 
    | makemv all_pets delim="|" 
    | mvexpand all_pets
        ] 
| bin _time span=5m 
| stats values(id_pets) as id_pets, values(all_animals) as animals by _time id_animals 
| eval id_pets=if(isnull(id_pets),"NO MATCH",id_pets) 
| table id_animals animals id_pets

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.