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 |
@iammax Isn't this the exact same question as How to search using values from another search result? that we discussed?
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.
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 |
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.