Find values of Field 1 in Field 2

[k1green97]

I am not sure where to start on this. I have 2 fields. Field1 only has a few values while Field2 has many. How can I return values Field2 that appear in Field1?

Field 1	Field 2
17	27
24	33
36	17
	22
	24
	31
	29
	08
	36

[livehybrid · Accepted solution]

Hi @k1green97 

Check out the following, if your Field2 is a multivalue field you should be good with a 'where IN':

| where Field1 IN (Field2)

 Full example:

| windbag | head 25 | streamstats count as Field1
| table _time Field1
| eval  Field2=split("27,33,17,22,24,31,29,08,36",",")
| where Field1 IN (Field2)

HOWEVER, if as it looks on the table you posted that for Row 1, Field1=17 Field2=27 but you want to check if Field1 is in the combined list of Field2 values then you will need to group them together first using eventstats:

| eventstats values(Field2) as Field2
| where Field1 IN (Field2)

Full example:

| makeresults count=9 | streamstats count as _n
| eval Field1=case(_n=1, 17, _n=2, 24, _n=3, 36)
| eval Field2=case(_n=1, 27, _n=2, 33, _n=3, 17, _n=4, 22, _n=5, 24, _n=6, 31, _n=7, 29, _n=8, 8, _n=9, 36)
| fields - _time
``` finished data sample ```
| eventstats values(Field2) as Field2
| where Field1 IN (Field2)

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[PickleRick]

OK. And those "fields" are...? Values of a multivalued field in a single event? Or just multiple values returned from "stats values "command? Something else?

Do you have any other fields in your data? Do you want them preserved?

[kiran_panchavat]

@k1green97 

To find values of Field1 that appear in Field2 using Splunk with makeresults(The makeresults command allows users to quickly generate sample data sets for testing) you can create a query that generates the data and then uses eval and where to filter the matching values.

You can try this query and replace the values:

index=my_index sourcetype=my_sourcetype 
| stats values(Field1) as Field1_values, values(Field2) as Field2_values 
| mvexpand Field1_values 
| where Field1_values IN (Field2_values) 
| table Field1_values