Splunk Search

## How do I edit my current search to compare the values of 2 fields efficiently?

Influencer

Hello,

I want to compare results of 2 searches, I am using a subsearch and a join

``````index=1 | table field1 | eval a=field1| join type=left a [ | search index=2 |table field2 | eval a=field2 | fields -a
``````

Problem is that field2 doesnยดt have all the values of field1 and I want to check which values are present and if they are equal.

Should look like that:

``````Field1   Field2
value1   value1
vaule2   value2
vaule3
vuale4   value4
``````

For now I just got it work by comparing both fields with the new field a. Is there a way to get that done more efficiently?

Thank you

Tags (4)
1 Solution
Splunk Employee

Im not sure what you mean by the eval here. Do you mean if the value is in field1, make it the same for field2? Joins are ugly, we can also do similar like the below with stats.

``````index=1 OR index=2 | stats list(a) AS A list(b) AS B
``````

Thats going to give you a list of the values, but it wont provide a gap between values.. Maybe why youre trying to do a join?

If there is a time field associated with these, its much easier to do

``````index=1 OR index=2 | stats list(a) AS A list(b) AS B by _time | eval B=if(isnull(B),A,B) | table A B
``````
SplunkTrust

Try something like this. This is inline with your expected output

``````index=1 OR index=2 | eval common=coalesce(field1,field2) | stats values(index) as index by common | eval Field1=case(mvcount(index)=2,common, mvcount(index)=1 AND index="1",common,1=1,"") | eval Field2=case(mvcount(index)=2,common, mvcount(index)=1 AND index="2",common,1=1,"") | table Field1 Field2
``````
Splunk Employee

Im not sure what you mean by the eval here. Do you mean if the value is in field1, make it the same for field2? Joins are ugly, we can also do similar like the below with stats.

``````index=1 OR index=2 | stats list(a) AS A list(b) AS B
``````

Thats going to give you a list of the values, but it wont provide a gap between values.. Maybe why youre trying to do a join?

If there is a time field associated with these, its much easier to do

``````index=1 OR index=2 | stats list(a) AS A list(b) AS B by _time | eval B=if(isnull(B),A,B) | table A B
``````
Influencer

Thank you , I got on the right track by using stats list().

I cam up with a different solution, but with stats. I didnยดt compare the field, I just took `stats count`to see how often the field is present.

`````` stats count list(origin) as origins list(sourcetype) list(type) by field
``````
Motivator

try like this :

``````(index=ind1 OR index=ind2) | table field1 field2 | eval field2=if(field1=field2, field1,"")
``````
SplunkTrust

if you want something more efficient then get rid of the join. I'm still not 100% sure what you are trying to achieve anyway, can you explain with some data?

In any case, try the following query (NOT TESTED) and let me know:

`````` index=1 OR index=2
| eval newfield = coalesce(field1,field2)
| stats first(field1) as field1, first(field2) as field2 by newfield
``````

newfield is the equivalent of a uniqueID in your join

If the above doesn't work for you please post a more detailed example of how your data looks like.

Thanks,
J