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'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! 🙂
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
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?
@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
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.
The example query works fine for me.
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.