Splunk Search

Joining two sourcetypes on a common Id where the names differ

arist0telis
Explorer

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

arist0telis
Explorer

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

PickleRick
SplunkTrust
SplunkTrust

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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

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

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...