Splunk Search

Comparing data located in two different indexes?

KayBeesKnees83
Path Finder

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 

Labels (1)
Tags (1)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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)

 

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

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
Path Finder

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

 

Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
Path Finder

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. 

Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
Path Finder

This worked, as well! 🙂 

Thank you! I sincerely appreciate your help. 

Tags (1)
0 Karma

KayBeesKnees83
Path Finder

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

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...