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.
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.
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
solved by using this instead of join with subsearch.
...
|lookup B-lookup UserName OUTPUT BField
|where isnotnull(BField)
...
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.
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.