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.
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
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.