topic Re: Matching fields from different indices to return another field in Splunk Search

Matching fields from different indices to return another field

izyknows — Mon, 08 Jun 2020 17:25:10 GMT

Hi,

I have two different indexes where I need to match a field and if true, return another field.

First Search (Index1)

FileName          DeviceName
explorer.exe     myserver.test.com
processor.dll    anothersystem.xyz.abc
third.exe        yetanother.aaa.bbb
another.exe      myserver.test.com

Second search (Index2)

HostName                      Owner
MYserver.test.com        bob@sample.com
nonEXistent.abc.ccc      larry@sample.com
yetANOTHER.aaa.bbb       charlie@sample.com

Desired search result

DeviceName                    FileName                Owner
myserver.test.com            explorer.exe           bob@sample.com
                             another.exe
yetanother.aaa.bbb           third.exe              charlie@sample.com

Couple of things to notice

I need to show results where DeviceName and HostName match. Both fields may be in different case (so case insensitive matching is required)
If DeviceName==HostName, I need the Owner field returned from Index2
One DeviceName/HostName may have many FileNames under it and I need to display all (explorer.exe + another.exe)

I've been tinkering around and am having a hard time finding the right query. Here's where I'm at.

(index=index1 sourcetype=type1 FileName=somecondition*) OR (index=index2 sourcetype=type2)
| fields FileName, DeviceName, Owner, HostName
| eval magic=case(DeviceName==HostName, Owner)
| stats list(FileName) as FileName, list(magic) as SysOwner by DeviceName

Although it doesn't work. I tried variations of the eval statement using if, coalesce and a few other solutions from other questions. But I believe the case difference between the two fields is what is hindering me.

I'm still new to Splunk and any help would be appreciated! 🙂

Re: Matching fields from different indices to return another field

493669 — Wed, 03 Jun 2020 12:49:23 GMT

@izyknows Please try below-

...|eval DeviceName=coalesce(DeviceName,HostName)|stats values(FileName) as FileName values(Owner) as Owner by DeviceName

Below is using sample data-

|makeresults|eval FileName="explorer.exe", DeviceName="myserver.test.com"
|append[|makeresults|eval HostName="myserver.test.com", Owner="bob@sample.com"]
|append[|makeresults|eval FileName="another.exe", DeviceName="myserver.test.com"]
|fields - _time|eval DeviceName=coalesce(DeviceName,HostName)|stats values(FileName) as FileName values(Owner) as Owner by DeviceName

Re: Matching fields from different indices to return another field

richgalloway — Wed, 03 Jun 2020 12:54:45 GMT

You were close. The secret is to create a common field between the two indexes that Splunk can use to match up events. I like to use coalesce to do that.

(index=index1 sourcetype=type1 FileName=somecondition*) OR (index=index2 sourcetype=type2)
 | fields FileName, DeviceName, Owner, HostName
 | eval DeviceName=coalesce(DeviceName, HostName)
 | stats values(*) as * by DeviceName
 | table DeviceName FileName Owner

Re: Matching fields from different indices to return another field

izyknows — Wed, 03 Jun 2020 14:45:46 GMT

Interesting! I tried running your query and while it gives me DeviceName and Owner properly, none of the other fields (FileName) are blank. Any idea why?

Re: Matching fields from different indices to return another field

izyknows — Wed, 03 Jun 2020 14:46:22 GMT

Thanks! I tried out your query but no luck. Similar to the other answer, DeviceName and Owner populate fine but the other fields are blank.

Re: Matching fields from different indices to return another field

richgalloway — Wed, 03 Jun 2020 17:29:56 GMT

The example query works fine for me.

Re: Matching fields from different indices to return another field

izyknows — Thu, 04 Jun 2020 15:12:47 GMT

Unfortunately it doesn't for me. The example I posted is a simplified version of the actual scenario though. There are 3 more fields from each index which I also fetch and show in the end. I took them out for the sake of simplicity.

Re: Matching fields from different indices to return another field

izyknows — Thu, 04 Jun 2020 15:28:56 GMT

I think when stats values() comes into picture, it removes duplicates. And in my FolderPath and other fields, I may have (and want) these duplicates. Why is it that after coalesce, my other fields disappear?

Re: Matching fields from different indices to return another field

richgalloway — Wed, 10 Jun 2020 00:41:06 GMT

Yes, stats values(foo) removes the duplicate values of field foo. To see the duplicates use list(foo).

Coalesce should not be affecting your other fields.