How to resolve field into either value from two different keys with two tables outer joined

[norish]

I'm using `Splunk Add-on for Box` to collect box logging data.

As a premise, `box:events' contains information for `uploaded`, `deleted`, `downloaded`, `source_item_id`, `source_parent_id` events, where `source_item_id` means file id, and `source_parent_id` means its folder id.

The `box:file` contains `file id`, `location` events.

The `box:folder` contains `folder id`, `location` events.

My purpose is to resolve folder location from `box:evets` file's `some action' event.

I can resolve by `box:file' with one outer join SPL like this.

search index="box" sourcetype="box:events" event_type=DOWNLOAD earliest=-1d
| field _time source_item_name source_item_id source_parent_id
| join type=outer left=L right=R where L.source_item_id=L.id
[ search index=box sourcetype="box:file" earliest=-1 | field id location ]
| table L._time L.source_item_name R.location

And I can do with `box:folder` like this.

search index="box" sourcetype="box:events" event_type=DOWNLOAD earliest=-1d
| field _time source_item_name source_item_id source_parent_id
| join type=outer left=L right=R where L.source_parent_id=L.id
[ search index=box sourcetype="box:folder" earliest=-1 | field id location ]
| table L._time L.source_item_name R.location

But I don't know how integrate above two SPL into one.

Please tell me some idea. Thanks in advance.

[ITWhisperer · Accepted solution]

Try to avoid using join, it is slow and inefficient. Try something like this

search index="box" (sourcetype="box:events" event_type=DOWNLOAD earliest=-1d) OR (sourcetype="box:file" earliest=-1) OR (sourcetype="box:folder" earliest=-1)
| eval source_item_id=if(sourcetype="box:file",id,source_item_id)
| eval source_parent_id=if(sourcetype="box:folder",id,source_parent_id)
| eventstats values(location) as file_location by source_item_id
| eventstats values(location) as folder_location by source_parent_id
| where sourcetype="box:events"
| table _time source_item_name source_item_id source_parent_id file_location folder_location

[norish]

Thank you very much. This is working to me and feel a bit faster than before.

[norish]

typo

> where L.source_item_id=L.id

where L.source_item_id=R.id

> where L.source_parent_id=L.id

where L.source_parent_id=R.id