How do I join multiple sourcetypes by different fields (ids)?

[ebruozys]

I'm trying to join the result of three different sourcetypes into one result. These three sourcetypes are connected by different ids. One of the sourcetypes contains all the ids which are used to connect the different sourcetypes.

sourcetype=doc:
doc_id, sgn_id, rcr_id, psn_id

sourcetype=docstats:
status, doc_id

sourcetype=reference:
refstats, service, date, sgn_id

sourcetype=type:
type, rcr_id

What I would like to see is a table with the following information:

doc_id, status, refstats, service, date, type

I am able to join two sourcetypes by one id using the stats command, but doing this on more than one sourcetype with different ids is where I get lost. I searched through previous answers, but can’t seem to find a similar case.

Does anyone have any tips on how I would be able to achieve this?

By the way, using the join command isn’t an option since the sourcetypes contain more than 50000 rows.

[gcusello]

Hi ebruozys,
you have to use more sequential joins:

sourcetype=doc
| join type=left doc_id [ search sourcetype=docstats ]
| join type=left sgn_id [ search sourcetype=reference ]
| join type=left rcr_id [ search sourcetype=type ]
| table doc_id, status, refstats, service, date, type

This solution has two problems: isn't very performant and you have only 50.000 events in each subsearch.
You could use a different solution:

sourcetype=doc OR sourcetype=docstats OR sourcetype=reference OR sourcetype=type
| stats values(status) AS status values(refstats) AS refstats values(service) AS service values(date) AS date BY doc_id, sgn_id, rcr_id

In addition I suggest to use always the index=my_index condition to have more performant searches.

Bye.
Giuseppe

[ebruozys]

Hi Giuseppe,

Thank you for your answer.

Whenever I run this search I only get the fields I used to group by as results in a table. The fields status, refstats, service and date remain empty.