Knowledge Management

help with inputlookup and a subsearch

jip31
Motivator

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

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

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"

View solution in original post

HiroshiSatoh
Champion

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

0 Karma

woodcock
Esteemed Legend

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
Motivator

thanks a lot

0 Karma

grittonc
Contributor

What is the significance to putting the index search first?

woodcock
Esteemed Legend

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

0 Karma

grittonc
Contributor

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

0 Karma
Get Updates on the Splunk Community!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...