I've been looking around here and on Google but can't find an answer to this specific usecase: I have two sourcetypes, both being fed by Salesforce, that have a common Id but the column names are different. How do I write a query to join them (or more specifically, augment the data from sourcetype A with some additional fields from sourcetype B)?
1. index=sfdc sourcetype=sfdc:LoginEvent
2. index=sfdc sourcetype=sfdc:User
The common field on both is the user's platform GUID (on LoginEvent the column is called UserId, on the User object it's just called Id). My primary source here is LoginEvent. I'm trying coalesce() but getting what looks like a crossjoin (I'm getting users in my table output with no LoginEvent corollary). If I were to write what I'm trying to get in SQL, this is what I'm trying to do (hopefully this makes it easier to understand):
SELECT le.EventDate, le.Application, le.LoginType, le.Platform, le.Status, u.Type, u.Location
FROM LoginEvent le
JOIN User u ON (u.Id = le.UserId)
ORDER BY le.EventDate
Actually as soon as I posted that I think I figured it out.
index=sfdc sourcetype=sfdc:LoginEvent
| rename UserId as Id
| join Id
[search sourcetype=sfdc:User]
| table EventDate,Application,LoginType,Platform,Status,Name
If anyone wants to suggest ways to improve efficiency I'm all for it but this looks like it does the proper joining.
If you can avoid subsearches and joining, do so.
In your case I'd go for simple alternative search, possibly conditional eval to avoid overlapping field names and stats values(*) by Id.