Splunk Search

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

iammax
Explorer

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

Labels (4)
Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

 

Screenshot 2022-11-22 at 6.57.16 PM.png

 

 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.

 

0 Karma

iammax
Explorer

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:

Timeid_animalsanimalsid_pets
01:00001

dog

cat

mouse

A2

01:05001

dog

zebra

A2

01:00002

lion

NO MATCH

01:05002

tiger

lion

NO MATCH

01:00003

parrot

snake

A1

01:05003

elephant

giraffe

NO MATCH

@kamlesh_vaghela

Tags (1)
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

 

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...