help with inputlookup and a subsearch

[jip31]

hello

In the search below I try to match host in "host.csv" with host which comes from a subsearch

| inputlookup host.csv 
| table host 
| join type=left host 
    [ search index=master-data-lookups sourcetype="view_splunk_assets" 
    | stats count by HOSTNAME TOWN COUNTRY
    | fields - count 
    | rename HOSTNAME as host] 

what is the problem because I have results when i execute | inputlookup host.csv OR index=master-data-lookups sourcetype="view_splunk_assets"
| stats count by HOSTNAME TOWN COUNTRY
| fields - count
| rename HOSTNAME as host

thanks

[HiroshiSatoh]

In your search statement, "host.csv" is 1 and ”subsearch” is the first one.
If you want "host.csv" to connect multiple ”subsearch” to 1 change the max value.

host.csv
A
B
C

”subsearch”
A TOWN1 COUNTRY1
A TOWN2 COUNTRY2
C TOWN3 COUNTRY3
C TOWN4 COUNTRY4

your search results
A TOWN1 COUNTRY1
B
C TOWN3 COUNTRY3

What do you want to do?

join-options
Syntax: type=(inner | outer | left) | usetime= | earlier= | overwrite= | max=
Description: Options to the join command. Use either outer or left to specify a left outer join.

max
Syntax: max=
Description: Specifies the maximum number of subsearch results that each main search result can join with. If set to max=0, there is no limit.
Default: 1

[woodcock · Accepted solution]

Try this:

index=master-data-lookups sourcetype="view_splunk_assets" 
| stats count by HOSTNAME TOWN COUNTRY
| fields - count 
| rename HOSTNAME as host
| appendpipe [|inputlookup host.csv 
| table host
| eval sourcetype="csv"]
| stats values(*) AS * BY host
| search sourcetype="csv"

[jip31]

thanks a lot

[grittonc]

What is the significance to putting the index search first?

[woodcock]

Because the appendpipe [|inputlookup ...] trick that I invented somehow bypasses the subsearch limits, but the normal search will not.

[grittonc]

What do you get when you run this entire search? Do you get records from your lookup table and nothing from the index?