How to link multiple sourcetypes based on multiple fields

[Laurengineer]

I have a few sourcetypes, looking something like this:

sourcetype=weather
date, location, temperature

sourcetype=actions
date, machine, location, action

sourcetype=repairs
date, machine, relacementPart

I'd like to be able to pick out a specific machine, and then list all the dates on which it completed actions, and then link those dates and its location at that time to find out what the weather was like when the action was carried out, and also when any repairs were carried out. So I'd end up with the following, for a given machine:

date, action, temperature, replacementPart

I've done a fair bit of googling and trying out methods given that seemed like they might help, including join, coalesce and transaction commands, but nothing I've tried seems to have quite worked.

 I'm pretty new to Splunk, so any help with a bit of an explanation is very much appreciated!

[Laurengineer · Accepted solution]

I (eventually) managed to get what I was looking for using a join, though I realise that they're inefficient in Splunk.

index=main sourcetype=actions machine=A
| join type=inner location date [ search index=main sourcetype=weather ]
| join type=left date [ search index=main sourcetype=repairs ]
| timechart avg(temperature) count(action) count(relacementPart)

If anyone has anything more elegant I'm more than happy to upgrade my solution!