topic Re: How to combine common fields in 2 sourcetypes without using the join command? in Getting Data In

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

lucky001 — Thu, 06 Jul 2017 19:13:21 GMT

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.

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

cmerriman — Thu, 06 Jul 2017 20:57:07 GMT

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.

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

lucky001 — Fri, 07 Jul 2017 13:04:11 GMT

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.

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

lucky001 — Tue, 11 Jul 2017 17:49:58 GMT

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

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

sbbadri — Tue, 11 Jul 2017 18:06:32 GMT

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