Splunk Search
Highlighted

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

Explorer

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!

Tags (4)
0 Karma
Highlighted

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

Contributor

Hi jawebb,

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

0 Karma
Highlighted

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

Contributor

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

0 Karma
Highlighted

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

Esteemed Legend

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

View solution in original post

Highlighted

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

Explorer

That does it. Thanks again!

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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
0 Karma
Highlighted

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

Explorer

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!!

0 Karma