How to search the difference between the values of two fields between separate searches?

[jawebb]

I have a field of names from two indexes and wish to find the unique values between them. I thought I should have to use the | set diff command for this, but have been coming up short. Any help would be appreciated. Here is what I have so far.

|set diff [search index=index1 |eval Name=lower(Name) | dedup Name | fields Name] [search index=index2| rename "computer_name" as Name | eval Name=lower(Name)| dedup Name | fields Name]

I still find duplicates and non unique values, but dedup seems to work when I separate the searches.

Thanks!

[somesoni2]

Try something like this

  index=index1 OR index=index2 | eval Name=coalesce(lower(Name),lower('computer_name')) | stats dc(index) as indexes by Name | where indexes=1

[jawebb]

This along with the previous answer both seem to get the job done, with the previous answer including all of the fields that I can use for additional manipulation. Thanks for the help!!

[woodcock · Accepted solution]

Try this:

index=index1 OR  index=index2 | rename "computer_name" as Name |eval Name=lower(Name) | stats values(*) AS * dc(index) AS indices by Name

This gives the fully merged set (full join). For each function, tack on the appropriate remaining search string:

For XOR (outer join) which I believe is what you are seeking:

| where indices = 1

For left join:

| where index=index1

For right join:

| where index=index2

For inner join:

| where indices>1

[jawebb]

That does it. Thanks again!

[gcato]

Hi jawebb,

Try doing a sort on the field Name in both searches and see if that helps.

[gcato]

Should also note that subsearches have a max result limit of 10,000 events.