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

[dkeck]

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

[somesoni2]

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

[esix_splunk · Accepted solution]

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 

[dkeck]

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 countto see how often the field is present.

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

[fdi01]

try like this :

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

[javiergn]

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