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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...