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!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...