Help on subsearch processing understanding

[jip31]

Hello

I would like to understand the exact processing of a subsearch.

As far as I know,  subsearches execute first and their results become part of the main search.

So in the example below, does it means that the search after the join command (`wire`) is run first, that these results are crossed with the host list there is in "host.csv" and finally that these results become part of the main search (`CPU` )?

Is "their results become part of the main search" means that the results of `wire` search are added in the correspondind events of `CPU` search or does it means that the results of `wire` search  are crossed with the correspondind events of `CPU` search ? By results I mean the results by host because "host" is just the only common field

[| inputlookup host.csv 
    | table host ] `CPU`  
| lookup fo_all HOSTNAME as host output SITE DESCRIPTION_MODEL BUILDING_CODE
| search SITE=XYZ
| stats last(DESCRIPTION_MODEL) as Model, count(process_cpu_used_percent) as "Number of CPU alerts", last(SITE) as Site, last(BUILDING_CODE) as Building by host 
| join host type=outer 
    [| search `wire` 
    | rename USERNAME as host 
    | lookup aps.csv NAME as AP_NAME OUTPUT Building 
    | stats last(AP_NAME) as "Access point", last(Building) as "Geolocation building" by host ] 
| rename host as Hostname 
| table Hostname Model Site Building Room "Access point" "Geolocation building" "Number of CPU alerts" 
| sort -"Number of CPU alerts"

Other question

If I look the "Number of CPU alerts" for the host "RE2345", i have 34 CPU alerts

But if I put the main search in the subsearch and subsearch in the main search like below, I have 6 CPU alerts

[| inputlookup host.csv 
    | table host ] `wire` 
    | rename USERNAME as host 
    | lookup aps.csv NAME as AP_NAME OUTPUT Building 
    | stats last(AP_NAME) as "Access point", last(Building) as "Geolocation building" by host
| join host type=outer 
    [| search `CPU`  
| lookup fo_all HOSTNAME as host output SITE DESCRIPTION_MODEL BUILDING_CODE
| search SITE=XYZ
| stats last(DESCRIPTION_MODEL) as Model, count(process_cpu_used_percent) as "Number of CPU alerts", last(SITE) as Site, last(BUILDING_CODE) as Building by host ]
| rename host as Hostname 
| table Hostname Model Site Building Room "Access point" "Geolocation building" "Number of CPU alerts" 
| sort -"Number of CPU alerts"

How to explain the difference please?

Thanks in advance

[richgalloway · Accepted solution]

You are correct that subsearches execute before the main search and add their output to the main search.

However, not everything within square brackets is a subsearch.

The join and append* commands behave differently from "regular" subsearches.  Mainly because they do not run before the main search.

The join command runs the "subsearch" and pairs the results with those from the main search using the specified field(s).

The append command places the results of the "subsearch" below those of the main search.

The appendcols command places the results of the "subsearch" to the right of those of the main search.

(It helps to think of each set of results as a table.)

Appendpipe is a different beast.  See the Search Reference manual for a description of it.

The foreach command also uses square brackets, but they do not denote a subsearch and the contents of those brackets execute as part of foreach.

Finally, the join command is not symmetric.  Joining A to B is not the same as joining B to A, although sometimes we get lucky and the two results are the same.