help on jointure in appendcols subsearch

[jip31]

Hello

In the search below, I need to do a jointure after the appendcols command like in the first part of the search

| inputlookup lookup_pana
| rename "name0" as HOSTNAME 
| fields HOSTNAME HealthState0  
| where HealthState0 < 85 
| join HOSTNAME 
    [| inputlookup fo_all where TYPE="In"
    | fields SITE COUNTRY RESPONSIBLE DEPARTMENT HOSTNAME] 
| stats count as NbHostHealthInf85 
| appendcols 
    [| inputlookup fo_all where TYPE="Ind" 
    | fields SITE COUNTRY RESPONSIBLE DEPARTMENT HOSTNAME 
    | stats count as NbIndHost] 

So I done this but it doesnt works

| appendcols 
| inputlookup lookup_pana
| rename "name0" as HOSTNAME 
| join HOSTNAME 
    [| inputlookup fo_all where TYPE="Ind" (
    | fields SITE COUNTRY RESPONSIBLE DEPARTMENT HOSTNAME 
    | stats count as NbIndHost] 

How to do this please?

[richgalloway · Accepted solution]

First, one should avoid joins whenever possible because they are slow.  See the Join section of the Search Reference manual for alternatives.

Second, the join in the second example fails because the subsearch does not return a HOSTNAME field so the join command has nothing to match events with.  The only fields returned by the subsearch are the 'count' and 'NbIndHost' fields from the stats command.