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 email@example.com nonEXistent.abc.ccc firstname.lastname@example.org yetANOTHER.aaa.bbb email@example.com
Desired search result
DeviceName FileName Owner myserver.test.com explorer.exe firstname.lastname@example.org another.exe yetanother.aaa.bbb third.exe email@example.com
Couple of things to notice
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
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! 🙂
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
@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="firstname.lastname@example.org"] |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
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.
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?
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.