Splunk Search

Joining two sourcetypes on a common Id where the names differ


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

Labels (1)
0 Karma


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.

Tags (1)
0 Karma

Ultra Champion

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.

0 Karma
Get Updates on the Splunk Community!

User Groups | Upcoming Events!

If by chance you weren't already aware, the Splunk Community is host to numerous User Groups, organized ...

Splunk Lantern | Spotlight on Security: Adoption Motions, War Stories, and More

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Cloud | Empowering Splunk Administrators with Admin Config Service (ACS)

Greetings, Splunk Cloud Admins and Splunk enthusiasts! The Admin Configuration Service (ACS) team is excited ...