distinct output from one splunk search as input to another

[bmer]

Hi Team,

I have 2 splunks as below

1. (index=xxxx) orgName=xxx sourcetype=CASE(SourceA) earliest=-15d uniqueIdentifier="Class.ClassName.MethodName*" | dedup SourceASqlId | tableSourceASqlId
2. (index=xxxx) orgName=xxx sourcetype=CASE(SourceB) earliest=-15d SourceBSqlId=xxxx | table SourceBSqlText

I want to form a single splunk to get ALL the distinct "SourceASqlId" [splunk # 1], get them as input to "SourceBSqlId" [splunk #2] and generate FINAL output as "SourceBSqlText

How can we achieve it.Iam even ok if the date range can be reduce to say 2d to make the splunk optimised as I feel my requirement is very heavy compute intensive

Thanks.

[PickleRick]

There is most probably a better way to achieve your goal. Try to describe the logic behind what you're trying to do.

Anyway, 

| dedup A | table A

is usually _not_ the way to go. You'd rather want to do

| stats values(A) as A | mvexpand A

[bmer]

@ITWhisperer I received an error saying "Error in 'SearchParser': Missing a search command before '('.Error at position '90' of search query 'search (index=xxxx) CASE(SourceA) source..."

Also any reason why the outer search is of 15d whereas subsearch is set for 2d?Is it for the optimisation?

[ITWhisperer]

Updated response - you said 2d would be OK - essentially, the subsearch needs to be fewer than 50,000 events, so if 15d matches that requirement, then use 15d otherwise use a smaller amount (like 2d as you suggested).

[livehybrid]

Hi @bmer 

The subsearch is missing the "search" prefix, try this (adjusted to -2d as required)

(index=xxxx) orgName=xxx sourcetype=CASE(SourceB) earliest=-2d 
    [ search (index=xxxx) orgName=xxx sourcetype=CASE(SourceA) earliest=-2d uniqueIdentifier="Class.ClassName.MethodName*" 
    | dedup SourceASqlId 
    | rename SourceASqlId as SourceBSqlId 
    | table SourceBSqlId] 
| table SourceBSqlText

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[ITWhisperer · Accepted solution]

The simplest way to do this (although perhaps not the most optimal) would be something like this

(index=xxxx) orgName=xxx sourcetype=CASE(SourceB) earliest=-15d [search (index=xxxx) orgName=xxx sourcetype=CASE(SourceA) earliest=-2d uniqueIdentifier="Class.ClassName.MethodName*" | dedup SourceASqlId | rename SourceASqlId as SourceBSqlId | table SourceBSqlId]
| table SourceBSqlText

Bear in mind that subsearches are limited to 50,000 events