How to Extract values from one index and see if similar matches exists in other indexes?

[innoce]

Hi,

I need to extract host values from one index (index=1) and see if there are similar matches that exists in other indexes (index=2 and index=3). Below are the details:

Index=1 hosta=* hostb=* hostc=*

index=2 hostx=*

index=3 hostx=*

Can someone please help me with an SPL to find this?

[PickleRick]

What do you mean by "similar"?

[innoce]

@PickleRick Like if index=1 has host=aaa, I need to check if similar host=aaa exists in either index=2 OR index=3

[dhirendra761]

hi @innoce  Just try this

( index=1 OR index=2 OR index=3) host=aaa | table index

[PickleRick]

You mean the same host? Because "similarity" can be understood in many different ways.

If you want to simply enumerate the values of host field in an index, you can do

index=index1 OR index=index2 OR ... | stats values(host) by index

That's a simple search but you'll have to compare the results by hand. With small number of results that's of course easy to do, with several dozens of hosts per index it might be more troublesome.

Since host is one of the default fields and is an indexed field you can do this even faster using tstats

| tstats values(host) by index where index=index1 OR index=index2 OR ...

Of course the "conceptually easiest" way to list only the values that are in index1 would be to use subsearch

(index=index2 OR index=index3 OR... )  [ | tstats values(host) as host where index=index1 | table host ] 
| whatever_stats_you_want

 Or even

| tstats values(host) as host by index where (index=index2 OR index=index3 OR ... [ | tstats values(host) as host where index=index1 | table host ]

 But that uses subsearches which has its downsides and as a rule of thumb you should avoid subsearches.

So the proper approach would be to create a list of indexes for each host

| tstats values(index) as index by host

And see which of them is included in index1

| search index=index1

 So effectively you have just a simple

| tstats values(index) as index by host
| search index=index1