How do I compare results from 2 indexes on a common field?

[mwdbhyat]

Hi guys,

Has anyone ever written a search that can compare events(in this case "indicator" across 2 indexes and show them in separate tables side by side? EG search(that doesn't work):

index=ironport
| rename url AS indicator
| join indicator [search index=crowdstrike* type=url earliest=0 | spath output=myfield path=relations.indicator{}.type{} | table indicator, type, ip_address_types, labels_name,malware_families]
| table indicator

I'm basically looking for common "indicator" fields between two indexes, which would then be presented in a table.
Any thoughts on this?

Thanks!

[woodcock · Accepted solution]

Stop using join; try this:

index=ironport OR (index=crowdstrike* type=url earliest=0)
| spath output=myfield path=relations.indicator{}.type{}
| table indicator, type, ip_address_types, labels_name,malware_families)
| eval indicator = coalesce(indicator, url)
| stats dc(index) AS indexCount BY indicator
| search indexCount>1

[mwdbhyat]

Thanks that did what I wanted

[woodcock]

It will probably be more efficient if you drop the | table line or switch to | fields. Try that and compare.

[ssadanala1]

You can try some this like this

index=ironport
| rename url AS indicator |appendcols [search index=crowdstrike* type=url earliest=0 | spath output=myfield path=relations.indicator{}.type{} | table indicator, type, ip_address_types, labels_name,malware_families]| stats values(*) as * by indicator