Splunk Search

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

jawebb
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
1 Solution

woodcock
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

somesoni2
Revered Legend

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

jawebb
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

woodcock
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

jawebb
Explorer

That does it. Thanks again!

0 Karma

gcato
Contributor

Hi jawebb,

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

0 Karma

gcato
Contributor

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!