Getting Data In

How to combine common fields in 2 sourcetypes without using the join command?

lucky001
Engager

I am using Splunk Enterprise. Here are 2 sourcetype A and B and they share a same fileld UserName. The search time range of A is changeable according to the time picker while the time range of B is -30d@d.
B has less UserName than A (B is a subset of A) and what I want is to use B's UserName and combined with A, then return A's other fields.

Since both sourcetype A and B are huge. I tried to save source B search with -30d@d in the lookup to make the subsearch quicker. But this search is still about 250-300MB which exceeds the limit which is 200MB. It takes Splunk running forever.
The search is like this:

index=whatever sourcetype=A 
|join UserName [inputlookup B-lookup]
|table UserName, "B's fields", "A's fields"

I tried to use stats but did not find a way to do the combination.

Is there anyone that could help with doing the combination without using join? Thanks.

0 Karma
1 Solution

cmerriman
Super Champion

so you do not need the lookup because B is a sourcetype, correct?

can you try something like:

index=whatever sourcetype=A OR (index=whatever sourcetype=B earliest=-30d@d)
|stats values(B fields) values(A fields) by UserName

edit the stats command as you see fit.

View solution in original post

sbbadri
Motivator

Try this

index=whatever eventtype=whateversourcetype |table UserName, "B's fields", "A's fields"

eventtypes.conf

[whateversourcetype ]
search = sourcetype=A OR sourcetype=B

or
From GUI:

settings->Event types-> new

Name=whateversourcetype
Search String=sourcetype=A OR sourcetype=B

0 Karma

lucky001
Engager

solved by using this instead of join with subsearch.
...
|lookup B-lookup UserName OUTPUT BField
|where isnotnull(BField)
...

0 Karma

cmerriman
Super Champion

so you do not need the lookup because B is a sourcetype, correct?

can you try something like:

index=whatever sourcetype=A OR (index=whatever sourcetype=B earliest=-30d@d)
|stats values(B fields) values(A fields) by UserName

edit the stats command as you see fit.

lucky001
Engager

Thank you cmerriman for replying.
I used a lookup for sourcetypeB to reduce the query size. so the lookup query is like
index=whatever sourcetype=B earliest=-30d@d outputlookup B-lookup
Then in the main query I used the inputlookup for finding UserName in B

I also tried the query you mentioned but it only returns values of fields from one of the sourcetype.
Maybe I will try to filter out some unnecessary data from the datasets.

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 ...