Comparing data located in two different indexes?

[KayBeesKnees83]

Hello fellow Splunkers!

So, I have a series of questions related to comparing data from two different indexes in Splunk. The data, hardware assets, are assigned by groups. However, the assets are located in two different indexes and I need to determine how to see which assets are in index 1, index 2, and both. The following are my questions. Due to the nature of the data, I cannot provide a sample nor can I provide specific field names. However, the following table shows the correlation between the data located in both indexes:

Index 1	Relation	Index 2
SN	Equals	serial_number
MAC	Equals	ip_mac
Asset	Equals	barcode

Each of the aforementioned should be its own search to match the data in both indexes. 

1.) How would you search index 1 to identify which hardware assets are located in index 1 but are not located in index 2? 

2.) How would you search index 2 for assets (assets which are assigned by groups) are not in index 1? 

3.) How would you search both index 1 and index 2 to determine which assets match in both lists? 

Thank you in advance. I know this is a tall order but any possible searches or tips would be much appreciated!

-KB 

[yuanliu]

The questions becomes easier if you first ignore the difference in field names.  How do you answer those questions if both indices use the same field names?  Then, think of a way to make equivalence for the two indices.

Let's try this two-step approach.

index IN (index1, index2)
| eval SN = if(index==index1, SN, serial_number), MAC = if(index==index1, MAC, ip_mac), Asset = if(index==index1, Asset, barcode)
| stats values(index) as INDEX dc(index) as index_count by Asset
| eval both = if(index_count > 1, "yes", null()), only_index1 = if(index_count == 1 AND INDEX == "index1", "yes", null()), only_index2 = if(index_count == 1 AND INDEX == "index2", "yes", null())
| eval exists_in = if(index_count > 1, "both", INDEX)

The above includes two alternative presentations of the output.  The first one is with discrete fields like "both", "only_index1", and "only_index2", the second one is a one field "exists_in".  Hope this helps.

[KayBeesKnees83]

Greetings ,

Thank you for your feedback. I attempted the execute the search you provided; however, it was not successful. I did not receive any results after the following search string, unfortunately "

| stats values(index) as INDEX dc(index) as index_count by Asset

[yuanliu · Accepted solution]

The second line should have quotation marks surrounding index1.  My mistake.

| eval SN = if(index=="index1", SN, serial_number), MAC = if(index=="index1", MAC, ip_mac), Asset = if(index=="index1", Asset, barcode)

[KayBeesKnees83]

As a follow up, would it be possible to link the sources based on the assets? I have the data based on Index but is there a way to link the sources to the indexes based on source? For example, the asset XXX123, is being produced by this source, in this INDEX, etc. 

Thank you. 

[yuanliu]

You only have to enumerate values(source)

index IN (index1, index2)
| eval SN = if(index=="index1", SN, serial_number), MAC = if(index=="index1", MAC, ip_mac), Asset = if(index=="index1", Asset, barcode)
| stats values(index) as INDEX dc(index) as index_count values(source) as SOURCES by Asset
| eval both = if(index_count > 1, "yes", null()), only_index1 = if(index_count == 1 AND INDEX == "index1", "yes", null()), only_index2 = if(index_count == 1 AND INDEX == "index2", "yes", null())
| eval exists_in = if(index_count > 1, "both", INDEX)

[KayBeesKnees83]

This worked, as well! 🙂 

Thank you! I sincerely appreciate your help. 

[KayBeesKnees83]

This worked!! 🙂 Thank you very much for your help!!