How to bring the deepest data in multiple subsearch

[tehong]

Hi Splunk experts!!

Please tell me about how to bring the deepest data in multiple subsearches. Of course, if there is another way to do it than subsearch, we can use that method as well.

I understand that when using multiple subsearches, each subsearch is just passing field results to the top subsearch.
But can the data of any field in the first subsearch also be passed to the next subsearch? (same for the second to third subsearch)

I am thinking that this is difficult with subsearch because subsearch just passes fields in AND.

I believe it can be done with join or stats. But how should I do it?

index=cmdb sourcetype=crm host="fwd-splunk-fwd01a"
    LogicalName="new_contract"
    (Attributes.KeyValuePairOfstringanyType{}.new_item_name="DC_Connection" OR Attributes.KeyValuePairOfstringanyType{}.new_circuit.Name="*DC*") 
    [| search index=cmdb sourcetype=crm host="fwd-splunk-fwd01a"
        LogicalName="new_circuit"
        FormattedValues.KeyValuePairOfstringstring{}.statecode="active"
        FormattedValues.KeyValuePairOfstringstring{}.statuscode="active"
        FormattedValues.KeyValuePairOfstringstring{}.new_circuit_status="contracted" 
        [| search index=cmdb sourcetype=crm host="fwd-splunk-fwd01a"
            LogicalName="new_circuit_authority"
            FormattedValues.KeyValuePairOfstringstring{}.statecode="active"
            FormattedValues.KeyValuePairOfstringstring{}.statuscode="active"
            FormattedValues.KeyValuePairOfstringstring{}.new_trouble_mail_receive_flag="yes"
            FormattedValues.KeyValuePairOfstringstring{}.new_valid_flag="yes" 
            [| search index=cmdb sourcetype=crm host="fwd-splunk-fwd01a"
                LogicalName="new_contactpoint"
                FormattedValues.KeyValuePairOfstringstring{}.statecode="active"
                FormattedValues.KeyValuePairOfstringstring{}.statuscode="active" Attributes.KeyValuePairOfstringanyType{}.new_cp_code="CP30058460" 
            | fields Attributes.KeyValuePairOfstringanyType{}.new_contactpointid 
            | stats latest(*) AS * by Attributes.KeyValuePairOfstringanyType{}.new_contactpointid 
            | rename Attributes.KeyValuePairOfstringanyType{}.new_contactpointid AS Attributes.KeyValuePairOfstringanyType{}.new_contactpoint.Id 
            | format
                ] 
        | fields Attributes.KeyValuePairOfstringanyType{}.new_circuit.Name 
        | stats latest by Attributes.KeyValuePairOfstringanyType{}.new_circuit.Name 
        | rename Attributes.KeyValuePairOfstringanyType{}.new_circuit.Name AS Attributes.KeyValuePairOfstringanyType{}.new_circuit_code 
        | format
            ] 
    | stats latest by Attributes.KeyValuePairOfstringanyType{}.new_circuit_code 
    | fields Attributes.KeyValuePairOfstringanyType{}.new_circuit_code 
    | rename Attributes.KeyValuePairOfstringanyType{}.new_circuit_code AS Attributes.KeyValuePairOfstringanyType{}.new_circuit.Name
        ] 
| fields Attributes.KeyValuePairOfstringanyType{}.new_circuit.Id
| stats latest by Attributes.KeyValuePairOfstringanyType{}.new_circuit.Id

 

[PickleRick]

To be absolutely honest with you, whenever I see such nested subsearches I get the impression that someone was trying to "think SQL" and implement it in Splunk. It doesn't work this way (or at least not very efficiently).

So please tell us what you want to achieve (not how you're trying to do it), maybe we'll think of something better 🙂

And tell us what data you have (some examples, anonymized/obfuscated if need be, would be helpful).

EDIT: Oh, and don't use conditions like

Attributes.KeyValuePairOfstringanyType{}.new_circuit.Name="*DC*"

in your initial searches. Since they contain wildcard at the beginning of your search term, Splunk will have to literarily scan all events from the defined timerange to find your matching events since it cannot use its internal indexes to match them.

[VatsalJagani]

@tehong - I did not understand why it's stats latest by, generally, it's like stats latest(<some field>) by.

So, I could not figure out exactly your goal.

But you can try using stats with an eval statement. - https://docs.splunk.com/Documentation/Splunk/latest/Search/Usestatswithevalexpressionsandfunctions

Based con conditions you have added in all subsearches, I'm guessing evals with stats should solve your problem. You may need additional evals after the stats to calculate some of the fields.

But this is where I would start.

--------
I hope this helps!!!!